Как в экселе пользоваться формулами: Занятие 4 Использование простых формул и функций

Excel получит «умное» автозаполнение в веб-версии Microsoft 365 — пользоваться формулами станет гораздо проще

Microsoft добавила несколько удобных функций автоматизации в веб-версию Excel (которая поставляется с Microsoft 365, ранее известной как Office 365) — своего программного обеспечения для работы с электронными таблицами. Новые предложения формул и формулы по примеру помогут автоматизировать некоторые действия, которые раньше приходилось выполнять вручную.

Курс Delivery Management.

Протягом 11 тижнів ознайомтесь з делівері-менеджментом у продуктових та аутсорс-компаніях. Дослідите особливості різних бізнес-доменів і набудете практичний досвід у цій галузі.

Ознайомитись з курсом

Предложения формул: если вы введете знак “=” в ячейку, веб-версия Excel предложит тип формулы, который вам следует использовать, учитывая расположенные рядом данные. Например, в документе с полным квартальным объемом продаж в столбце с пометкой «итого» Excel предложит суммировать диапазоны ячеек.

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

• SUM – функция суммирования значений;
• AVERAGE — функция, вычисляющая среднее арифметическое значение в списке аргументов;
• COUNT — функция, подсчитывающая количество ячеек с числами, а также количество чисел в списке аргументов;
• COUNTA – функция, вычисляющая количество заполненных ячеек в диапазон;
• MIN – функция, возвращающая наименьшее число в наборе значений;
• MAX – функция, возвращающая наибольшее число в наборе значений.

Конечно, предложения формул — не новаторская функция. В Google Sheets уже некоторое время есть нечто подобное, а AutoSum в Excel уже давно известен как способ быстрого применения формул к данным – но для некоторых случаев использование предложений действительно может сэкономить время.

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

Сообщение в блоге Microsoft также содержит объявления о нескольких других функциях, в частности «предложенные ссылки» (функция, которая автоматически исправит поврежденную ссылку на хранящиеся в облаке рабочие документы), функция добавления изображения с альтернативным текстом (доступна для Windows, Mac и веб-версии) Также компания добавляет вложенные типы данных Power Query, и возможность получать данные из динамических массивов для пользователей Windows (Insider).

10 лучших вопросов и ответов на собеседовании по формулам Excel (2023)

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

1) Формула СУММ в Excel: =СУММ (C2,C3,C4,C5)

В Excel Формула СУММ используется для расчета общего числа. Например, здесь мы подсчитали общее количество компьютеров, проданных в разных регионах США, используя формулу = СУММ (C2, C3, C4, C5) , в конце вы получите общую сумму 20 500 долларов, как показано в следующей формуле. В формуле внутри скобок вы должны указать номер столбца или строки, который вы хотите добавить. 9

2) Формула среднего значения Excel: = Среднее Excel, формула среднего, используется для получения среднего значения для любого числа. Например, мы рассчитали средние продажи компьютерных товаров в США. На первом снимке экрана показана формула, т. е.

= среднее (C2, C3, C4, C5) , которую мы применили к нашим данным.

Средняя формула

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

3) Формула SumIF = SUMIF (A2:A7, «Товары нужны», D2:D7)

SumIF дает общее количество любых элементов для выбранных диапазонов. Например, здесь мы хотим рассчитать только общую сумму продаж для единиц программного обеспечения, , чтобы сделать это, мы применим формулу как =СУММЕСЛИ (A2:A7, «программное обеспечение», D2:D7). Здесь A2 и A7 определяют диапазон для программного обеспечения, и таким же образом мы можем найти объем продаж для оборудования. (A2:A7, «аппаратное обеспечение», D2:D7).

Ниже на снимке экрана показано общее количество проданного аппаратного и программного обеспечения в таблице.

Формула СЧЁТЕСЛИ

4) Формула СЧЁТЕСЛИ: СЧЁТЕСЛИ(D2:D7, «Функция»)

