Формулы со: Вставка формул и функций в Numbers на Mac

Ниже: формула, вставленная в ячейку E2 с помощью Control + V. Обратите внимание, что ссылки на ячейки изменены:

Та же формула вставлена ​​в E3.Сотовые адреса обновлены снова:

Относительные и абсолютные ссылки

Указанные выше ссылки на ячейки называются относительными ссылками . Это означает, что ссылка относится к ячейке, в которой она находится. Формула в E1 выше:

 
 = B1 + C1 + D1 // формула в E1 

Буквально это означает «ячейка 3 столбца слева» + «ячейка 2 столбца слева» + «ячейка 1 столбец слева». Вот почему, когда формула копируется в ячейку E2, она продолжает работать точно так же.

Относительные ссылки чрезвычайно полезны, но бывают случаи, когда вы не хотите, чтобы ссылка на ячейку изменялась. Ссылка на ячейку, которая не изменится при копировании, называется абсолютной ссылкой . Чтобы сделать ссылку абсолютной, используйте символ доллара ($):

 
 = A1 // относительная ссылка
= $ A $ 1 // абсолютная ссылка 

Например, на экране ниже мы хотим умножить каждое значение в столбце D на 10, которое вводится в A1. Используя абсолютную ссылку для A1, мы «блокируем» эту ссылку, чтобы она не изменилась при копировании формулы в E2 и E3:

Вот окончательные формулы в E1, E2 и E3:

 
 = D1 * $ A $ 1 // формула в E1
= D2 * $ A $ 1 // формула в E2
= D3 * $ A $ 1 // формула в E3 

Обратите внимание, что ссылка на D1 обновляется при копировании формулы, но ссылка на A1 никогда не изменяется. Теперь мы можем легко изменить значение в A1, и все три формулы пересчитаются. Ниже значение в A1 изменилось с 10 на 12:

Этот простой пример также показывает, почему не имеет смысла жестко закодировать значения в формулу. Сохраняя значение в A1 в одном месте и ссылаясь на A1 с абсолютной ссылкой, значение можно изменить в любое время, и все связанные формулы будут обновлены мгновенно.

Совет: вы можете переключаться между относительным и абсолютным синтаксисом с помощью клавиши F4.

Как ввести формулу

Чтобы ввести формулу:

1. Выберите ячейку
2. Введите знак равенства (=)
3. Введите формулу и нажмите клавишу ВВОД.

Вместо того, чтобы вводить ссылки на ячейки, вы можете указать и щелкнуть, как показано ниже. Ссылки на заметки имеют цветовую кодировку:

Все формулы в Excel должны начинаться со знака равенства (=). Без знака равенства, без формулы:

Как изменить формулу

Для редактирования формулы у вас есть 3 варианта:

1. Выберите ячейку, отредактируйте в строке формул
2. Дважды щелкните ячейку, отредактируйте напрямую
3. Выберите ячейку, нажмите F2, отредактируйте напрямую

Независимо от того, какой вариант вы используете, нажмите Enter, чтобы подтвердить изменения, когда закончите. Если вы хотите отменить и оставить формулу без изменений, нажмите клавишу Escape.

Видео: 20 советов по вводу формул

Что такое функция?

Работая в Excel, вы услышите, как часто используются слова «формула» и «функция», иногда как синонимы. Они тесно связаны, но не совсем одинаковы. Технически формула – это любое выражение , которое начинается со знака равенства (=).

С другой стороны, функция – это формула со специальным именем и назначением.В большинстве случаев функции имеют имена, которые отражают их предполагаемое использование. Например, вы, вероятно, уже знаете функцию СУММ, которая возвращает сумму заданных ссылок:

 
 = СУММ (1,2,3) // возвращает 6
= СУММ (A1: A3) // возвращает A1 + A2 + A3 

Функция AVERAGE, как и следовало ожидать, возвращает среднее значение данных ссылок:

А функции MIN и MAX возвращают минимальное и максимальное значения соответственно:

 
 = MIN (1,2,3) // возвращает 1
= MAX (1,2,3) // возвращает 3 

Excel содержит сотни специальных функций. Для начала ознакомьтесь со статьей 101 Основные функции Excel.

Аргументы функции

Большинству функций требуются входные данные для возврата результата. Эти входные данные называются «аргументами». Аргументы функции появляются после имени функции в круглых скобках, разделенных запятыми. Для всех функций требуются соответствующие открывающая и закрывающая круглые скобки (). Выглядит выкройка так:

 
 = ИМЯ ФУНКЦИИ (аргумент1, аргумент2, аргумент3) 

Например, функция СЧЁТЕСЛИ подсчитывает ячейки, которые соответствуют критериям, и принимает два аргумента: диапазон и критерии :

 
 = СЧЁТЕСЛИ (диапазон, критерий) // два аргумента 

На приведенном ниже экране диапазон – A1: A5, а критерий – «красный».Формула в C1:

Видео: как использовать функцию СЧЁТЕСЛИ

Не все аргументы требуются. Аргументы, показанные в квадратных скобках, необязательны. Например, функция YEARFRAC возвращает дробное количество лет между датой начала и датой окончания и принимает 3 аргумента:

Дата начала и дата окончания являются обязательными аргументами, основание – необязательным аргументом. См. Ниже пример того, как использовать YEARFRAC для расчета текущего возраста на основе даты рождения.

Как войти в функцию

Если вы знаете название функции, просто начните печатать. Вот шаги:

1. Введите знак равенства (=) и начните вводить текст. Excel отобразит список подходящих функций по мере ввода:

Когда вы увидите в списке нужную функцию, используйте клавиши со стрелками для выбора (или просто продолжайте вводить).

2. Нажмите клавишу Tab, чтобы принять функцию. Excel завершит функцию:

3.Заполните обязательные аргументы:

4. Нажмите Enter, чтобы подтвердить формулу:

Объединение функций (вложение)

Многие формулы Excel используют более одной функции, и функции могут быть «вложены» друг в друга. Например, ниже у нас есть дата рождения в B1, и мы хотим вычислить текущий возраст в B2:

Функция YEARFRAC вычисляет годы с начальной и конечной датами:

Мы можем использовать B1 для даты начала, а затем использовать функцию TODAY, чтобы указать дату окончания:

Когда мы нажимаем Enter для подтверждения, мы получаем текущий возраст на основе сегодняшней даты:

Обратите внимание, что мы используем функцию СЕГОДНЯ, чтобы передать дату окончания функции ГОД.Другими словами, функция TODAY может быть вложена в функцию YEARFRAC, чтобы предоставить аргумент даты окончания. Мы можем пойти дальше по формуле и использовать функцию INT, чтобы отрезать десятичное значение:

Здесь исходная формула YEARFRAC возвращает 20,4 функции INT, а функция INT возвращает конечный результат 20.

Примечания:

