Как использовать формулы в excel: Самые полезные формулы Excel — Раздел 5. Урок 1 — Онлайн-курс «Digital-аналитика» — This is Data

Содержание

Объяснение функций ЕСЛИ И ИЛИ • My Online Training Hub

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

В этом уроке мы рассмотрим основы функций И и ИЛИ, а затем применим их к оператору ЕСЛИ. Если вы не знакомы с функцией ЕСЛИ, сначала прочтите это > Объяснение оператора ЕСЛИ в Excel.

Скачать рабочую тетрадь

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

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

 =И(логический1,[логический2]....)

 =И(истинно ли условие 1, И условие 2 true (добавьте дополнительные условия, если хотите)

 =ИЛИ(логический1,[логический2]....)

 =ИЛИ(истинно ли условие 1, истинно ли условие 2 ИЛИ (если хотите, добавьте другие условия)

 =ЕСЛИ(Человек-паук популярен, И он зарабатывает <100 тысяч долларов), рассчитайте его зарплату x 10%, если не ставить "Ноль" в ячейке)

 =ЕСЛИ(И(B2="Да",C2<100),C2x$H$1,"Ноль")

Вы заметите, что сначала вводятся два условия, а затем результаты. У вас может быть более двух условий; на самом деле вы можете иметь до 30, просто разделив каждое условие запятой (хотя см. Предупреждение ниже о том, что вы можете переборщить с этим).

 =ЕСЛИ(Человек-паук популярен, ИЛИ он зарабатывает <100 тысяч долларов), рассчитайте его зарплату x 10%, если не ставить «Ноль» в ячейке)

 =ЕСЛИ(ИЛИ(B2="Да",C2<100),C2x$H$1,"Ноль")

В качестве альтернативы вы можете проверить число, а поскольку функции И и ИЛИ относятся к логическому семейству, вы можете использовать другие проверки, отличные от оператора «меньше» (<), используемого в приведенных выше примерах.

Во встроенную рабочую книгу Excel ниже вставьте формулу (в серых ячейках в столбце E), которая вычисляет бонус, причитающийся каждому продавцу. Бонус в размере 500 долларов США выплачивается, если продавец достигает любой цели в ячейках C24 и C25, в противном случае он получает бонус в размере 0 долларов США.

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

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

Одна путаница заключается в том, что функции не такие, как в Excel; они записываются с использованием М-кода. Поначалу нас расстраивает, что мы не можем перенести имеющиеся у нас знания Excel в Power Query. Но как только мы поймем, как устроены формулы, легко найти то, что вам нужно.

 = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])

 = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Text .1", "Text.2"})

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

 = Table.AddColumn(Источник, «Имя нового столбца», каждое 1+1)

Доступные столбцы
Содержит список столбцов запроса, которые существуют на предыдущем шаге. При выборе столбца и нажатии кнопки «Вставить» (или двойном щелчке имени) имя столбца вставляется в формулу. Это полезно, так как уменьшает количество опечаток, которые мешают нашему коду работать должным образом.
Проверка ошибок
Проверка ошибок помогает нам узнать, правильный ли синтаксис. Например, он проверит, существуют ли соответствующие открывающие и закрывающие скобки. Однако он не скажет нам, дает ли формула правильный результат, только то, что синтаксис правильный. На самом деле, сложно интерпретировать, что означают ошибки. Чтобы узнать больше о типах ошибок, с которыми мы можем столкнуться, ознакомьтесь с этим сообщением: Power Query — распространенные ошибки и способы их исправления.

 =[Столбец 1] + [Столбец 2] //Сложение
=[Столбец 1] - [Столбец 2] //Вычитание
=[Столбец 1] * [Столбец 2] //Умножение
=[Столбец 1] / [Столбец 2] //Деление
=[Столбец 1] и [Столбец 2] // Объединить

 =([Столбец 1] + [Столбец 2]) / [Столбец 3]

 =[Столбец 1] = [Столбец 2] //Равно