Функция СЧЁТЕСЛИ имеет широкое применение; Вы можете применить формулу согласно. Здесь мы взяли простой пример функции СЧЁТЕСЛИ, где наша цель — найти общее количество ячеек, значение которых превышает 3000 долларов. Для того, чтобы узнать, что применим формулу =СЧЁТЕСЛИ(D2:D7,"3000").

Ниже на снимке экрана показано общее количество ячеек, значение которых превышает 3000. …)

Функция конкатенации используется в Excel для соединения различных сегментов или текста для отображения в виде одного предложения. Например, здесь мы хотим отобразить текст как «Нью-Йорк имеет самые высокие продажи в размере 12000 долларов», для этого мы будем использовать формулу = СЦЕПИТЬ (C4, «имеет самые высокие продажи», D4, доллары).

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

Int Formula

7) MAX Formula: =Max(D2:D7)

стоимость 12000$. Точно так же вы можете выполнить ту же формулу, чтобы получить минимальное значение, в формуле вы должны заменить Max на Min.

Ниже на снимке экрана показано самое высокое значение в столбце.

Формула MAX

8) Формула факториала = FACT(число)

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

Формула факториала

9) Формула VLookup = Vlookup(значение, диапазон и получение значения в столбце, отсортирован ли мой список)

Формула VLookup используется, когда вы знаете какую-либо деталь любого объекта или человека, и вы сохраняете другую структуру, основанную на этой детали. Например, здесь у нас есть пример клавиатуры, где вы знаете розничную цену клавиатуры, но не знаете, сколько всего было продано в Калифорнии за счет продажи клавиатуры. Чтобы знать, что вы будете использовать =Vlookup(20,D2:D7,2,False). Эта формула даст вам общую сумму продажи на основе розничной цены. Применяя эту формулу, вы должны убедиться, что все, что вы размещаете в качестве ссылки, должно быть уникальным, например, вы ищете какого-либо конкретного сотрудника с его идентификационным номером, он не должен быть присвоен другим, иначе он покажет ошибку.

При выполнении формулы отображается общая сумма продажи: $2500

Формула VLookup

10) Формула функции ЕСЛИ: ЕСЛИ (E2>2000, верно/неверно) 900 05

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

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

Формула функции ЕСЛИ

Эти вопросы также помогут в устной речи.

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

Формулы — старый способ

При любом моделировании коллеги полагались на формулы Excel, созданные для всей книги, связанные с функциями ВПР или ИНДЕКС/ПОИСКПОЗ. Если бы они были более продвинутыми пользователями Excel, они бы также использовали СУММПРОИЗВ и СМЕЩ/ДВССЫЛ для получения несколько динамичных окончательных результатов.

При создании все эти модели работали так, как ожидалось, но со временем все они обнаружили недостатки.

Было несколько проблем с моделями, созданными с использованием формул Excel. Я упомяну лишь некоторые из них:

1. Гибкость — Какими бы умными и «динамичными» ни были ваши формулы, это все равно просто «формулы», записанные в ячейках. Вы не можете указать формулам в ячейках «B1:B1000» транспонировать и стать формулами в ячейках «B1:ZZ1», но при этом показывать ожидаемые результаты. Даже если вы создадите динамические формулы, используя ДВССЫЛ в сочетании с ИНДЕКС/ПОИСКПОЗ , они все равно не оправдаются, если кто-то захочет изменить измерения, в которых они проводят анализ (например, переключиться с показателей по брендам на показатели по странам). Сейчас и это можно сделать с помощью программирования на VBA, но сколько людей в обычной компании обладают такими навыками?
   • Если вы внимательно прочитали предыдущий абзац, вы увидите, что именно эти функции сделали сводные таблицы такой важной частью Excel. Несовершенство PivotTable заключается в том, что он может выполнять такие сводные данные только с помощью базовых функций (таких как SUM, AVERAGE, COUNT, MIN, MAX). Не имеет расширенных возможностей создания формул
