Формулы для электронных таблиц Excel и Google, которые нельзя игнорировать

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

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

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

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

К ним относятся операторы IF, команды VLOOKUP, INDEX, MATCH и т. д. 

И это проще, чем вы думаете!

---

Основные выводы

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

• SUM, AVERAGE, COUNT, VLOOKUP, INDEX и MATCH - вот некоторые формулы, которые могут помочь в повседневных вычислениях. Вы также можете поиграть с ними, введя условную логику.

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

---

Что такое формула электронной таблицы

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

И Microsoft Excel, и Google Docs оснащены такими командами, отчасти поэтому они являются основными инструментами на любом рабочем месте. 

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

Помимо использования формул Google Sheets или Excel для дат, вот несколько причин, по которым Excel и Google Sheets имеют сотни команд формул: 

• Быстро автоматизирует вычисления

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

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

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

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

• Сокращает количество ошибок, допускаемых вручную

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

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

Запуск формулы электронной таблицы уменьшает количество ошибок. 

• Логическое соединение данных

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

Он отлично подходит для: 

• Бюджетирование 

• Ведение личных или деловых финансовых записей 

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

• Данные исследований 

• Расчет доходности инвестиций 

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

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

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

• SUM для итогов

Команда SUM() используется для суммирования всех выделенных ячеек. Разумеется, для этого в ячейке должно быть числовое значение. Формула не будет работать ни с одной нечисловой ячейкой. 

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

Так, если я хочу добавить содержимое строк с 3 по 7 в первый столбец, я напишу формулу следующим образом: SUM(A3:A7). Формула работает и для нескольких строк.

Кроме того, эта формула остается неизменной как для Google Sheets, так и для Excel.

Посмотрите на этот пример: 

Помимо простейшей функции SUM, вы можете выполнять следующие типы сложений:

• SUMIF: Складывайте числа в диапазоне, удовлетворяющем одному условию
  
• SUMIFS: складывайте числа в диапазоне, удовлетворяющем нескольким условиям
  
• СУММПРОДУКТ: Перемножайте соответствующие значения в массивах, а затем складывайте результаты
  
• SUMSQ: складывайте квадраты чисел в диапазоне
  
• SUMX2MY2: сложите результат (x² - y²) для соответствующих значений в двух диапазонах.
  
• SUMX2PY2: сложите результат (x² + y²) для соответствующих значений в двух диапазонах.
  
• SUMXMY2: Сложение квадратов разностей (x - y)² для двух диапазонов
  
• DSUM: добавление в столбец значений, соответствующих критериям, определенным в таблице базы данных.
  
• IMSUM: Сложение комплексных чисел.
  
• SERIESSUM: Вычислите сумму степенного ряда по заданным коэффициентам.

• Среднее значение для проницательности

Формула AVERAGE() используется для вычисления среднего значения для заданного набора ячеек в электронной таблице. 

Опять же, работа практически такая же. В скобках указывается номер первой и последней ячейки, разделенные двоеточием, например, AVERAGE(E2:E11), и вы получите среднее значение для выбранных ячеек.

Опять же, формула для среднего значения в Excel такая же, как и в Google Sheets.

Небольшие изменения в формуле AVERAGE могут помочь вам в условных расчетах, например: 

• СРЕДНЕЕ: Вычисление среднего арифметического чисел в диапазоне
  
• AVERAGEA: Используйте его, когда вам нужно получить среднее значение, включая числа, текст и логические значения.
  
• AVERAGEIF: Эта команда предназначена для вычисления среднего значения чисел, удовлетворяющих одному условию
  
• AVERAGEIFS: Вычисляет среднее значение чисел, удовлетворяющих нескольким условиям.
  
• DAVERAGE: Вычисляет среднее значение значений в столбце базы данных, которые соответствуют заданным критериям
  
• COUNT для отслеживания предметов

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

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

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

Формула одинакова как в Google Sheets, так и в Excel.

