Простые формулы: Ошибка: 404 Материал не найден

3. Проценты

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

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

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

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

4. Вычитание

Чтобы выполнить формулу вычитания в Excel, введите ячейки, которые вы вычитаете, в формате =СУММ(A1, -B1) . Это вычтет ячейку, используя формулу СУММ, добавив отрицательный знак перед ячейкой, которую вы вычитаете. Например, если A1 равно 10, а B1 равно 6, =СУММ(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, введите ячейки, которые вы делите, в формате =А1/В1 . В этой формуле используется косая черта «/» для разделения ячейки A1 на ячейку B1. Например, если A1 равно 5, а B1 равно 10, =A1/B1 вернет десятичное значение 0,5.

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

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

7. ДАТА

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

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

• Создание дат из ряда значений ячеек. Для этого выделите пустую ячейку, введите «=ДАТА» и в круглых скобках введите ячейки, значения которых создают желаемую дату — начиная с года, затем номер месяца, затем день. Окончательный формат должен выглядеть так: =ДАТА(год, месяц, день). Посмотрите, как это выглядит на скриншоте ниже.
• Автоматически установить сегодняшнюю дату. Для этого выделите пустую ячейку и введите следующую текстовую строку: =ДАТА(ГОД(СЕГОДНЯ()), МЕСЯЦ(СЕГОДНЯ()), ДЕНЬ(СЕГОДНЯ())). Нажатие Enter вернет текущую дату, когда вы работаете в электронной таблице 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, функция =СЧЕТ(A1:A10) вернет значение 8.

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

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

10. СРЗНАЧ

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

Нахождение среднего диапазона ячеек в Excel избавляет вас от необходимости находить отдельные суммы, а затем выполнять отдельное уравнение деления для вашей суммы. Используя =AVERAGE в качестве начальной текстовой записи, вы можете позволить 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

Формула TRIM в Excel обозначается =ОТРЕЗАТЬ(текст) . Эта формула удалит все пробелы, введенные до и после текста, введенного в ячейку. Например, если ячейка 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. В нашем примере это «Лист2!А:Б». «A» означает столбец A на листе 2, который является столбцом на листе 2, где перечислены данные, идентичные нашему значению поиска (электронной почте) на листе 1. «B» означает столбец B, который содержит информацию, доступную только на листе 2, которую вы хотите перевести на лист 1.
  • Номер столбца: Массив таблиц сообщает Excel, где (какой столбец) находятся новые данные, которые вы хотите скопировать на лист 1. В нашем примере это будет столбец «Дом», второй в нашем массиве таблиц, что делает его столбцом номер 2.
  • Поиск диапазона: Используйте FALSE, чтобы обеспечить получение только точных совпадений значений.
• Формула с переменными из примера Sprung ниже: =ВПР(C2,Лист2!A:B,2,ЛОЖЬ)

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

15. RANDOMIZE

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

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

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

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

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

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

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

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

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

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

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

Для Mac:

• Выбрать столбец = Command + Shift + Down/Up
• Выберите строку = Команда + Shift + Вправо/Влево

Для ПК:

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

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

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

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

Для Mac:

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

Для ПК:

• Открыть = Управление + O
• Закрыть = Управление + F4
• Создать новый = Контроль + N

3. Форматировать числа в валюте.

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

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

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

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

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

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

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

Другие хитрости Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ярлыки Excel экономят ваше время

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

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

Первоначально опубликовано 27 июля 2022 г., 7:00:00, обновлено 27 июля 2022 г.

Темы:

Excel

Не забудьте поделиться этим сообщением!

12 основных формул Excel, которые вам необходимо знать

Итак, вы только начинаете работать с Excel и слышали о том, насколько хороши формулы. Есть только одно — вы мало что знаете о том, как использовать формулы Excel.

Давайте исправим это прямо сейчас. В качестве бонуса мы также составили список из 12 рекомендуемых основных формул Excel.

Загрузите файл с базовыми формулами Excel!

Используйте этот бесплатный файл Excel, чтобы попрактиковаться вместе с учебным пособием.

Введите адрес электронной почты

Для чего в Excel используются формулы?

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

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

Эти операции представлены символами плюс, минус, звездочка и косая черта (+, -, *, /) соответственно. Чтобы сигнализировать о том, что ожидается вычисление, формулы в Excel начинаются со знака равенства (=).

Итак, это формула:

Но это не формула:

В чем разница между формулами и функциями?

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

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

Названия этих функций легко запоминаются, поскольку они близки к действию, которое они выполняют. Например, есть функция СУММ для добавления значений, функция МНИЖЕ для преобразования текста в нижний регистр и функция СЧИТАТЬПУСТОТЫ, которая подсчитывает количество пустых ячеек в диапазоне.

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

Например, вот как найти среднее или среднее значение группы чисел. Мы можем ввести формулу, добавив числа, используя символ плюс (+), а затем, используя косую черту (/), разделив эту сумму на количество значений в наборе данных. Однако вместо выполнения этих шагов в Excel существует предварительно созданная функция СРЗНАЧ, позволяющая выполнить эту задачу с минимальными усилиями с вашей стороны.

Синтаксис функции и возвращаемое значение

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

Все функции имеют следующую общую структуру:

знак равенства > имя функции > открывающая скобка > аргументы > закрывающая скобка

Например, чтобы найти среднее значение значений в ячейках от A2 до A10, будет написана формула СРЗНАЧ. следующим образом:

  =СРЕДНЕЕ(A2:A10)  

При вводе вашей функции Excel помогает вам следующим образом:

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

Различные способы вставки формул 

Excel использует символ равенства для распознавания формул (и, соответственно, функций).

1. Использование явных чисел

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

  =7+2*2 
 

2. Использование ссылок на ячейки

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

  =A7+A8*A8  

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

Примечание:

• Excel следует правилу операций PEMDAS (круглые скобки – показатели степени – умножение – деление – сложение – вычитание). Это означает, что значения в круглых скобках будут обработаны в первую очередь, затем степени и так далее.

Различные способы вставки функций

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

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

3. Вы также можете использовать вкладку Формулы для выбора функции по категории.

Преимущество методов 2 и 3 заключается в определении каждого аргумента и предварительном просмотре результата во время ввода значений или ссылок на ячейки.

12 основных функций Excel, которые вам необходимо знать

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

1. СУММ

Функция СУММ суммирует значения. Вы можете добавить явные числовые значения, ссылки на ячейки, диапазоны или сочетание всех трех. Основной синтаксис:

.
  СУММ(число1, [число2], [число3]...)  

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

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

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

Вот полезный совет: если вы хотите быстро просуммировать значения в непрерывном (неразрывном) диапазоне, перейдите к самой следующей ячейке и щелкните значок Автосумма на вкладке «Главная». Excel вставит функцию СУММ, чтобы найти сумму всех предыдущих ячеек, содержащих числовые значения.

Ярлык Alt = также выполняет это.

2. СРЗНАЧ

Другим основным арифметическим вычислением, о котором заботится Excel, является функция СРЗНАЧ. Как было показано ранее, это устраняет необходимость выполнения двухшагового расчета и, следовательно, является одной из наших рекомендуемых основных функций.

Синтаксис функции СРЗНАЧ:

  СРЗНАЧ(число1, [число2], ….)  

Аргументы также можно вводить в виде диапазона ячеек.

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

3. ЕСЛИ

Функция ЕСЛИ входит в область логических функций. Логические функции в основном проверяют, является ли ситуация истинной или ложной. Функция ЕСЛИ делает еще один шаг вперед, выполняя одно действие, если ситуация верна, и другое действие, если она ложна. Эта функция — отличный пример того, как Excel может превратить простую таблицу данных в аналитический инструмент.

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

Синтаксис функции ЕСЛИ:

  ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь]) 
 