2. Целостность данных — Уверен, что и с вами такое случалось не раз. Вы создаете отличную модель на основе формул, а затем делитесь ею со своими коллегами, чтобы внести свой вклад в предоставленные места в файле (например, смету расходов для каждого продукта). Не прошло и пяти минут, как ваша отличная модель сломалась. Почему? Некоторые коллеги использовали числа Cut/Paste, в результате чего получилось Ошибка #REF во всей рабочей книге. Вы должны найти эти ошибки #REF , возможно, даже повторно отправить свой файл, потратив еще один рабочий день и опоздав с другими задачами.
3. Точность формул — Все виды вмешательства пользователя, такие как вставка/удаление строк, различное форматирование ячеек, слияние, сортировка и т. д., могут нарушить сложные формулы, и в большинстве случаев вы даже не заметите, пока не станет слишком поздно.
4. Удобство использования формул — Формулы Excel нельзя повторно использовать в других частях вашей рабочей книги — например, вы создаете потрясающую формулу на основе 6 различных параметров. Теперь вы хотите применить ту же формулу на следующем листе, но на этом листе параметры находятся не в том месте, где они должны быть. Теперь у вас есть 2 варианта: либо настроить формулу на новые места параметров, либо настроить параметры на нужные места перед применением формулы. Оба эти действия требуют много времени и подвержены ошибкам, и их следует по возможности избегать.

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

Почему меры

Проще говоря, меры — это расширенные формулы для сводной таблицы. Так что теперь вместо просто СУММ, СРЕДНЕГО, СЧЁТ, МИН, МАКС вы можете использовать любую формулу, которую вы себе представляете, в сводной таблице (хорошо, такая таблица называется не сводной таблицей, а 9).0007 PowerPivot , но у них практически одинаковый пользовательский интерфейс). Чтобы написать меры в PowerPivot, вам нужна небольшая лекция о языке формул DAX , и вы готовы к работе. Теперь вы можете писать потрясающие формулы в PowerPivot, при этом все функции поворота сводной таблицы работают безукоризненно.

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

При выборе данных для PivotTable/PowerPivot я настоятельно рекомендую хранить все данные в таблице Excel с помощью функции Insert=>Table! Таким образом, когда вы добавляете новые данные в таблицу, PowerPivot автоматически включает новые строки. Если перед импортом в PowerPivot вам нужно выполнить некоторую очистку или реорганизацию данных, вы можете внести эти изменения в PowerQuery (еще один отличный инструмент, доступный в Excel 2010 и более поздних версиях)!

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

Как насчет решения всех этих проблем с помощью PowerPivot Measures?

PowerPivot Меры по спасению!

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

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

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

Хорошо, теперь давайте попробуем проверить эти две гипотезы.

На основе данных на первом рисунке представьте, что нам нужно создать цену за единицу измерения. Простая формула для этого Объем продаж / Объем продаж . Наше руководство хочет соблюдать эту меру в различных ситуациях. Иногда они хотят смотреть на уровне производителя или бренда, а иногда на основе сегментации. Кроме того, иногда они хотят видеть тенденцию, а иногда хотят видеть долгосрочное развитие (из года в год). Вы не можете создать такую ​​гибкую структуру, используя исключительно формулы Excel и сводные таблицы (вы можете, но это займет у вас значительно больше времени и будет неприемлемо по стоимости/времени). Эта задача с мерами? Кусок торта.

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

Вы вставляете новую меру, перейдя к имени таблицы в полях сводной таблицы -> Щелкните правой кнопкой мыши имя таблицы -> Добавить меру

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

=РАССЧИТАТЬ (

    СУММ ( Таблица1[Значение] ),

    ФИЛЬТР ( Таблица1, Таблица1[Факт] = «Объем продаж»)

   )

    / ВЫЧИСЛИТЬ ( 9 0003

        СУММА ( Таблица1[Значение] ),

        ФИЛЬТР ( Таблица1, Таблица1[Факт] = «Объем продаж»)

  )

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

Развертка PricePerUnit по сегментам за каждый период также всего в 2 клика.

Только представьте, насколько проще и быстрее вы можете создавать и корректировать свой анализ, комбинируя Показатели. Каждая мера является переносимой и может использоваться в любой части вашего отчета/анализа. Вы даже можете создавать меры на основе других мер! Теперь объедините это с новыми возможностями моделирования данных Excel и Relations, это просто безумие!

Подведение итогов

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