Например, =COUNT(E1:E100)

Как и формулы SUM и AVERAGE, COUNT можно использовать несколькими разными способами, например, 

• COUNTA: подсчитывает непустые ячейки (числа, текст или значения).
  
• COUNTBLANK: подсчитывает только пустые ячейки в диапазоне.
  
• КОУНТИФ: Подсчитывает ячейки, которые удовлетворяют одному условию
  
• COUNTIFS: используйте его для подсчета ячеек, удовлетворяющих нескольким условиям.
  
• DCOUNT: Подсчитывает числовые значения в столбце базы данных, которые соответствуют заданным критериям
  
• DCOUNTA: используется для подсчета непустых значений в столбце базы данных, которые соответствуют заданным критериям.

Продвинутые формулы для анализа данных

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

• VLOOKUP для поиска

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

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

В нашем примере листа, если в столбце A указаны идентификаторы продуктов, а в столбце C - цена за единицу, мы можем использовать VLOOKUP для поиска определенного идентификатора продукта и получения его цены из столбца C. 

Он будет записан как VLOOKUP(“P-1001”, A:C, 3, FALSE)

• Операторы IF для логики

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

Чтобы написать оператор IF, вы начинаете со знака =, включаете команду IF, раскрываете круглые скобки, а затем описываете условие.

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

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

Вот как вы его напишете: =IF(A1>=50, “Pass”, “Fail”)

Лист просканирует значение в ячейке, если оно больше 50, то покажет, что ученик сдал экзамен. В противном случае будет указано, что он провалился.

• ИНДЕКС И МАТЧ для поиска

INDEX и MATCH используются вместе для поиска информации в таблице более гибким способом, чем формула LOOKUP. 

Функция MATCH отвечает за поиск позиции значения в строке или столбце. Используйте MATCH для поиска значения в таблице, и она не вернет само значение. Она вернет число, обозначающее местоположение значения.

Например, если идентификатор продукта является пятым элементом в списке, MATCH вернет 5.

Функция INDEX использует этот номер позиции для возврата значения из определенного места в таблице или столбце. Таким образом, она просто извлекает любое значение, существующее в указанной вами строке. 

Советы, как избежать распространенных ошибок в формулах

Как вы уже могли заметить, и Excel, и Google Sheets требуют, чтобы вы вводили формулы с точностью 100%.

Любые ошибки в написании формулы не принесут вам результата, вы просто получите сообщение об ошибке с текстом #N/A.

Вот несколько советов, которые помогут вам избежать этого сообщения об ошибке. 

• Внимательно проверяйте скобки

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

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

• Типы данных часов

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

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

• Избегайте круговых ссылок

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

Например, если вы введете SUM(A1:A10) в ячейку A10, вы создадите круговой цикл, в котором ячейка будет постоянно добавлять значение, просуммированное в ячейке A10, и этот процесс никогда не закончится.

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

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

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

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

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

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

Вы также можете комбинировать COUNT или AVERAGE с условиями для более точного анализа данных.

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

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

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

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

Например:

• Владелец малого бизнеса может воспользоваться формулой SUMIF отслеживать расходы по категориям
• Или учитель может сочетать ЕСЛИ с COUNTIF Функции, позволяющие отмечать студентов, отстающих в выполнении заданий
• То же самое можно сказать и о фрилансерах, которые управляют счетами клиентов. Они могут использовать простой VLOOKUP команда, позволяющая извлекать тарифы клиентов из основного списка, не прокручивая строки вручную.

Знать, какая формула подходит именно вам, - почти половина успеха. И именно в этом случае Undetectable's Чатбот с искусственным интеллектом может стать вашим проводником. 

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

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

Узнайте, как наши детектор искусственного интеллекта и гуманизатор могут улучшить ваш контент, ниже!

Заключительные мысли

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

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

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

Попробуйте Необнаруживаемый ИИ сегодня!