1. Текущая дата на изображениях выше – 22 февраля 2019 г. 2 = 16 () Круглые скобки = (2 + 4) / 3 = 2

   Логические операторы

   Логические операторы обеспечивают поддержку таких сравнений, как «больше чем», «меньше чем» и т. Д.Логические операторы, доступные в Excel, показаны в таблице ниже:

   Оператор	Значение	Пример
   =	равно	= A1 = 10
   	Не равно	= A110
   >	Больше	= A1> 100
   	Менее	= A1
   > =	Больше или равно	= A1> = 75
   	Меньше или равно	= A1

   Видео: Как строить логические формулы

   Порядок работы

   При решении формулы Excel следует последовательности, называемой «порядком операций».Сначала оцениваются любые выражения в скобках. Далее Excel решит любые степени. После экспонент Excel выполнит умножение и деление, затем сложение и вычитание. Если формула включает конкатенацию, это произойдет после стандартных математических операций. Наконец, Excel оценит логические операторы, если они есть.

   1. Скобки
   2. Показатели
   3. Умножение и деление
   4. Сложение и вычитание
   5. Конкатенация
   6. Логические операторы

   Совет: вы можете использовать функцию Evaluate, чтобы посмотреть, как Excel решает формулы шаг за шагом.

   Преобразование формул в значения

   Иногда нужно избавиться от формул и оставить на их месте только значения. Самый простой способ сделать это в Excel – скопировать формулу, а затем вставить ее, используя Специальная вставка> Значения. При этом формулы заменяются возвращаемыми значениями. Вы можете использовать сочетание клавиш для вставки значений или использовать меню «Вставить» на вкладке «Главная» на ленте.

   Видео: вставка специальных ярлыков

   Что дальше?

   Ниже приведены руководства, которые помогут вам узнать больше о формулах и функциях Excel. Мы также предлагаем онлайн-видео обучение.

   15 формул Excel, сочетания клавиш и хитрости, которые сэкономят вам много времени

   Для большинства маркетологов попытки организовать и проанализировать электронные таблицы в Microsoft Excel могут казаться снова и снова входящими в кирпичную стену. Вы вручную копируете столбцы и записываете длинные математические формулировки на клочке бумаги, при этом думая про себя: «У есть , чтобы лучше это сделать».

   По правде говоря, есть – вы просто еще этого не знаете.

   Excel может быть непростой задачей. С одной стороны, это исключительно мощный инструмент для составления отчетов и анализа маркетинговых данных. С другой стороны, без надлежащей подготовки легко почувствовать, что это работает против вас. Во-первых, существует более десятка важных формул, которые Excel может автоматически запускать для вас, поэтому вам не придется перебирать сотни ячеек с помощью калькулятора на своем столе.

   Что такое формулы Excel?

   Формулы

   Excel помогают определять отношения между значениями в ячейках электронной таблицы, выполнять математические вычисления с использованием этих значений и возвращать полученное значение в выбранной вами ячейке. Формулы, которые вы можете выполнять автоматически, включают сумму, вычитание, процентное соотношение, деление, среднее значение и даже дату / время.

   Мы рассмотрим все это и многое другое в этом сообщении в блоге.

   Как вставлять формулы в Excel

   Вы можете спросить, что означает вкладка «Формулы» на верхней панели инструментов навигации в Excel. В более поздних версиях Excel это горизонтальное меню, показанное ниже, позволяет находить и вставлять формулы Excel в определенные ячейки электронной таблицы.

   Чем чаще вы используете различные формулы в Excel, тем легче их запоминать и выполнять вручную. Тем не менее, набор значков выше представляет собой удобный каталог формул, который вы можете просматривать и ссылаться на них, оттачивая свои навыки работы с электронными таблицами.

   Формулы

   Excel также называют «функциями». Чтобы вставить формулу в электронную таблицу, выделите ячейку, в которой вы хотите запустить формулу, затем щелкните крайний левый значок «Вставить функцию», чтобы просмотреть популярные формулы и их назначение. Это окно просмотра будет выглядеть так:

   Хотите более упорядоченный просмотр веб-страниц? Используйте любой из выделенных нами значков (внутри длинного красного прямоугольника на первом скриншоте выше), чтобы найти формулы, относящиеся к множеству общих тем, таких как финансы, логика и т. Д. Найдя формулу, которая соответствует вашим потребностям, нажмите «Вставить функцию», как показано в окне выше.

   Теперь давайте глубже погрузимся в некоторые из наиболее важных формул Excel и то, как выполнять каждую из них в типичных ситуациях.

   Чтобы помочь вам использовать Excel более эффективно (и сэкономить массу времени), мы составили список основных формул, сочетаний клавиш и других небольших приемов и функций, которые вам следует знать.

   ПРИМЕЧАНИЕ. Следующие формулы применимы к Excel 2017 . Если вы используете более старую версию Excel, расположение каждой функции, упомянутой ниже, может немного отличаться.

   1. СУММА

   Все формулы Excel начинаются со знака равенства =, за которым следует специальный текстовый тег, обозначающий формулу, которую должен выполнять Excel.

   Формула СУММ в Excel – одна из самых простых формул, которые вы можете ввести в электронную таблицу, позволяющую найти сумму (или итог) двух или более значений. Чтобы выполнить формулу СУММ, введите значения, которые вы хотите сложить, используя формат = СУММ (значение 1, значение 2 и т. Д.) .

   Значения, которые вы вводите в формулу СУММ, могут быть либо фактическими числами, либо равными числу в определенной ячейке вашей электронной таблицы.

   • Чтобы найти СУММ 30 и 80, например, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (30, 80) .Нажмите «Enter», и в ячейке появится сумма обоих чисел: 110.
   • Чтобы найти СУММУ значений в ячейках B2 и B11, например, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (B2, B11) . Нажмите «Enter», и в ячейке появится сумма чисел, которые в настоящее время заполнены в ячейках B2 и B11. Если ни в одной из ячеек нет чисел, формула вернет 0.

   Имейте в виду, что вы также можете найти общее значение списка чисел в Excel.Чтобы найти СУММУ значений в ячейках B2 с по B11, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (B2: B11) . Обратите внимание на двоеточие между обеими ячейками, а не на запятую. Посмотрите, как это может выглядеть в таблице Excel для контент-маркетолога, ниже:

   2. IF

   Формула ЕСЛИ в Excel обозначается как = ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь) . Это позволяет вам ввести текстовое значение в ячейку «если» что-то еще в вашей электронной таблице истинно или ложно.Например, = IF (D2 = «Гриффиндор», «10», «0») присудит 10 баллов ячейке D2, если эта ячейка содержит слово «Гриффиндор».

   Бывают случаи, когда мы хотим знать, сколько раз значение появляется в наших таблицах. Но бывают случаи, когда мы хотим найти ячейки, содержащие эти значения, и ввести рядом с ними определенные данные.

   Мы вернемся к примеру Спранга для этого. Если мы хотим присудить 10 баллов всем, кто принадлежит к Гриффиндорскому дому, вместо того, чтобы вручную вводить десятки рядом с именем каждого гриффиндорского ученика, мы воспользуемся формулой ЕСЛИ-ТО, чтобы сказать: Если ученик находится в Гриффиндоре, , затем он или она должны получить десять баллов.

   • Формула: ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь)
     • Logical_Test: Логический тест – это часть оператора «ЕСЛИ». В данном случае логика D2 = «Гриффиндор». Убедитесь, что ваше значение Logical_Test заключено в кавычки.
     • Value_if_True: Если значение истинно, т. Е. Если студент живет в Гриффиндоре, то это значение мы хотим отобразить. В данном случае мы хотим, чтобы это было число 10, чтобы указать, что ученику были присвоены 10 баллов.Примечание. Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом.
     • Value_if_False: Если значение false – и ученик не , а живет в Гриффиндоре – мы хотим, чтобы в ячейке отображалось «0» за 0 баллов.
   Формула
   • в примере ниже: = IF (D2 = «Гриффиндор», «10», «0»)
   
   

   3. В процентах

   Чтобы вычислить процентную формулу в Excel, введите ячейки, для которых вы найдете процентное значение, в формате = A1 / B1 .Чтобы преобразовать полученное десятичное значение в процент, выделите ячейку, щелкните вкладку «Главная» и выберите «Процент» в раскрывающемся списке чисел.

   Не существует «формулы» Excel для процентов как таковой, но Excel позволяет легко преобразовать значение любой ячейки в проценты, чтобы вам не приходилось вычислять и повторно вводить числа самостоятельно.

   Базовая настройка для преобразования значения ячейки в процент находится на вкладке «Главная» в Excel . Выберите эту вкладку, выделите ячейки, которые вы хотите преобразовать в процент, и щелкните раскрывающееся меню рядом с «Условное форматирование» (эта кнопка меню может сначала сказать «Общие»). Затем выберите «Процент» из появившегося списка параметров. Это преобразует значение каждой выделенной ячейки в процент. См. Эту функцию ниже.

   Имейте в виду, что если вы используете другие формулы, такие как формула деления (обозначенная = A1 / B1 ), для возврата новых значений, ваши значения могут отображаться как десятичные по умолчанию. Просто выделите ячейки до или после выполнения этой формулы и установите для формата этих ячеек значение «Процент» на вкладке «Главная», как показано выше.

   4. Вычитание

   Чтобы выполнить формулу вычитания в Excel, введите вычитаемые ячейки в формате = СУММ (A1, -B1) . Это позволит вычесть ячейку по формуле СУММ, добавив знак минус перед ячейкой, которую вы вычитаете. Например, если A1 было 10, а B1 было 6, = SUM (A1, -B1) выполнит 10 + -6, вернув значение 4.

   Как и проценты, вычитание не имеет собственной формулы в Excel, но это не значит, что это невозможно. Вы можете вычесть любые значения (или значения внутри ячеек) двумя разными способами.

   • Использование формулы = СУММ. Чтобы вычесть несколько значений друг из друга, введите ячейки, которые вы хотите вычесть, в формате = СУММ (A1, -B1) со знаком минус (обозначается дефисом) перед ячейкой, значение которой вы вычитаете. Нажмите клавишу ВВОД, чтобы вернуть разницу между обеими ячейками, заключенную в круглые скобки. Посмотрите, как это выглядит на скриншоте выше.
   • Используем формат = A1-B1 .Чтобы вычесть несколько значений друг из друга, просто введите знак равенства, за которым следует первое значение или ячейка, дефис и значение или ячейка, которые вы вычитаете. Нажмите Enter, чтобы вернуть разницу между обоими значениями.

   5. Умножение

   Чтобы выполнить формулу умножения в Excel, введите умножаемые ячейки в формате = A1 * B1 . В этой формуле звездочка используется для умножения ячейки A1 на ячейку B1. Например, если A1 было 10, а B1 было 6, = A1 * B1 вернет значение 60.

   Вы можете подумать, что умножение значений в Excel имеет свою собственную формулу или использует символ «x» для обозначения умножения между несколькими значениями. На самом деле это просто как звездочка – *.

   Чтобы умножить два или более значений в электронной таблице Excel, выделите пустую ячейку. Затем введите значения или ячейки, которые вы хотите умножить, в формате = A1 * B1 * C1 … и т. Д. Звездочка будет эффективно умножать каждое значение, включенное в формулу.

   Нажмите Enter, чтобы вернуть желаемый продукт.Посмотрите, как это выглядит на скриншоте выше.

   6. Отдел

   Чтобы выполнить формулу деления в Excel, введите разделяемые ячейки в формате = A1 / B1 . В этой формуле используется косая черта «/» для разделения ячейки A1 на ячейку B1. Например, если A1 было 5, а B1 было 10, = A1 / B1 вернет десятичное значение 0,5.

   Деление в Excel – одна из самых простых функций, которые вы можете выполнить. Для этого выделите пустую ячейку, введите знак равенства «=» и введите два (или более) значения, которые вы хотите разделить, с помощью косой черты «/» между ними.Результат должен быть в следующем формате: = B2 / A2 , как показано на скриншоте ниже.

   Нажмите Enter, и желаемое частное должно появиться в первоначально выделенной ячейке.

   7. ДАТА

   Формула ДАТЫ Excel обозначается как = ДАТА (год, месяц, день) . Эта формула вернет дату, соответствующую значениям, указанным в скобках, – даже значениям, полученным из других ячеек. Например, если A1 было 2018, B1 было 7, а C1 было 11, = DATE (A1, B1, C1) вернет 7/11/2018.

   Создание дат в ячейках электронной таблицы Excel может быть непростой задачей время от времени. К счастью, есть удобная формула, которая упрощает форматирование дат. Эту формулу можно использовать двумя способами:

   • Создание дат из ряда значений ячеек. Для этого выделите пустую ячейку, введите «= ДАТА» и в скобках введите ячейки, значения которых создают желаемую дату – начиная с года, затем с номера месяца, затем с дня. Окончательный формат должен выглядеть так: = ДАТА (год, месяц, день).Посмотрите, как это выглядит на скриншоте ниже.
   • Автоматически устанавливает сегодняшнюю дату. Для этого выделите пустую ячейку и введите следующую строку текста: = ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()), ДЕНЬ (СЕГОДНЯ ())). Нажатие клавиши ВВОД вернет текущую дату, когда вы работаете в электронной таблице Excel.

   При любом использовании формулы даты Excel возвращаемая дата должна быть в форме «мм / дд / гг» – если ваша программа Excel не отформатирована по-другому.

   8. Массив

   Формула массива в Excel окружает простую формулу в фигурных скобках в формате {= (Начальное значение 1: Конечное значение 1) * (Начальное значение 2: Конечное значение 2)} . При нажатии ctrl + shift + center будет вычислено и возвращено значение из нескольких диапазонов, а не только отдельные ячейки, добавленные или умноженные друг на друга.

   Вычислить сумму, произведение или частное отдельных ячеек легко – просто используйте формулу = СУММ и введите ячейки, значения или диапазон ячеек, над которыми вы хотите выполнить эту арифметику.Но как насчет нескольких диапазонов? Как найти совокупное значение большой группы ячеек?

   Числовые массивы – это удобный способ одновременного выполнения нескольких формул в одной ячейке, чтобы вы могли видеть одну итоговую сумму, разницу, произведение или частное. Например, если вы хотите узнать общий доход от нескольких проданных единиц, формула массива в Excel идеально подходит для вас. Вот как бы вы это сделали:

   1. Чтобы начать использовать формулу массива, введите «= СУММ» и в скобках введите первые из двух (или трех, или четырех) диапазонов ячеек, которые вы хотите умножить вместе. Вот как может выглядеть ваш прогресс: = СУММ (C2: C5
   2. Затем добавьте звездочку после последней ячейки первого диапазона, который вы включили в формулу. Это означает умножение. После этой звездочки введите второй диапазон ячеек. Вы умножите этот второй диапазон ячеек на первый. Ваш прогресс в этой формуле теперь должен выглядеть так: = СУММ (C2: C5 * D2: D5)
   3. Готовы нажать Enter? Не так быстро … Поскольку эта формула настолько сложна, Excel резервирует другую команду клавиатуры для массивов.После того, как вы закроете круглые скобки в формуле массива, нажмите C trl + Shift + Enter . Это распознает вашу формулу как массив, заключит формулу в фигурные скобки и успешно вернет ваш продукт обоих диапазонов вместе.

   При расчете доходов это может значительно сократить ваше время и усилия. См. Окончательную формулу на скриншоте выше.

   9. СЧЕТ

   Формула СЧЁТ в Excel обозначается как = СЧЁТ (начальная ячейка: конечная ячейка) . Эта формула вернет значение, равное количеству записей, найденных в желаемом диапазоне ячеек. Например, если есть восемь ячеек с введенными значениями от A1 до A10, = COUNT (A1: A10) вернет значение 8.

   Формула СЧЁТ в Excel особенно полезна для больших электронных таблиц , в которых вы хотите увидеть, сколько ячеек содержит фактические записи. Не дайте себя обмануть: Эта формула не выполняет никаких вычислений со значениями самих ячеек. Эта формула предназначена просто для того, чтобы узнать, сколько ячеек в выбранном диапазоне чем-то занято.

   Используя формулу, выделенную жирным шрифтом выше, вы можете легко выполнить подсчет активных ячеек в своей электронной таблице. Результат будет выглядеть примерно так:

   10. СРЕДНЕЕ

   Чтобы выполнить формулу среднего в Excel, введите значения, ячейки или диапазон ячеек, для которых вы вычисляете среднее значение, в формате = СРЕДНЕЕ (число1, число2 и т. Д.) или = СРЕДНЕЕ (Начальное значение: Конечное значение). Это вычислит среднее значение всех значений или диапазона ячеек, включенных в круглые скобки.

   Нахождение среднего значения диапазона ячеек в Excel избавляет вас от необходимости находить отдельные суммы и затем выполнять отдельное уравнение деления для общей суммы. Используя = СРЕДНЕЕ в качестве начального ввода текста, вы можете позволить Excel сделать всю работу за вас.

   Для справки, среднее значение группы чисел равно сумме этих чисел, деленной на количество элементов в этой группе.

   11. СУММЕСЛИ

   Формула СУММЕСЛИ в Excel обозначается как = СУММЕСЛИ (диапазон, критерий, [диапазон суммы]) .Это вернет сумму значений в желаемом диапазоне ячеек, которые все соответствуют одному критерию. Например, = СУММЕСЛИ (C3: C12, “> 70 000”) вернет сумму значений между ячейками C3 и C12 только из ячеек, размер которых превышает 70 000.

   Предположим, вы хотите определить прибыль, полученную вами из списка потенциальных клиентов, связанных с определенными кодами регионов, или рассчитать сумму заработной платы определенных сотрудников – но только если она превышает определенную сумму. Выполнение этого вручную, мягко говоря, занимает немного времени.

   С функцией СУММЕСЛИ этого не должно быть – вы можете легко сложить сумму ячеек, соответствующих определенным критериям, как в примере с зарплатой выше.

   • Формула : = СУММЕСЛИ (диапазон, критерии, [диапазон_суммы])
     
     • Диапазон: Диапазон, который проверяется с использованием ваших критериев.
     • Критерии: Критерии, определяющие, какие ячейки в Criteria_range1 будут добавлены вместе
     • [Sum_range]: Необязательный диапазон ячеек, который вы собираетесь сложить в дополнение к первому введенному диапазону Range .Это поле можно не указывать.

   В приведенном ниже примере мы хотели вычислить сумму зарплат, превышающих 70 000 долларов. Функция СУММЕСЛИ суммирует суммы в долларах, которые превышают это число в ячейках с C3 по C12, по формуле = СУММЕСЛИ (C3: C12, “> 70 000”) .

   12. ОТДЕЛКА

   Формула TRIM в Excel обозначается как = TRIM (текст) . Эта формула удалит все пробелы, введенные до и после текста, введенного в ячейку.Например, если A2 включает имя «Стив Петерсон» с нежелательными пробелами перед именем, = TRIM (A2) вернет «Стив Петерсон» без пробелов в новой ячейке.

   Электронная почта и обмен файлами – прекрасные инструменты на современном рабочем месте. То есть до тех пор, пока один из ваших коллег не пришлет вам рабочий лист с каким-то действительно забавным интервалом. Эти ложные пробелы не только затрудняют поиск данных, но также влияют на результаты, когда вы пытаетесь сложить столбцы чисел.

   Вместо того, чтобы кропотливо удалять и добавлять пробелы по мере необходимости, вы можете убрать любые нерегулярные интервалы с помощью функции TRIM, которая используется для удаления лишних пробелов из данных (кроме одиночных пробелов между словами).

   • Формула : = ОБРЕЗАТЬ (текст).
     • Текст: Текст или ячейка, из которых вы хотите удалить пробелы.

   Вот пример того, как мы использовали функцию TRIM для удаления лишних пробелов перед списком имен. Для этого мы ввели = TRIM (“A2”) в строку формул и скопировали это для каждого имени под ним в новом столбце рядом со столбцом с нежелательными пробелами.

   Ниже приведены некоторые другие формулы Excel, которые могут оказаться полезными по мере роста потребностей в управлении данными.

   13. ЛЕВЫЙ, СРЕДНИЙ и ПРАВЫЙ

   Допустим, у вас есть строка текста в ячейке, которую вы хотите разбить на несколько разных сегментов. Вместо того, чтобы вручную вводить каждый фрагмент кода в соответствующий столбец, пользователи могут использовать ряд строковых функций для деконструкции последовательности по мере необходимости: LEFT, MID или RIGHT.

   ЛЕВЫЙ

   • Цель : Используется для извлечения первых X чисел или символов в ячейке.
   • Формула : = ЛЕВЫЙ (текст, число_символов)
     
     • Текст : строка, из которой вы хотите извлечь.
     • Number_of_characters : количество символов, которые вы хотите извлечь, начиная с самого левого символа.

   В приведенном ниже примере мы ввели = ЛЕВЫЙ (A2,4) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь первые 4 символа кода.

   MID

   • Назначение : Используется для извлечения символов или чисел в середине в зависимости от позиции.
   • Формула : = MID (текст, начальная_позиция, число_символов)
     
     • Текст : строка, из которой вы хотите извлечь.
     • Начальная_позиция : позиция в строке, с которой вы хотите начать извлечение. Например, первая позиция в строке – 1.
     • Number_of_characters : количество символов, которые вы хотите извлечь.

   В этом примере мы ввели = MID (A2,5,2) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь два числа, начиная с пятой позиции кода.

   ПРАВЫЙ

   • Назначение: Используется для извлечения последних X чисел или символов в ячейке.
   • Формула: = ПРАВО (текст, число_символов)
     
     • Текст : строка, из которой вы хотите извлечь.
     • Number_of_characters : количество символов, которые вы хотите извлечь, начиная с самого правого символа.

   Для этого примера мы ввели = ПРАВО (A2,2) в ячейку B2 и скопировали в B3: B6. Это позволило нам извлечь два последних числа кода.

   14. ВПР

   Это старый, но полезный – и он немного более подробный, чем некоторые другие формулы, которые мы здесь перечислили. Но это особенно полезно в тех случаях, когда у вас есть два набора данных в двух разных таблицах и вы хотите объединить их в одну таблицу.

   Моя коллега Рэйчел Спранг, чей учебник «Как использовать Excel» является обязательным к прочтению для всех, кто хочет учиться, использует в качестве примера список имен, адресов электронной почты и компаний.Если у вас есть список имен людей рядом с их адресами электронной почты в одной электронной таблице и список адресов электронной почты тех же людей рядом с названиями их компаний в другой, но вы хотите, чтобы имена, адреса электронной почты и названия компаний этих людей появиться в одном месте – вот тут-то и пригодится ВПР.

   Примечание. При использовании этой формулы необходимо убедиться, что по крайней мере один столбец отображается как идентично в обеих таблицах. Просмотрите свои наборы данных, чтобы убедиться, что столбец данных, который вы используете для объединения информации, точно такой же, без лишних пробелов.

   • Формула: ВПР (значение поиска, массив таблицы, номер столбца, [поиск диапазона])
     • Значение поиска: Идентичное значение в обеих таблицах. Выберите первое значение в своей первой таблице. В следующем примере Спрунга это означает первый адрес электронной почты в списке или ячейку 2 (C2).
     • Массив таблицы: Диапазон столбцов на листе 2, из которого вы собираетесь извлекать данные, включая столбец данных, идентичный вашему значению поиска (в нашем примере, адреса электронной почты) на листе 1, а также столбец данные, которые вы пытаетесь скопировать на лист 1.В нашем примере это «Sheet2! A: B». «A» означает столбец A на листе 2, который является столбцом на листе 2, где перечислены данные, идентичные нашему поисковому значению (электронная почта) в листе 1. «B» означает столбец B, содержащий информацию, доступную только на листе 2, которую вы хотите перевести на лист 1.
     • Номер столбца: Массив таблицы сообщает Excel, где (какой столбец) находятся новые данные, которые вы хотите скопировать на лист 1. В нашем примере это будет столбец «Дом», второй в нашем массиве таблиц, что делает его столбцом номер 2.
     • Поиск диапазона: Используйте FALSE, чтобы гарантировать получение только точных совпадений значений.
   • Формула с переменными из примера Спранга ниже: = ВПР (C2, Sheet2! A: B, 2, FALSE)

   В этом примере Лист 1 и Лист 2 содержат списки, описывающие различную информацию об одних и тех же людях, а общая связь между ними – их адреса электронной почты. Допустим, мы хотим объединить оба набора данных, чтобы вся информация о доме из Листа 2 переводилась в Лист 1.Вот как это будет работать:

   15. СЛУЧАЙНОЕ УСТРОЙСТВО

   Есть отличная статья, в которой формула RANDOMIZE в Excel сравнивается с перетасовкой колоды карт. Вся колода представляет собой столбец, а каждая карта – 52 в колоде – представляет собой ряд. «Чтобы перемешать колоду, – пишет Стив МакДоннелл, – вы можете вычислить новый столбец данных, заполнить каждую ячейку столбца случайным числом и отсортировать книгу на основе поля случайных чисел».

   В маркетинге вы можете использовать эту функцию, когда хотите присвоить случайный номер списку контактов – например, если вы хотите поэкспериментировать с новой почтовой кампанией и должны были использовать слепые критерии, чтобы выбрать, кто будет его получать.Назначая номера указанным контактам, вы можете применить правило: «Любой контакт с цифрой 6 или выше будет добавлен в новую кампанию».

   • Формула: RAND ()
     • Начните с одного столбца контактов. Затем в соседнем столбце введите «RAND ()» без кавычек, начиная со строки верхнего контакта.
   • Для примера ниже: RANDBETWEEN (внизу, вверху)
     • RANDBETWEEN позволяет вам определять диапазон номеров, которые вы хотите назначить.В случае этого примера я хотел использовать от 1 до 10.
     • внизу: Наименьшее число в диапазоне.
     • наверху: Наибольшее число в диапазоне,
   Формула
   • в примере ниже: = СЛУЧМЕЖДУ (1,10)

   Полезный материал, правда? Теперь о вишенке на торте: после того, как вы освоите нужную формулу Excel, вы захотите воспроизвести ее для других ячеек, не переписывая формулу. И, к счастью, для этого тоже есть функция Excel.Посмотрите это ниже.

   Как вставить формулу в Excel для всего столбца

   Чтобы вставить формулу в Excel для всего столбца электронной таблицы, введите формулу в самую верхнюю ячейку нужного столбца и нажмите «Ввод». Затем выделите и дважды щелкните нижний правый угол этой ячейки, чтобы скопировать формулу в каждую ячейку под ней в столбце.

   Иногда вам может понадобиться запустить одну и ту же формулу для всей строки или столбца вашей электронной таблицы. Скажем, например, у вас есть список чисел в столбцах A и B электронной таблицы, и вы хотите ввести отдельные итоги каждой строки в столбец C.

   Очевидно, было бы слишком утомительно настраивать значения формулы для каждой ячейки, чтобы вы находили сумму соответствующих чисел каждой строки. К счастью, Excel позволяет автоматически составлять столбец; все, что вам нужно сделать, это ввести формулу в первую строку. Проверьте следующие шаги:

   1. Введите формулу в пустую ячейку и нажмите «Ввод», чтобы запустить формулу.
   2. Наведите курсор на правый нижний угол ячейки, содержащей формулу.Вы увидите маленький жирный символ «+».
   3. Хотя вы можете дважды щелкнуть этот символ, чтобы автоматически заполнить весь столбец формулой, вы также можете щелкнуть и перетащить курсор вниз вручную, чтобы заполнить только определенную длину столбца. столбец, в который вы хотите ввести формулу, отпустите кнопку мыши, чтобы скопировать формулу. Затем просто проверяйте каждое новое значение, чтобы убедиться, что оно соответствует правильным ячейкам.

   Сочетания клавиш Excel

   1.Быстро выбирайте строки, столбцы или всю таблицу.

   Возможно, у вас не хватает времени. Я имею в виду, а кто нет? Нет времени, нет проблем. Вы можете выбрать всю электронную таблицу одним щелчком мыши. Все, что вам нужно сделать, это просто щелкнуть вкладку в верхнем левом углу листа, чтобы выделить все сразу.

   Просто хотите выделить все в определенном столбце строки? Это так же просто с этими ярлыками:

   Для Mac:

   • Выбрать столбец = Command + Shift + Down / Up
   • Выбрать строку = Command + Shift + Right / Left

   Для ПК:

   • Выбрать столбец = Control + Shift + Down / Up
   • Выбрать строку = Control + Shift + Right / Left

   Этот ярлык особенно полезен, когда вы работаете с большими наборами данных, но вам нужно выбрать только конкретную его часть.

   2. Быстро откройте, закройте или создайте книгу.

   Нужно на лету открывать, закрывать или создавать книгу? Следующие сочетания клавиш позволят вам выполнить любое из вышеперечисленных действий менее чем за минуту.

   Для Mac:

   • Открыть = Команда + O
   • Закрыть = Command + W
   • Создать = Команда + N

   Для ПК:

   • Открыть = Контроль + O
   • Закрыть = Control + F4
   • Создать = Control + N

   3.Отформатируйте числа в валюту.

   Есть необработанные данные, которые вы хотите превратить в валюту? Будь то зарплаты, маркетинговые бюджеты или продажа билетов на мероприятие, решение простое. Просто выделите ячейки, которые вы хотите переформатировать, и выберите Control + Shift + долларов США.

   Числа будут автоматически переведены в суммы в долларах – со знаками доллара, запятыми и десятичными точками.

   Примечание. Этот ярлык также работает с процентами. Если вы хотите пометить столбец числовых значений как «проценты», замените «$» на «%».

   4. Вставьте текущую дату и время в ячейку.

   Независимо от того, регистрируете ли вы сообщения в социальных сетях или отслеживаете задачи, которые вы отмечаете в своем списке дел, вы можете добавить дату и время на свой рабочий лист. Начните с выбора ячейки, в которую вы хотите добавить эту информацию.

   Затем, в зависимости от того, что вы хотите вставить, выполните одно из следующих действий:

   • Вставить текущую дату = Control +; (точка с запятой)
   • Вставить текущее время = Control + Shift +; (точка с запятой)
   • Вставить текущую дату и время = Control +; (точка с запятой) , ПРОБЕЛ, а затем Control + Shift +; (точка с запятой) .

   Другие уловки Excel

   1.

   Настройте цвет вкладок.

   Если у вас есть тонна разных листов в одной книге – что случается с лучшими из нас – упростите определение того, куда вам нужно идти, выделив вкладки цветом. Например, вы можете пометить маркетинговые отчеты за прошлый месяц красным, а текущие – оранжевым.

   Просто щелкните вкладку правой кнопкой мыши и выберите «Цвет вкладки». Появится всплывающее окно, в котором можно выбрать цвет из существующей темы или настроить его в соответствии с вашими потребностями.

   2. Добавьте комментарий к ячейке.

   Если вы хотите сделать заметку или добавить комментарий к определенной ячейке на листе, просто щелкните правой кнопкой мыши ячейку, которую вы хотите прокомментировать, затем нажмите Вставить комментарий . Введите свой комментарий в текстовое поле и щелкните за пределами поля комментария, чтобы сохранить его.

   Ячейки, содержащие комментарии, отображаются в углу маленьким красным треугольником. Чтобы просмотреть комментарий, наведите на него курсор.

   3. Скопируйте и продублируйте форматирование.

   Если вы когда-нибудь тратили время на форматирование листа по своему вкусу, вы, вероятно, согласитесь, что это не самое приятное занятие.На самом деле это довольно утомительно.

   По этой причине, вероятно, вы не захотите повторять процесс в следующий раз – да и не обязаны. Благодаря программе Excel Format Painter вы можете легко скопировать форматирование из одной области рабочего листа в другую.

   Выберите то, что вы хотите воспроизвести, затем выберите опцию Format Painter – значок кисти – на панели управления. Затем указатель отобразит кисть, предлагая вам выбрать ячейку, текст или весь рабочий лист, к которому вы хотите применить это форматирование, как показано ниже:

   4.Определите повторяющиеся значения.

   Во многих случаях повторяющиеся значения – например, повторяющийся контент при управлении поисковой оптимизацией – могут стать проблемой, если не будут исправлены. Однако в некоторых случаях вам просто нужно знать об этом.

   Какова бы ни была ситуация, легко обнаружить любые существующие повторяющиеся значения в вашем рабочем листе всего за несколько быстрых шагов. Для этого щелкните опцию Conditional Formatting и выберите Highlight Cell Rules> Duplicate Values ​​

   Используя всплывающее окно, создайте желаемое правило форматирования, чтобы указать, какой тип дублированного содержимого вы хотите перенести.

   В приведенном выше примере мы искали любые повторяющиеся зарплаты в выбранном диапазоне и отформатировали повторяющиеся ячейки желтым цветом.

   В маркетинге использование Excel неизбежно, но с этими уловками это не должно быть так пугающе. Как говорится, практика ведет к совершенству. Чем больше вы будете использовать эти формулы, ярлыки и уловки, тем больше они станут вашей второй натурой.

   Чтобы копнуть немного глубже, ознакомьтесь с некоторыми из наших любимых ресурсов для изучения Excel. Хотите больше советов по Excel? Прочтите этот пост о том, как создать сводную таблицу с медианами.

   Примечание редактора: этот пост был первоначально опубликован в январе 2019 года и был обновлен для полноты.

   зачем использовать $ в формуле Excel

   При написании формулы Excel $ в ссылках на ячейки сбивает с толку многих пользователей. Но объяснение очень простое. Знак доллара в ссылке на ячейку Excel служит только одной цели – он сообщает Excel, следует ли изменять ссылку при копировании формулы в другие ячейки.И это короткое руководство предоставляет полную информацию об этой замечательной функции.

   Важность ссылки на ячейки Excel трудно переоценить. Получите представление о разнице между абсолютными, относительными и смешанными ссылками, и вы на полпути к освоению мощи и универсальности формул и функций Excel.

   Все вы, наверное, видели знак доллара ($) в формулах Excel и задавались вопросом, что это такое. Действительно, вы можете ссылаться на одну и ту же ячейку четырьмя разными способами, например A1, $ A $ 1, $ A1 и A $ 1.

   Знак доллара в ссылке на ячейку Excel влияет только на одно – он указывает Excel, как обрабатывать ссылку, когда формула перемещается или копируется в другие ячейки. Вкратце, использование знака $ перед координатами строки и столбца создает абсолютную ссылку на ячейку, которая не изменится. Без знака $ ссылка будет относительной и изменится.

   Если вы пишете формулу для одной ячейки, вы можете использовать любой ссылочный тип и в любом случае получить формулу правильно.Но если вы собираетесь скопировать формулу в другие ячейки, выбор соответствующего типа ссылки на ячейку имеет решающее значение. Если вам повезет, вы можете подбросить монетку 🙂 Если вы хотите быть серьезным, потратьте несколько минут на изучение тонкостей абсолютных и относительных ссылок на ячейки в Excel и на то, когда использовать какую из них.

   Что такое ссылка на ячейку Excel?

   Проще говоря, ссылка на ячейку в Excel – это адрес ячейки. Он сообщает Microsoft Excel, где искать значение, которое вы хотите использовать в формуле.

   Например, если вы введете простую формулу = A1 в ячейку C1, Excel извлечет значение из ячейки A1 в ячейку C1:
   

   Как уже упоминалось, до тех пор, пока вы пишете формулу для одной ячейки , вы можете использовать любой ссылочный тип со знаком доллара ($) или без него, результат будет таким же:
   

   Но если вы хотите, чтобы переместил или , скопировав формулу по листу, очень важно выбрать правильный тип ссылки для формулы, чтобы правильно скопировать формулу в другие ячейки.В следующих разделах представлены подробные объяснения и примеры формул для каждого типа ссылки на ячейку.

   Примечание. Помимо ссылочного стиля A1 , где столбцы определяются буквами, а строки – номерами, существует также ссылочный стиль R1C1 , в котором и строки, и столбцы идентифицируются номерами (R1C1 обозначает строку 1, столбец 1).

   Поскольку A1 является эталонным стилем по умолчанию в Excel и используется большую часть времени, в этом руководстве мы обсудим только ссылки типа A1. Если кто-то в настоящее время использует стиль R1C1, вы можете отключить его, щелкнув Файл > Параметры > Формулы , а затем сняв отметку с поля ссылочного стиля R1C1.

   Относительная ссылка на ячейку в Excel (без знака $)

   Относительная ссылка в Excel – это адрес ячейки без знака $ в координатах строки и столбца, например A1 .

   Когда формула с относительными ссылками на ячейки копируется в другую ячейку, ссылка изменяется в зависимости от относительного положения строк и столбцов.По умолчанию все ссылки в Excel являются относительными. В следующем примере показано, как работают относительные ссылки.

   Предположим, у вас есть следующая формула в ячейке B1:

   = A1 * 10

   Если вы скопируете эту формулу в другую строку в том же столбце, скажем, в ячейку B2, формула будет скорректирована для строки 2 (A2 * 10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца A на 10
   

   Если вы скопируете формулу с относительной ссылкой на ячейку в другой столбец в той же строке, Excel изменит ссылку на столбец соответственно:
   

   И если вы скопируете или переместите формулу Excel с относительной ссылкой на ячейку в , другую строку и другой столбец , ссылки на столбец и строку изменятся:
   

   Как видите, использование относительных ссылок на ячейки в формулах Excel – очень удобный способ выполнять одни и те же вычисления на всем листе. Чтобы лучше проиллюстрировать это, давайте обсудим реальный пример.

   Использование относительной ссылки – Excel – пример формулы

   Предположим, у вас есть столбец цен в долларах США (столбец B) на вашем листе, и вы хотите преобразовать их в евро. Зная обменный курс доллара США к евро (0,93 на момент написания), формула для строки 2 очень проста: = B2 * 0,93 . Обратите внимание, что мы используем относительную ссылку на ячейку Excel без знака доллара.

   При нажатии клавиши Enter вычисляется формула, и результат немедленно отображается в ячейке.
   

   Наконечник. По умолчанию все ссылки на ячейки в Excel являются относительными. Таким образом, при написании формулы вы можете добавить относительную ссылку, щелкнув соответствующую ячейку на листе вместо того, чтобы вводить ссылку на ячейку вручную.

   Чтобы скопировать формулу в столбец , наведите указатель мыши на маркер заполнения (маленький квадрат в правом нижнем углу выбранной ячейки). При этом курсор изменится на тонкий черный крестик, и вы удерживаете его и перетаскиваете по ячейкам, которые хотите автоматически заполнить.
   

   Вот и все! Формула копируется в другие ячейки с относительными ссылками, которые правильно настроены для каждой отдельной ячейки. Чтобы убедиться, что значение в каждой ячейке вычисляется правильно, выберите любую из ячеек и просмотрите формулу в строке формул. В этом примере я выбрал ячейку C4 и вижу, что ссылка на ячейку в формуле относится к строке 4, как и должно быть:
   

   Абсолютная ссылка на ячейку Excel (со знаком $)

   Абсолютная ссылка в Excel – это адрес ячейки со знаком доллара ($) в координатах строки или столбца, например $ A $ 1 .

   Знак доллара фиксирует ссылку на заданную ячейку, так что остается неизменным независимо от того, куда перемещается формула. Другими словами, использование $ в ссылках на ячейки позволяет копировать формулу в Excel без изменения ссылок.

   Например, если у вас 10 в ячейке A1 и вы используете абсолютную ссылку на ячейку ( $ A $ 1 ), формула = $ A $ 1 + 5 всегда будет возвращать 15, независимо от того, в каких других ячейках эта формула копируется в.С другой стороны, если вы напишете ту же формулу с относительной ссылкой на ячейку ( A1 ), а затем скопируете ее в другие ячейки столбца, для каждой строки будет вычислено другое значение. Следующее изображение демонстрирует разницу:
   

   Примечание. Хотя мы и говорили, что абсолютная ссылка в Excel никогда не изменяется, на самом деле она меняется, когда вы добавляете или удаляете строки и / или столбцы на вашем листе, и это меняет расположение указанной ячейки.В приведенном выше примере, если мы вставляем новую строку вверху листа, Excel достаточно умен, чтобы скорректировать формулу, чтобы отразить это изменение:
   

   На реальных листах очень редко приходится использовать в формуле Excel только абсолютные ссылки. Однако существует множество задач, требующих использования как абсолютных, так и относительных ссылок, как показано в следующих примерах.

   Использование относительных и абсолютных ссылок на ячейки в одной формуле

   Довольно часто вам может понадобиться формула, в которой некоторые ссылки на ячейки настроены для столбцов и строк, в которые копируется формула, а другие остаются фиксированными для определенных ячеек.Другими словами, вы должны использовать относительные и абсолютные ссылки на ячейки в одной формуле.

   Пример 1. Относительные и абсолютные ссылки на ячейки для вычисления чисел

   В нашем предыдущем примере с ценами в долларах США и евро вы можете не захотеть жестко указывать обменный курс в формуле. Вместо этого вы можете ввести это число в какую-либо ячейку, например C1, и исправить ссылку на эту ячейку в формуле, используя знак доллара ($), как показано на следующем снимке экрана:
   

   В этой формуле (B4 * $ C $ 1) существует два типа ссылок на ячейки:

   • B4 – относительная ссылка на ячейку , которая настраивается для каждой строки, и
   • $ C $ 1 – абсолютная ссылка на ячейку , которая никогда не изменяется независимо от того, куда копируется формула.

   Преимущество этого подхода заключается в том, что пользователи могут рассчитывать цены в евро на основе переменного обменного курса без изменения формулы. После изменения коэффициента конверсии все, что вам нужно сделать, это обновить значение в ячейке C1.

   Пример 2. Относительные и абсолютные ссылки на ячейки для вычисления дат

   Еще одно распространенное использование абсолютных и относительных ссылок на ячейки в одной формуле – это вычисление дат в Excel на основе сегодняшней даты.

   Предположим, у вас есть список дат доставки в столбце B, и вы вводите текущую дату в C1 с помощью функции СЕГОДНЯ ().Вам нужно знать, сколько дней отправляется каждый предмет, и вы можете рассчитать это, используя следующую формулу: = B4- $ C $ 1
   

   И снова мы используем в формуле два ссылочных типа:

   • Относительный для ячейки с первой датой доставки (B4), поскольку вы хотите, чтобы эта ссылка на ячейку изменялась в зависимости от строки, в которой находится формула.
   • Абсолютный для ячейки с сегодняшней датой ($ C $ 1), потому что вы хотите, чтобы эта ссылка на ячейку оставалась постоянной.

   В заключение: всякий раз, когда вы хотите создать статическую ссылку на ячейку Excel, которая всегда ссылается на одну и ту же ячейку, обязательно включите в формулу знак доллара ($), чтобы создать абсолютную ссылку в Excel.

   Ссылка на смешанные ячейки Excel

   Ссылка на смешанную ячейку в Excel – это ссылка, в которой фиксирована буква столбца или номер строки. Например, $ A1 и A $ 1 являются смешанными ссылками. Но что означает каждое из них? Все очень просто.

   Как вы помните, абсолютная ссылка Excel содержит 2 знака доллара ($), которые блокируют столбец и строку.В смешанной ссылке на ячейку только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от относительного положения строки или столбца:

   • Абсолютный столбец и относительная строка , например $ A1. Когда формула с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы она никогда не изменялась. Относительная ссылка на строку без знака доллара зависит от строки, в которую копируется формула.
   • Относительный столбец и абсолютная строка , например A $ 1. В этом типе ссылки ссылка на строку не изменится, а ссылка на столбец изменится.

   Ниже вы найдете пример использования обоих смешанных типов ссылок на ячейки, который, надеюсь, упростит понимание.

   Использование смешанной ссылки в Excel – пример формулы

   В этом примере мы снова будем использовать нашу таблицу конвертации валют. Но на этот раз мы не будем ограничиваться только конвертацией доллара в евро.Что мы собираемся сделать, так это преобразовать долларовые цены в ряд других валют, используя единую формулу !

   Для начала давайте введем коэффициенты конверсии в какую-нибудь строку, скажем, строку 2, как показано на скриншоте ниже. А затем вы пишете только одну формулу для верхней левой ячейки (C5 в этом примере), чтобы рассчитать цену в евро:

   = 5 бат * 2 канадских доллара

   Где B5 – это цена в долларах в той же строке, а C $ 2 – это обменный курс доллара США к евро.

   А теперь скопируйте формулу в другие ячейки в столбце C и после этого автоматически заполните другие столбцы той же формулой, перетащив маркер заполнения. В результате у вас будет 3 разных столбца с ценами, правильно рассчитанные на основе соответствующего обменного курса в строке 2 того же столбца. Чтобы проверить это, выберите любую ячейку в таблице и просмотрите формулу в строке формул.

   Например, выделим ячейку D7 (в столбце GBP). Здесь мы видим формулу = $ B7 * D $ 2 , которая берет цену в долларах США в B7 и умножает ее на значение в D2, которое представляет собой обменный курс доллара США к фунту стерлингов, именно то, что доктор прописал 🙂

   А теперь давайте разберемся, как получается, что Excel точно знает, какую цену брать и на какой обменный курс ее умножать. Как вы уже догадались, трюк делают смешанные ссылки на ячейки ($ B5 * C $ 2).

   • $ B5 – абсолютный столбец и относительная строка . Здесь вы добавляете знак доллара ($) только перед буквой столбца, чтобы привязать ссылку к столбцу A, поэтому Excel всегда использует исходные цены в долларах США для всех преобразований. Ссылка на строку (без знака $) не заблокирована, потому что вы хотите рассчитать цены для каждой строки отдельно.
   • C $ 2 – относительный столбец и абсолютная строка .Поскольку все обменные курсы находятся в строке 2, вы блокируете ссылку на строку, помещая знак доллара ($) перед номером строки. И теперь, независимо от того, в какую строку вы копируете формулу, Excel всегда будет искать обменный курс в строке 2. И поскольку ссылка на столбец является относительной (без знака $), она будет скорректирована для столбца, для которого формула скопировано.

   Как создать ссылку на весь столбец или строку в Excel

   Когда вы работаете с листом Excel, который имеет переменное количество строк, вы можете обратиться ко всем ячейкам в определенном столбце. Чтобы сослаться на весь столбец, просто дважды введите букву столбца и двоеточие между ними, например A: A .

   Ссылка на весь столбец

   Как и ссылки на ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

   • Абсолютная ссылка на столбец , , например, $ A: $ A
   • Ссылка на относительный столбец, например A: A

   И снова вы используете знак доллара ($) в абсолютной ссылке на столбец , чтобы привязать его к определенному столбцу, чтобы ссылка на весь столбец не изменялась при копировании формулы в другие ячейки.

   Относительная ссылка на столбец изменится при копировании формулы или перемещении в другие столбцы и останется неизменной при копировании формулы в другие ячейки в том же столбце.

   Ссылка на всю строку

   Для ссылки на всю строку используется тот же подход, за исключением того, что вы вводите номера строк вместо букв столбцов:

   • Абсолютная ссылка на строку , , например, $ 1: $ 1
   • Относительная ссылка на строку, например 1: 1

   Теоретически вы также можете создать смешанную ссылку на весь столбец или смешанную вся ссылка на строку –, как $ A: A или $ 1: 1, соответственно. Я говорю «теоретически», потому что не могу придумать никакого практического применения таких ссылок, хотя пример 4 доказывает, что формулы с такими ссылками работают именно так, как должны.

   Пример 1. Ссылка на весь столбец Excel (абсолютная и относительная)

   Предположим, у вас есть некоторые числа в столбце B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые строки добавляются в таблицу каждую неделю, поэтому писать обычную формулу SUM () или AVERAGE () для фиксированного диапазона ячеек – не лучший вариант.Вместо этого вы можете сослаться на весь столбец B:

   .

   = СУММ ($ B: $ B) – используйте знак доллара ($) для создания абсолютной ссылки на весь столбец , которая фиксирует формулу в столбце B.

   = СУММ (B: B) – напишите формулу без $, чтобы сделать относительной ссылкой на весь столбец , которая будет изменяться при копировании формулы в другие столбцы.

   Наконечник. При написании формулы щелкните букву столбца, чтобы добавить ссылку на весь столбец в формулу.Как и в случае со ссылками на ячейки, Excel по умолчанию вставляет относительную ссылку (без знака $):

   Таким же образом мы пишем формулу для расчета средней цены во всем столбце B:

   = СРЕДНИЙ (B: B)

   В этом примере мы используем относительную ссылку на весь столбец, поэтому наша формула корректируется правильно, когда мы копируем ее в другие столбцы:
   

   Примечание. При использовании ссылки на весь столбец в формулах Excel никогда не вводите формулу в любом месте одного столбца.Например, может показаться хорошей идеей ввести формулу = СУММ (B: B) в одну из самых пустых нижних ячеек в столбце B, чтобы получить результат в конце того же столбца. Не делай этого! Это создаст так называемую круговую ссылку , и формула вернет 0.

   Пример 2. Ссылка на всю строку в Excel (абсолютная и относительная)

   Если данные в вашем листе Excel организованы в виде строк, а не столбцов, вы можете ссылаться на всю строку в своей формуле. Например, вот как мы можем рассчитать среднюю цену в строке 2:

   = СРЕДНЕЕ (2 доллара США: 2 доллара США) – абсолютная ссылка на всю строку привязана к определенной строке с помощью знака доллара ($).

   = СРЕДНЕЕ (2: 2) – относительная ссылка на всю строку будет изменена при копировании формулы в другие строки.

   В этом примере нам нужна относительная ссылка на всю строку, потому что у нас есть 3 строки данных, и мы хотим вычислить среднее значение в каждой строке, скопировав ту же формулу:
   

   Пример 3.Как сослаться на весь столбец, исключая первые несколько строк

   Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа B5: B, которые включали бы все строки в столбце B, начиная со строки 5. Если вы попытаетесь добавить такую ​​ссылку, ваша формула, скорее всего, вернет ошибку #NAME.

   Вместо этого вы можете указать максимальную строку , чтобы ваша ссылка включала все возможные строки в данном столбце.В Excel 2016, 2013, 2010 и 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии Excel имеют максимум 65 536 строк и 256 столбцов.

   Итак, чтобы найти среднее значение для каждого столбца цен в приведенной ниже таблице (столбцы с B по D), вы вводите следующую формулу в ячейку F2, а затем копируете ее в ячейки G2 и h3:

   = СРЕДНИЙ (B5: B1048576)
   

   Если вы используете функцию СУММ, вы также можете вычесть строки, которые хотите исключить:

   = СУММ (B: B) -SUM (B1: B4)

   Пример 4.Использование смешанной ссылки на весь столбец в Excel

   Как я упоминал несколькими абзацами ранее, вы также можете сделать смешанную ссылку на весь столбец или целую строку в Excel:

   • Смешанная ссылка на столбец, например $ A: A
   • Ссылка на смешанную строку, например $ 1: 1

   Теперь давайте посмотрим, что произойдет, если вы скопируете формулу с такими ссылками в другие ячейки. Предположим, вы ввели формулу = СУММ ($ B: B) в некоторую ячейку, F2 в этом примере. Когда вы копируете формулу в соседнюю правую ячейку (G2), она изменяется на = SUM ($ B: C) , потому что первая B фиксируется знаком $, а вторая – нет.В результате формула сложит все числа в столбцах B и C. Не уверен, имеет ли это какое-либо практическое значение, но вы можете узнать, как это работает:
   

   Предупреждение! Не используйте слишком много ссылок на целые столбцы / строки на листе, потому что они могут замедлить работу Excel.

   Как переключаться между абсолютными, относительными и смешанными ссылками (клавиша F4)

   Когда вы пишете формулу Excel, знак $ можно, конечно, ввести вручную, чтобы изменить относительную ссылку на ячейку на абсолютную или смешанную.Или вы можете нажать клавишу F4, чтобы ускорить процесс. Чтобы ярлык F4 работал, вы должны находиться в режиме редактирования формулы:

   1. Выберите ячейку с формулой.
   2. Войдите в режим редактирования, нажав клавишу F2, или дважды щелкните ячейку.
   3. Выберите ссылку на ячейку, которую вы хотите изменить.
   4. Нажмите F4 для переключения между четырьмя типами ссылок на ячейки.

   Если вы выбрали относительную ссылку на ячейку без знака $, например A1, многократное нажатие клавиши F4 переключает между абсолютной ссылкой с обоими знаками доллара, такими как $ A $ 1, абсолютной строкой A $ 1, абсолютным столбцом $ A1, а затем вернуться к относительной ссылке A1.
   

   Примечание. Если вы нажмете F4, не выбрав ссылку на ячейку, ссылка слева от указателя мыши будет выбрана автоматически и заменена на другой тип ссылки.

   Надеюсь, теперь вы полностью понимаете, что такое относительные и абсолютные ссылки на ячейки, и формула Excel со знаками $ больше не является загадкой. В следующих нескольких статьях мы продолжим изучение различных аспектов ссылок на ячейки Excel, таких как ссылка на другой рабочий лист, трехмерная ссылка, структурированная ссылка, циклическая ссылка и т. Д.А пока я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

   Вас также может заинтересовать

   формул Excel с примерами

   Можно ли легко выучить формулы Microsoft Excel? Ага! В этом руководстве объясняются самые основы формул Excel для начинающих с подробными инструкциями по их написанию и использованию. Он также предоставляет ряд расширенных примеров формул для опытных пользователей. Вы будете удивлены, насколько просто создавать формулы в Excel.

   Если бы вас спросили, что такое Microsoft Excel, что бы вы ответили? Да, все дело в хранении и обработке чисел. Вы можете использовать Excel для вычисления процентов и сложных процентов, подсчета и суммирования ячеек на основе определенных критериев, поиска среднего и даже получения выборочного отклонения для заданного набора значений. Все это можно сделать с помощью формул Excel.

   В этом руководстве мы изучим основы создания и использования формул в Excel. А поскольку один из наиболее эффективных способов обучения – это практика, мы также обсудим ряд примеров формул, чтобы упростить понимание.Вот список тем, которые мы рассмотрим:

   Формулы Microsoft Excel – основы

   В MS Excel формулы – это уравнения, которые выполняют различные вычисления на ваших листах. Хотя за прошедшие годы Microsoft представила несколько новых функций, концепция формул электронной таблицы Excel одинакова во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и ниже.

   • Все формулы Excel начинаются со знака равенства (=).
   • После символа равенства введите либо вычисление , либо функцию .Например, чтобы сложить значения в ячейках с B1 по B5, вы можете:
     • Введите уравнение целиком: = B1 + B2 + B3 + B4 + B5
     • Используйте функцию СУММ: = СУММ (B1: B5)
   • Нажмите клавишу Enter, чтобы заполнить формулу. Сделанный!

   Элементы формул Microsoft Excel

   Когда вы составляете формулу в Excel, вы можете использовать различные элементы, чтобы предоставить исходные данные в формулу и указать, какие операторы должны быть выполнены с этими данными.В зависимости от типа формулы, которую вы создаете, она может включать любую или все следующие части:

   • Константы – числа или текстовые значения, которые вы вводите непосредственно в формулу, например = 2 * 3.
   • Ссылки на ячейки – ссылка на ячейку, содержащую значение, которое вы хотите использовать в формуле Excel, например = СУММ (A1, A2, B5) .

     Чтобы ссылаться на данные в двух или более смежных ячейках, используйте ссылку на диапазон , например A1: A5. Например, чтобы суммировать значения во всех ячейках от A1 до A5 включительно, используйте следующую формулу:
     = СУММ (A1: A5) .

   • Names – определенное имя для диапазона ячеек, константы, таблицы или функции, например = SUM (my_name) .
   Функции
   • – стандартные формулы в Excel, которые выполняют вычисления с использованием значений, указанных в их аргументах.
   • Операторы – специальные символы, указывающие тип операции или расчета, которые необходимо выполнить.

   Операторы в формулах листа Excel

   Чтобы сообщить Microsoft Excel, какой тип операции вы хотите выполнить в формуле, вы используете специальные символы, которые технически называются операторами .В Excel существует 4 типа операторов:

   Использование арифметических операторов в формулах Excel

   Эти операторы используются для выполнения основных математических операций, таких как сложение, вычитание, умножение и деление.

   Оператор	Значение	Пример формулы
   + (плюс)	Дополнение	= A2 + B2
   – (знак минус)	Вычитание Отрицание (меняя знак)	= A2-B2 = -A2 (меняет знак значения в A2)
   * (звездочка)	Умножение	= A2 * B2
   / (косая черта)	Дивизия	= A2 / B2
   % (знак процента)	Процент	= A2 * 10% (возвращает 10% значения в A2)
   ^ (каретка)	Показательная (степень)	= A2 ^ 3 (возводит число в A2 в степень 3)

   Например, если у вас есть цена товара в ячейке A2 и НДС в ячейке B2, вы можете рассчитать сумму НДС, используя следующую процентную формулу: = A2 * B2
   

   Операторы сравнения в формулах Excel

   В формулах Microsoft Excel для сравнения двух значений используются сравнения или логические , операторы . Результатом сравнения всегда является логическое значение ИСТИНА или ЛОЖЬ. В Excel доступны следующие логические операторы:

   Оператор сравнения	Значение	Пример формулы
   =	равно	= A2 = B2
   <>	Не равно	= A2 <> B2
   >	Больше	= A2> B2
   <	Менее	= A2
   > =	Больше или равно	= A2> = B2
   <=	Меньше или равно	= A2 <= B2

   Например, формула = A1 = B1 возвращает ИСТИНА, если ячейки A1 и B1 содержат одно и то же значение (число, текст или дату), в противном случае - ЛОЖЬ.

   Дополнительные сведения и примеры использования операторов сравнения в формулах MS Excel см. В следующем руководстве: Логические операторы Excel - равно, не равно, больше, меньше.

   Оператор объединения текста

   Оператором конкатенации текста в Excel является символ амперсанда ( и ). Вы можете использовать его для объединения двух или более текстовых строк в одну строку.

   Например, если у вас есть коды стран в столбце A и номера телефонов в столбце B, вы можете использовать следующую формулу, чтобы получить номера телефонов в сочетании с кодами стран:

   = A1 & "" & B1

   В приведенной выше формуле мы объединяем пробел между ними, чтобы числа лучше читались:
   

   Тот же результат может быть достигнут с помощью функции СЦЕПИТЬ, и в следующем учебном пособии объясняются все детали: Как объединить текстовые строки, ячейки и столбцы в Excel.

   Справочные операторы в формулах и функциях Excel

   Для передачи значений в формулы MS Excel и отдельных аргументов в функциях Excel используются следующие операторы.

   Двоеточие (:) - это оператор диапазона , который позволяет вам сделать одну ссылку для нескольких ячеек, расположенных между двумя указанными вами ячейками.

   Например, диапазон A1: A00 включает 100 ячеек от A1 до A100. Чтобы найти среднее значение из этих 100 ячеек, используйте следующую формулу: = СРЕДНЕЕ (A1: A00)

   Вы также можете ссылаться на весь столбец (A: A) или на всю строку (1: 1).Например, следующая формула находит сумму всех чисел в столбце A: = СУММ (A: A) . Узнайте больше о ссылках на целый столбец и целую строку.

   Запятая (, ) - используется для разделения аргументов в формулах электронных таблиц Excel. Например, формула = ЕСЛИ (A1> 0, «хорошо», «плохо») читается следующим образом: если A1 больше нуля, вернуть «хорошо», в противном случае - «плохо».

   Примечание. Запятая - это значение по умолчанию Разделитель списка в Северной Америке и некоторых других странах. В европейских странах запятая зарезервирована как десятичный символ , а разделитель списка обычно устанавливается на точку с запятой (;). В этом случае вам необходимо разделить аргументы функции точкой с запятой, например = ЕСЛИ (A1> 0; «хорошо»; «плохо») .

   Итак, если вы пытаетесь создать формулу на своем листе, но Excel не принимает ее и выдает ошибку «неверная формула», перейдите в региональные настройки (панель управления > Регион и язык > Дополнительно Settings) и проверьте, какой символ установлен там как List Separator .Это тот символ, который вам нужно использовать для разделения аргументов в формулах Excel.

   Пробел - это оператор пересечения, который позволяет получить ячейки, общие для двух указанных вами ссылок. Например, если у вас есть список элементов в столбце A и некоторые связанные данные в других столбцах, вы можете получить значение на пересечении
   данного столбца и строки, используя следующую формулу: = B3: D3 C2: C4
   

   В качестве примера формулы из реальной жизни посмотрите, как можно выполнять двусторонний поиск в Excel с помощью оператора именованных диапазонов и пробела.

   Типы формул Excel

   Формулы, которые вы создаете в таблицах Excel, могут быть простыми или сложными:

   • Простые формулы Excel выполняют всего одну математическую операцию, например = 10 * 5 или = СУММ (A1: A10)
   • Сложный (расширенный) Формулы Excel включают несколько вычислений, например = 10 * 5 + 20 или = СУММ (A1: A10) / 2

   Далее в этом руководстве вы найдете подробные инструкции по созданию обоих типов формул электронной таблицы Excel.

   Как создавать формулы в Excel

   Как уже упоминалось, любая формула Excel начинается со знака равенства (=). Итак, какую бы формулу вы ни собирались написать, начните с ввода = либо в ячейке назначения, либо в строке формул Excel. А теперь давайте подробнее рассмотрим, как вы можете составлять разные формулы в Excel.

   Как создавать простые формулы в Excel

   Хотя простые формулы Excel выполняют только одно вычисление, они могут делать это разными способами. Вы можете предоставить исходные данные в виде констант, ссылок на ячейки или определенных имен и выполнять вычисления с помощью математических операторов или функций Excel. Подробные инструкции см. На следующих ресурсах:

   Как создавать расширенные формулы в Excel

   Если у вас есть некоторый опыт работы с простыми формулами Excel, вы можете выполнить несколько вычислений в рамках одной формулы. Следующие примеры показывают, как это можно сделать.

   Создание сложных формул с константами и математическими операторами

   Для правильного вычисления сложной формулы Excel одни операции должны выполняться раньше других.По умолчанию порядок операций в формулах Excel следующий:

   .
   • Математические операции в скобках
   • Степень (экспоненциальные вычисления)
   • Умножение и деление, в зависимости от того, что идет первым в формуле
   • Сложение и вычитание, в зависимости от того, что в формуле наступит раньше

   Например, вы можете использовать следующие формулы для расчета суммы и комиссии:
   

   А теперь давайте разберем эти формулы, чтобы увидеть, как их вычисляет Microsoft Excel:

   Итоговая формула: = $ B2 * $ D2 + $ B2 * $ D2 * $ C2

   • 1-е умножение: $ B2 * $ D2 (цена * кол-во. = сумма)
   • 2 ^nd и 3 ^-е умножения: $ B2 * $ D2 * $ C2 (цена * кол-во *% НДС = сумма НДС)
   • Дополнение: сумма + сумма НДС = итого

   Формула комиссии: = ($ B2 * $ D2 + $ B2 * $ D2 * $ C2) * 10%

   Чтобы рассчитать комиссию 10%, вам нужно умножить сумму на 10%, чтобы вы заключили предыдущий расчет в скобки и получили желаемый результат.

   Конечно, ничто не мешает вам умножить сумму, уже рассчитанную в столбце E, на 10%, в этом случае формула сведется к простому вычислению = E2 * 10% .Однако на больших листах имеет смысл писать независимо вычисляемые формулы, чтобы удаление столбца с одной формулой не нарушило работу других.

   Формулы Excel с вложенными функциями

   В формулах Microsoft Excel вложение одной функции в другую означает использование одной функции в качестве аргумента другой функции. В современных версиях Excel 2016, 2013, 2010 и 2010 можно использовать до 64 вложенных функций. В более старых версиях Excel 2003 и ниже разрешены только до 7 уровней функций.

   Вот очень простой пример вложенной формулы Excel, которая включает функцию СУММ для нахождения суммы и функцию ОКРУГЛ для округления этого числа до ближайшего целого (0 знаков после запятой):

   = ОКРУГЛ (СУММ (B2: B6), 0)
   

   Из всех функций Excel функция IF является вложенной чаще, чем все остальные. Как вы, вероятно, знаете, функция ЕСЛИ используется для оценки указанного условия и возврата одного значения, когда условие выполняется, и другого значения, когда условие не выполняется.Однако, часто цитируя, вам приходится иметь дело с ситуациями, когда существует более двух возможных исходов. И в этом случае вы можете написать несколько функций ЕСЛИ и вложить их друг в друга:
   

   Подробное описание синтаксиса вложенных IF и примеров расширенных формул см. В следующем руководстве: Использование вложенных функций IF в Excel.

   Формулы массива в Excel

   Формулы массива в Excel - это продвинутый пилотаж. Одна формула массива Excel может выполнять тысячи вычислений и заменять сотни обычных формул.Изучение формул массива, безусловно, требует времени и усилий, но оно того стоит.

   Поскольку это руководство предназначено для начинающих, я не буду запугивать вас определениями констант массива и сложных многострочных формул. Я покажу только один очень простой пример формулы массива Excel, который демонстрирует, на что они способны.

   Предположим, у вас есть 2 столбца чисел, столбец A и B. И вы хотите знать, во сколько раз столбец B больше или равен столбцу A, когда значение в столбце B больше 0.

   Эта задача требует сравнения двух диапазонов, и это можно сделать, используя следующую формулу массива:

   = СУММ ((B2: B10> = A2: A10) * (B2: B10> 0))
   

   Примечание. Чтобы правильно ввести формулу массива Excel, необходимо нажать Ctrl + Shift + Enter вместо обычного штриха Enter.

   Чтобы узнать больше о формулах массива Excel, см. Следующие руководства:

   Пользовательские функции Excel

   Хотя Microsoft Excel имеет сотни встроенных функций, вы все равно можете столкнуться с проблемой, для которой не существует предопределенной функции Excel.В этом случае вы можете создать эту функцию самостоятельно ... или попросите кого-нибудь создать ее для вас 🙂

   Такие пользовательские функции называются Пользовательскими функциями (UDF), и они особенно полезны для сложных математических или инженерных расчетов. Как и макросы, пользовательские функции написаны на языке VBA (Visual Basic для приложений). Например, вы можете просмотреть и загрузить пользовательские функции, созданные нашей командой, для подсчета и суммирования ячеек по цвету.

   Абсолютные, относительные и смешанные ссылки на ячейки в формулах Excel

   В Excel существует 3 типа ссылок на ячейки: абсолютные ($ A $ 1), относительные (A1) и смешанные ($ A1 или A $ 1).Все три приведенные выше ссылки относятся к одной и той же ячейке, а знак доллара ($) используется только для одной цели - он сообщает Microsoft Excel, следует ли изменять или не изменять ссылки на ячейки, когда формула перемещается или копируется в другие ячейки.

   Абсолютная ссылка на ячейку ($ A $ 1) - знак $ перед координатами строки и столбца делает ссылку статической и позволяет копировать формулу без изменения ссылок .

   Относительная ссылка на ячейку (A1) - ссылка на ячейку без изменения знака $ в зависимости от относительного положения строк и столбцов в электронной таблице.

   Ссылка на смешанную ячейку - может быть 2-х типов:

   • Абсолютный столбец и относительная строка ($ A1) - знак $ перед буквой столбца блокирует ссылку на указанный столбец, поэтому столбец никогда не изменяется. Относительная ссылка на строку без знака доллара изменяется в зависимости от строки, в которую копируется формула.
   • Относительный столбец и абсолютная строка (A $ 1) - ссылка на строку, заблокированная с помощью $, не изменяется, а ссылка на столбец изменяется.

   На следующем изображении показано, как на практике работают разные типы ссылок.
   

   Дополнительные сведения о ссылках на ячейки Excel и другие примеры формул см. В разделе Зачем использовать $ в формулах Excel - абсолютные и относительные ссылки на ячейки.

   Советы и быстрые клавиши для экономии времени для формул Excel

   Формулы

   в Excel - это мощный многогранный инструмент, который может решать самые разные задачи в ваших таблицах. Конечно, изучение различных аспектов формул и функций Microsoft Excel требует времени, поэтому вы можете почувствовать, что в день не хватает времени, чтобы изучить все.Что ж, хороший способ найти больше времени - сэкономить время 🙂

   • Для переключения между абсолютными, относительными и смешанными ссылками в формуле используйте клавишу F4, как показано в разделе «Переключение между типами ссылок в Excel».
   • Чтобы просмотреть все формулы на листе, нажмите кнопку Показать формулы на вкладке Формулы > Аудит формул или нажмите сочетание клавиш Ctrl + ~.
   От
   • до отредактируйте формулу , нажмите F2, дважды щелкните ячейку или щелкните строку формул.
   • Для отладки формул в Excel выберите часть формулы и нажмите F9. Это позволит вам увидеть фактические значения за ссылками на ячейки.
   • В скопируйте формулу в все ячейки в столбце , введите формулу в первую ячейку, выберите эту ячейку и наведите курсор на маленький квадрат в правом нижнем углу, пока он не изменится на черный крест (который называется
     дескриптором заполнения). Дважды щелкните этот крестик, и формула будет скопирована через весь столбец.
   • Чтобы преобразовать формулы в значения , выберите все ячейки с формулами, которые вы хотите преобразовать, нажмите Ctrl + C, чтобы скопировать эти формулы, затем нажмите Shift + F10, затем нажмите V, а затем нажмите Enter. Shift + F10 + V - это ярлык для специальной вставки в Excel - значения только . Если вы не уверены, что запомните этот ярлык, просто нажмите обычный ярлык для вставки Ctrl + V, щелкните маленькую стрелку справа от кнопки Вставить , чтобы открыть раскрывающийся список, и выберите Вставить значения .Дополнительные сведения см. В разделе «Как заменить формулы на их значения в Excel».

   Формулы Microsoft Excel с примерами

   Excel предоставляет формулы практически для всего, и в современных версиях Microsoft Excel существуют десятки или даже сотни различных функций. Итак, если вы столкнулись с задачей, для которой вы не можете найти решение, скорее всего, вы упускаете формулу, которая может сделать это за вас. Прежде чем тратить часы и часы на выполнение вычислений вручную, уделите несколько минут изучению следующих ресурсов.Это подборка самых популярных формул MS Excel с примерами, сгруппированными по категориям.

   Процентная формула Excel

   Формулы сумм в Excel

   Формула подсчета в Excel

   Формула среднего в Excel

   Формулы даты Excel

   • Как преобразовать текст на дату в Excel - несколько формул для преобразования текста на дату.
   • Как преобразовать дату Excel в текст - формула для преобразования даты в текстовую строку в указанном формате.
   Примеры формул
   • Excel DATE - как получить серийный номер, представляющий дату, сложить и вычесть даты в Excel, вернуть дату на основе значений в других ячейках, преобразовать текстовую строку в дату, а также несколько расширенных примеров формул Excel DATE.
   • Формулы для вычисления дней в Excel (функции WEEKDAY, DAY) - формулы даты для возврата дня недели от даты, получения количества дней в году, определения количества дней между двумя датами и т. Д.
   • Как рассчитать месяцы в Excel (функции МЕСЯЦ и МЕСЯЦ) - примеры формулы даты Excel для извлечения месяца из даты, получения первого и последнего дня месяца, преобразования названия месяца в число и т. Д.
   • Расчет номера недели в Excel (функция WEEKNUM) - как использовать формулы даты в Excel для получения номера недели от даты, преобразования номера недели в дату, получения номера недели в месяце, суммирования значений по номеру недели и т. Д.
   • Как складывать и вычитать даты в Excel - формулы даты для сложения и вычитания дней, недель, месяцев и лет.
   • Как рассчитать разницу между двумя датами (функция РАЗНДАТ) - Формула даты Excel для вычисления разницы между двумя датами в днях, неделях, месяцах или годах.
   • Расчет рабочих дней в Excel (WORKDAY и NETWORKDAYS) - использование формулы даты в Excel для расчета рабочих дней с настраиваемыми параметрами выходных и праздничных дней.
   • Преобразование даты в год и вычисление возраста по дате рождения - формулы возраста Excel и несколько других формул даты для извлечения года из даты, преобразования даты в месяц и год, определения високосных лет.

   Формулы времени в Excel

   Формулы Excel для ВПР

   Формулы условного форматирования Excel

   Что ж, похоже, мы наконец дошли до конца. То, что планировалось как краткое руководство по формулам Excel для начинающих, почти превратилось в объемное руководство, потому что необходимо охватить так много различных аспектов формул Excel. Я очень благодарен всем, кто дочитал эту страницу до конца!

   Вас также может заинтересовать

   Создание эффективных формул с помощью @cell и @row

   Оптимизируйте формулы, чтобы улучшить общую производительность листа и избежать необходимости вручную ссылаться на ячейки в формулах.

   По мере того, как ваши листы становятся больше, эффективное создание формул становится более полезным для их работы. Вот две функции, которые помогут сделать ваши формулы более эффективными: @cell и @row.

   Используйте @cell для более интеллектуальных вычислений ячеек

   Если вы хотите выполнить вычисления в формулах, которые рассматривают диапазоны ячеек, например: СУММЕСЛИ, СУММЕСЛИ, СЧЁТЕСЛИ и СЧЁТЕСЛИМН, вы можете использовать аргумент @cell в критериях функции. Аргумент @cell выполняет вычисление для каждой строки в то же время, когда основная функция (например, СУММЕСЛИ) оценивает критерии в диапазоне, что делает вашу формулу более эффективной.

   Например, используя образец данных на изображении ниже, если вы хотите суммировать стоимость всех задач, срок выполнения которых наступил в 2014 году, вы должны создать следующую формулу:

   = СУММЕСЛИ ([Срок] 1: [Срок] 5, ГОД (@cell) = 2014, Стоимость1: Стоимость5)

   Результатом этой формулы является 875,75 доллара США (сумма затрат для всех задач, срок выполнения которых наступил в 2014 году).

   СОВЕТ. Добавьте в формулу функцию ЕСЛИОШИБКА, чтобы избежать появления ошибки, если @cell обнаружит пустую ячейку в формуле.Например: = СЧЁТЕСЛИ (Дата: Дата; ЕСЛИ ОШИБКА (ГОД (@cell), 0) = 2016)

   Используйте @row для повышения производительности формулы

   С помощью @row вы можете повысить производительность за счет автоматического изменения ссылок на ячейки в столбце (например, статуса ваших задач) для большого количества строк.
   Допустим, мы хотим использовать формулу для автоматического изменения символов в столбце статуса этой таблицы:

   Вот пример формулы, которую вы можете построить для автоматизации изменения цвета шарика RYG в столбце «Статус»:

   = ЕСЛИ (Завершено3 <> 1, ЕСЛИ (СЕГОДНЯ () - [Срок] 3> 0, «Красный», ЕСЛИ (СЕГОДНЯ () - [Срок] 3> -3, «Желтый», «Зеленый») ))

   Приведенная выше формула содержит несколько ссылок на ячейки в строке 3.Добавление строки над строкой 3 приводит к тому, что Smartsheet автоматически обновляет все ссылки на ячейки в формуле до строки 4. Smartsheet также изменяет формулы в каждой строке ниже аналогичным образом. Чем больше формул необходимо изменить, тем сильнее будет влияние на вашу производительность в Smartsheet.

   Чтобы по возможности улучшить производительность ваших листов, замените номера строк на @row в ссылках на ячейки. Вот как будет выглядеть формула с использованием @row:

   = ЕСЛИ (Завершить @ row <> 1, ЕСЛИ (СЕГОДНЯ () - [Срок] @row> 0, «Красный», ЕСЛИ (СЕГОДНЯ () - [Дата выполнения] @row> -3, «Желтый», "Зеленый")))

   Smartsheet не нужно изменять ссылки на ячейки, если строка перемещается, что ускоряет загрузку листа и экономит время.Вы также можете скопировать и вставить эту формулу без необходимости вручную изменять номера строк в ссылках на ячейки, что сэкономит ваше время, когда вам нужно скопировать формулы.
   

   формул Excel: сложные формулы

   Урок 3: Сложные формулы

   / ru / excelformulas / простые-формулы / содержание /

   Введение

   Простая формула - это математическое выражение с одним оператором, например 7 + 9 . 2)

2. M ultiplication и Division , в зависимости от того, что наступит первым вычитание , в зависимости от того, что наступит раньше

Мнемоника, которая поможет вам запомнить заказ: PEMDAS или P lease E xcuse M y D ear A до S ally.

Щелкните стрелки в слайд-шоу ниже, чтобы узнать больше о том, как порядок операций используется для вычисления сложных формул.

• Хотя эта формула может показаться действительно сложной, мы можем использовать порядок действий шаг за шагом, чтобы найти правильный ответ.

• Сначала мы начнем с вычисления всего, что указано в скобках. В этом случае нам нужно вычислить только одно: 6-3 = 3.

• Как видите, формула уже выглядит немного проще.2 = 4.

• Далее мы решим любое умножение и деление, работая слева направо. Поскольку операция деления предшествует умножению, она вычисляется первой: 3/4 = 0,75.

• Теперь посчитаем оставшуюся операцию умножения: 0,75 * 4 = 3.

• Затем мы вычислим любое сложение или вычитание, снова работая слева направо. Сначала идет сложение: 10 + 3 = 13.

• Наконец, у нас осталась одна операция вычитания: 13-1 = 12.

• И теперь у нас есть ответ: 12. Это точно такой же результат, который вы получили бы, если бы вводили формулу в электронную таблицу.

Использование круглых скобок в формуле может быть очень важным. Из-за порядка действий он может полностью изменить ответ. Давайте попробуем решить ту же задачу, но на этот раз добавим скобки к последней части.

Создание сложных формул

В приведенном ниже примере мы продемонстрируем сложную формулу, используя порядок операций.Здесь мы хотим рассчитать стоимость налога с продаж для счета за питание. Для этого запишем нашу формулу как = (D2 + D3) * 0,075 в ячейку D4 . Эта формула суммирует цены на наши товары, а затем умножает это значение на ставку налога 7,5% (которая записывается как 0,075) для расчета стоимости налога с продаж.

Затем таблица следует порядку операций и сначала добавляет значения в скобках: (44,85 + 39,90) = 84,75 долларов США .Затем он умножает это значение на налоговую ставку: 84,75 доллара * 0,075 . Результат покажет, что налог с продаж составляет $ 6,36 .

Особенно важно вводить сложные формулы с правильным порядком действий. В противном случае таблица не будет точно рассчитывать результаты. В нашем примере, если скобки не включены, умножение вычисляется первым и результат неверный. Скобки - лучший способ определить, какие вычисления будут выполняться в формуле первыми.

Чтобы создать сложную формулу, используя порядок операций:

В нашем примере ниже мы будем использовать ссылок на ячейки вместе с числовыми значениями , чтобы создать сложную формулу, которая вычислит общую стоимость для счета-фактуры общественного питания. Формула рассчитает стоимость каждого пункта меню и сложит эти значения.

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку C4 .
2. Введите формулу . В нашем примере мы введем = B2 * C2 + B3 * C3 . Эта формула будет следовать порядку операций, сначала выполняется умножение: 2,29 * 20 = 45,80 и 3,49 * 35 = 122,15 . Затем он сложит эти значения, чтобы вычислить общую сумму: 45,80 + 122,15 .
3. Еще раз проверьте точность формулы, затем нажмите Введите на клавиатуре. Формула вычислит и отобразит результат .В нашем примере результат показывает, что общая стоимость заказа составляет $ 167,95 .

Вы можете добавить скобок к любому уравнению, чтобы облегчить чтение. Хотя это не изменит результат формулы в этом примере, мы могли бы заключить операции умножения в круглые скобки, чтобы уточнить, что они будут вычисляться перед сложением.

Ваша электронная таблица не всегда сообщит вам , если ваша формула содержит ошибку, поэтому вам нужно проверить все свои формулы.Чтобы узнать, как это сделать, ознакомьтесь с уроком «Двойная проверка формул».

Challenge!

1. Откройте существующую книгу Excel. Если хотите, вы можете использовать файл-пример для этого урока.
2. Создайте сложную формулу, которая будет выполнять сложение перед умножением. Если вы используете этот пример, создайте формулу в ячейке D6 , которая сначала добавляет значений ячеек D3 , D4 и D5 , а затем умножает их общее количество на 0.075 . Подсказка: вам нужно подумать о порядке операций, чтобы это работало правильно.

/ ru / excelformulas / относительные-абсолютные-ссылки-ячейки / содержание /

.