=[Столбец 1] > [Столбец 2] //Больше, чем
=[Столбец 1] >= [Столбец 2] // Больше или равно
=[Столбец 1] < [Столбец 2] // Меньше, чем
=[Столбец 1] <= [Столбец 2] //Меньше или равно
=[Столбец 1] <> [Столбец 2] //Не равно 9), который мы используем в Excel, не работает в формулах Power Query.  Вместо этого мы должны использовать формулу  Number.Power . 
  Пример формулы #1 
  На приведенном ниже снимке экрана показаны некоторые основные операции с формулами в действии. 
  Вы можете видеть используемые имена столбцов;  Число 1  делится на сумму  Число 1   +  Число 2  . 
  Пример формулы #2 
  На приведенном ниже снимке экрана показано, что формулы могут быть построены из статических значений. 
  Формула может включать сочетание столбцов и статических значений. 
  Контекст строки 
  Если вы не применили сложную магию формул, формулы в Power Query имеют то, что известно как  Контекст строки  . Это означает, что формула применяется к каждой строке по одной. 
  Типы данных 
  Формулы Power Query требовательны к типам данных. 
  Если столбец, используемый в функции, имеет неправильный тип данных, формула вычислит ошибку. Итак, если тип данных не соответствует требованиям формулы, нам нужно найти способ его преобразования.  Мы можем либо добавить шаг перед созданием пользовательского столбца, либо использовать формулу преобразования. 
  Некоторые из наиболее распространенных формул преобразования: 
    Text.From  — для преобразования чего-либо в текст 
   Date.ToText  — для преобразования даты в текст 
   Date.From 90 063 - Чтобы преобразовать числа в дату 
   Date.FromText  — для преобразования текста в дату 
   Number.ToText  — для преобразования числа в текст 
   Number.From  — для преобразования любых входных данных в число 
 900 52  Number.FromText  — для преобразования текста в число    Logical.From  — для преобразования чисел в их истинные или ложные значения 
   Logical.FromText логические значения True или False 
   Logical.ToText  — для преобразования логических значений True или False в текстовые строки «True» или «False» 
 
  Пример формулы #3 
  Следующая таблица содержит числа и текст.  Как  Номер дома  является числовым, нам нужно преобразовать его в текст, прежде чем объединять его с названием улицы в одну адресную строку. 
  Текст формулы будет таким: 
  =  Text.From([Номер дома])  & " " & [Улица] 
 Жирным шрифтом выделено использование функции  Text.From  для преобразования дома  Введите номер  в тип данных Text перед объединением со столбцом  Street Name .
 Поиск функций 
 Как отмечалось ранее, мы не можем использовать формулы Excel внутри Power Query.
 Библиотека функций Power Query 
 Самый простой способ найти доступные формулы — щелкнуть ссылку «Узнать о формулах Power Query».
 Функции сгруппированы по назначению. Мы рассмотрим группу «Числа», если нам нужна формула, которая использует или возвращает числа. Большинство формул в этом разделе начинаются со слова «Число». Вот несколько примеров:
  Number. Abs  — возвращает абсолютное значение числа 9.0053
  Number.FromText  — возвращает число из текстовой строки
  Number.IsOdd  — возвращает true, если значение нечетное, или false, если оно четное.
 Определенные числовые формулы могут также начинаться с других слов, например:
  Decimal.From  — возвращает десятичную дробь от заданного значения
  Percentage.From  — возвращает процент от заданного значения 900 53
 Как правило, формулы, которые выводят дату, начинаются с даты в имени формулы (например,  Date.Year  ), а формулы, которые выводят текст, начинаются с Text в имени формулы (например,  Text.Start  ).
 Подумайте о функциях Excel; как ты их выучил? Вероятно, вам кто-то показывал, или вам приходилось гадать методом проб и ошибок. Из-за названий функций Power Query гораздо проще найти нужную функцию.
 #Shared для отображения функций Power Query 
 Другой способ обнаружить функцию Power Query — ввести #shared в строку формул.
 Это дает нам полный список функций. Мы можем нажать на любое имя функции, чтобы узнать о ней.
 IntelliSense 
 Power Query имеет встроенный IntelliSense. Это означает, что мы можем начать вводить имя функции, и появится список параметров. Например, на снимке экрана ниже показан список функций, если просто ввести «Tex» в поле формулы.
  Примечание:  На момент написания статьи обнаружена раздражающая ошибка, из-за которой IntelliSense повторяет слова при вводе формул. Посмотрите это видео, чтобы узнать больше о том, как этого избежать: https://www.youtube.com/watch?v=p3hhUZwQGVE
 IntelliSense помогает нам обнаруживать функции и упрощает их использование. Получив имя функции, введите открывающую скобку, и Power Query покажет необходимые аргументы и типы данных. Power Query потребовалось некоторое время, чтобы получить эту функциональность, но теперь, когда она здесь, я не могу жить без нее.
 На снимке экрана выше мы видим, что  Text. Start  имеет  текст  в качестве первого аргумента, который должен быть типом данных  текст .
 Просмотр существующих шагов преобразования 
 Преобразования, которые мы используем через стандартный интерфейс, используют функции. Поэтому мы можем использовать их в качестве руководства, чтобы помочь нам написать наши собственные формулы.
 Допустим, мы хотим извлечь первые 4 буквы из текстовой строки. Мы могли бы использовать  Добавить столбец > Извлечь > Первые символы  с ленты, а затем вставить 4 в диалоговое окно  Вставить первый символ . Затем мы могли бы взглянуть на код M в строке формул для этого примененного шага; это покажет следующее:
 = Table.AddColumn(#"Измененный тип", "Первые символы", каждый  Text.Start([Текстовый столбец], 4)  , введите текст) 
 Раздел, выделенный жирным шрифтом, — это функция  Text.Start  . Эта функция извлекает первые символы из текстовой строки (она аналогична функции LEFT в Excel).
 Это показывает, что в любое время, когда вы застряли, просмотр М-кода для подобных преобразований может привести вас к нужному типу функции.
 Основные насадки для формул 
 Различия между М-кодом и функциями Excel могут сбить нас с толку. Вот некоторые из ключевых отличий:
 М-код чувствителен к регистру 
 В Excel мы можем писать функции как в верхнем, так и в нижнем регистре; Excel понимает, что мы хотим, и преобразует функции в верхний регистр для нас. Но Power Query чувствителен к регистру;  Text.Start  — это имя функции, а  text.start  — нет. Power Query может выполнять некоторые автоматические преобразования текста, чтобы избежать некоторых ошибок, но не полагайтесь на него.
 M начинает отсчет с 0 
  Text.PositionOf  — это функция, которая находит позицию строки в другой строке. Если мы используем следующий код M в качестве примера:
 =Text.PositionOf("Excel Off The Grid","Excel") 
 Это похоже на функцию НАЙТИ в Excel с аргументами в обратном порядке.
 =НАЙТИ("Excel","Excel Off The Grid") 
 Функция Excel возвращает значение 1, а функция Power Query возвращает значение 0. Это связано с тем, что Power Query начинает отсчет с 0. Это может большинству пользователей Excel кажутся странными, но в языках программирования это обычное дело.
 Практика, практика, практика 
 В этом посте мы рассмотрели основы формул Power Query. Я гарантирую, что вы забудете все, что прочитали, если не будете практиковать это в течение следующих нескольких недель или месяцев. Вам, вероятно, придется обратиться к библиотеке функций M, чтобы найти то, что вам нужно в разное время. В этом нет ничего постыдного… на самом деле, в этом весь смысл его существования.
 Хватит болтать с меня; пришло время пойти и попробовать некоторые из них на себе.
  Читать больше сообщений из этой серии 
 Введение в Power Query
 Получение данных в Power Query — 5 распространенных источников данных
 DataRefresh Power Query в Excel: 4 способа и дополнительные параметры Редактор запросов к запросы на обновление
 Знакомство с параметрами закрытия и загрузки Power Query
 Параметры Power Query: 3 метода
 Общие преобразования Power Query (объяснено более 50 мощных преобразований)
 Добавление Power Query: быстрое объединение множества запросов в 1
 Получение данных из папки в Power Query: быстрое объединение файлов
 Получение списка файлов в папке и подпапках с помощью Power Query
 Как отменить сводку в Excel с помощью Power Query (3 способа)
 Power Query: поиск значения в другой таблице с помощью слияния
 Как изменить местоположение исходных данных в Power Query (7 способов)
 Формулы Power Query (как использовать их и подводные камни, которых следует избегать)
 Оператор Power Query If: вложенные операторы if и несколько условий
 Как использовать Power Query Group By для суммирования данных
 Как использовать пользовательские функции Power Query
 Power Query — распространенные ошибки и способы их исправления Советы и подсказки
  Об авторе 
 Привет, меня зовут Марк, и я запускаю Excel Off The Grid.
 Мои родители рассказали мне, что в возрасте 7 лет я объявил, что стану квалифицированным бухгалтером. Либо я был экстрасенсом, либо у меня не было воображения, как это и произошло. Однако мое путешествие по-настоящему началось только в 35 лет.
 В 2015 году я устроился на новую работу, на которой регулярно работал после 22:00. В результате я редко видел своих детей в течение недели. Итак, я начал искать секреты автоматизации Excel. Я обнаружил, что, создав небольшое количество простых инструментов, я могу комбинировать их по-разному, чтобы автоматизировать почти все свои обычные задачи. Это означало, что я мог работать меньше часов (и мне повысили зарплату!). Сегодня я обучаю этим техникам других специалистов в рамках нашей программы обучения, чтобы они тоже могли проводить меньше времени на работе (и больше времени со своими детьми и любимым делом).
  Вам нужна помощь в адаптации этого поста к вашим потребностям? 
 Я предполагаю, что примеры в этом посте не совсем соответствуют вашей ситуации. Мы все используем Excel по-разному, поэтому невозможно написать пост, который удовлетворит все потребности. Потратив время на то, чтобы понять методы и принципы, изложенные в этом посте (и в других местах на этом сайте), вы сможете адаптировать его к своим потребностям.
 Но если вы все еще испытываете трудности, вам следует:
 Почитайте другие блоги или посмотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, открыв для себя собственные решения.
 Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
 Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
 Используйте Excel Rescue, моего партнера-консультанта. Они помогают, предоставляя решения небольших проблем Excel.