• Logical_test — тестируемый оператор.
• Value_if_true — это значение или выражение, которое Excel должен вернуть, если ячейка проходит логическую проверку.
• Value_if_false — это значение или выражение, которое Excel должен вернуть, если логическая проверка не пройдена.

В случае функции ЕСЛИ, если значение_если_истина опущено, необходимо указать значение_если_ложь , и наоборот. Но оба нельзя опускать.

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

Хотя и второй, и третий аргументы объявлены необязательными, должен быть указан хотя бы один из этих аргументов.

  =ЕСЛИ(B2>=90,«Выдающийся», «»)  

Приведенная выше формула проверяет, больше или равно ли значение в ячейке B2 90. Если это так, Excel возвращает текстовое значение «Выдающийся» ( обратите внимание, что текстовые значения вводятся в двойных кавычках).

Если значение не больше или равно 90, ячейка должна оставаться пустой (обратите внимание на пустые двойные кавычки). Затем эта формула копируется в ячейки с C3 по C8, где C3 оценивает значение в ячейке B3 и так далее.

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

4. СУММЕСЛИМН

СУММЕСЛИМН — очень полезная функция Excel. Он сочетает в себе базовую операцию СУММ с логическим тестом ЕСЛИ, чтобы добавить только те ячейки, которые соответствуют нескольким заданным пользователем критериям.

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

Синтаксис СУММЕСЛИМН:

  СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2], [критерий2],...) 
 

• Sum_range — диапазон ячеек для суммирования.
• Criteria_range1 — диапазон ячеек для оценки.
• Критерии1 — это условие, которому должны удовлетворять ячейки в критерии_диапазона1 .
• Criteria_range2 — это второй диапазон ячеек для оценки.
• Критерии2 — — это условие или критерий, которому должны удовлетворять ячейки в критерии_диапазона2 .

Все аргументы после критерия 1 являются необязательными.

Вот пример работы функции СУММЕСЛИМН.

  =СУММЕСЛИМН($C$2:$C$10,$B$2:$B$10,E2)  

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

5. СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИМН является ещё одним членом семейства функций ЕСЛИ. Он считает только те ячейки, которые удовлетворяют всем заявленным критериям. СЧЁТЕСЛИМН превосходит функцию СЧЁТЕСЛИ, которая позволяет одновременно оценивать только одно условие.

Синтаксис функции СЧЁТЕСЛИМН:

  COUNTIFS(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…) 
 

• Criteria_range1 — это первый оцениваемый диапазон.
• Criteria1 — это первый критерий, которому должны удовлетворять ячейки в критерии_диапазона1 .
• Criteria_range2 — второй оцениваемый диапазон.
• Criteria2 — это первый критерий, который ячейки в критерии_диапазон2 должен удовлетворить.

Все пары после критериев1 являются необязательными. Допускается до 127 пар диапазонов и критериев.

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

  =СЧЁТЕСЛИМН(B2:B10,E1,C2:C10,E2)  

Примечание . Как правило, текстовые значения в СЧЁТЕСЛИМН необходимо заключать в двойные кавычки («»), а числа — нет. Но когда логический оператор (> или <) используется с числом, и число, и оператор должны быть заключены в кавычки.

  =СЧЁТЕСЛИМН(B2:B10,E1,C2:C10,   "   >1500") 
 

6. ВПР

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

Синтаксис ВПР:

  ВПР(искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотра])  

Вот что означают эти аргументы:

• Lookup_value — — это то, что вы хотите найти.
• Table_array — где вы хотите его искать.
• Col_index_num — номер столбца в диапазоне, содержащем возвращаемое значение.
• Range_lookup — введите TRUE для приблизительного совпадения или FALSE для точного совпадения.

Формула

  =VLOOKUP(A14,$A$2:$B$10,2,FALSE)  

вводится в ячейку B14, которая возвращает имя сотрудника, соответствующее идентификационному номеру.

В последние годы VLOOKUP уступает XLOOKUP, который является более гибким и, возможно, более простым в использовании. XLOOKUP доступен в Microsoft 365 и более поздних версиях.

7. COUNT

Функция COUNT подсчитывает количество ячеек, содержащих числа. Вы можете использовать эту функцию, чтобы получить количество записей в числовом поле, которое находится в диапазоне или массиве чисел. Синтаксис функции СЧЁТ:

  =СЧЁТ(значение1, [значение2]...)  

Функция СЧЁТ в ячейке B6 выше определяет наличие трёх числовых значений между ячейками B1 и B5.

8. TRIM

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

Следуйте этому простому формату:

  =TRIM(текст)  

TRIM работает с текстовой строкой, введенной в двойных кавычках, или со ссылкой на одну ячейку.

9. ВЛЕВО 

Функция ВЛЕВО используется для извлечения определенного количества символов из текстовой строки, например определенной части телефонного номера. Синтаксис функции ВЛЕВО:

.
  =LEFT(text, [num_chars])  

Num_chars относится к количеству символов, которые нужно извлечь, считая слева. Если аргумент num_chars опущен, Excel возвращает только крайний левый символ.

10. СПРАВА 

Функция ПРАВИЛЬНО используется для извлечения определенного количества символов из текстовой строки, например определенной части телефонного номера. Синтаксис функции ПРАВИЛЬНО:

.
  =RIGHT(текст, [число_символов])  

Число_символов относится к числу символов, которые необходимо извлечь, считая справа. Если аргумент num_chars опущен, Excel возвращает только самый правый символ.

11. MID

Функция MID используется для извлечения текста из середины текстовой строки. Синтаксис функции MID:

  =MID(text, start_num, num_chars)  

Аргумент start_num сообщает Excel, с какого номера позиции начинать извлечение (считая слева), а num_chars указывает, сколько символов нужно извлечь.

12. СЦЕП

Наша последняя базовая функция Excel — СЦЕП. Эта функция объединяет значения из нескольких ячеек в одно. Это полезно для объединения различных частей текста, например, для объединения имени и фамилии в полное имя.

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

Например,

  СЦЕПЛЕНИЕ (A2, "", B2) 
 

скопирует значение из ячеек A2 и B2, вставив между ними пробел.

Заключение — основные формулы Excel

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

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

Повысьте уровень своих навыков работы с Excel

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

Начать бесплатную пробную версию

Как создать формулу в Excel: вычитание, умножение и многое другое!

Автор: соучредитель Каспер Лангманн , специалист по Microsoft Office.

Научиться создавать формулы в Excel легко.

Если вы никогда не знали, как писать формулы в электронных таблицах Excel, вы попали по адресу.

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

Каспер Лангманн , соучредитель Spreadsheeto

К тому времени, как вы ознакомитесь с этим руководством, вы будете знать, что такое формула и как создать свою собственную.

Оглавление

• Бонус: бесплатный файл упражнений (скачать)

• 1: Что такое формула?

• 2: Как создать формулу?

• 3: The difference between a formula and function

• 4: How to add

• 5: How to subtract

• 5: как умножьте

• 6: Как Разделение

• 7: Как использовать .0084

• 8: How to use exponents

• 9: The order of operations

• Conclusion: Wrapping things up