Формулы в таблице excel: Функции Excel (по категориям) — Служба поддержки Майкрософт

Таблицы и формулы Excel — WiseData

Просмотров:
375

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

Имя таблицы

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

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

Итоги

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

Как видно итоговая сумма появилась только под полем общей стоимости продаж. Нам нужны еще итоги по полю «TotalCost», среднее значение по «DiscountAmount» и посчитать количество по полю «DiscountQuantity» учитывая только значения больше 0.

Для того что бы добавить итог сумму по полю «TotalCost» встаем курсором в строку «Итог» столбца и в выпадающем списке выбираем «Сумма». Так же встаем в столбце «DiscountAmount» на последнюю строку итоги и выбираем в выпадающем списке «Среднее». С количеством по полю «DiscountQuantity» интереснее. Так же в нужном столбце встаем в последнюю строку и в выпадающем списке выбираем «Другие функции». Откроется привычное окно «Мастер функций» в котором в категории «Статистические» выбираем функцию «СЧЕТЕСЛИ».

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

Нажмем ОК. и наша строка итогов, после всех действий должна принять такой вид:

Интересна особенность вставки формулы итогов через саму строку итогов. Вместо вставки функций СРЗНАЧ, СЧЁТ, СЧЕТЗ, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛЛОН, СТАНДОТКЛОНП, СУММ, ДИСП, ДИСПР Excel вставит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ с необходимыми параметрами, а если выбирать «Другие функции» то вставлена будет именно та функция, которую выбрали. По этому в строке итогов в трех полях будет функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а в одном поле функция СЧЁТЕСЛИ.

Формулы в таблице

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

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

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

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

Обратите внимание на некоторые особенности ввода формул, мы вводили формулу используя имя столбца таблицы, а не имя столбца листа. На самом деле вводя формулу в ячейке L2, мы могли написать формулу как =F2*G2. Такой вариант тоже допустим, и такая формула так же будет распространена на весь столбец таблицы, но тогда будет теряться удобство работы с таблицей. Формула в которой отражается реальное имя столбца более наглядна чем формула со ссылками. В некоторых формулах можно встретить обозначение столбца как [@UnitPrice] в данном случае символ @ обозначает – «эта строка» и фактический может опускаться при вводе формул.

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

Ссылки на таблицы и данные таблицы

Ссылаться на таблицы и на данные таблицы из любого другого места рабочей книги так же просто как добавлять формулу в саму таблицу. Для этого будет использоваться полное имя таблицы или столбца данных. Таблица – универсальный и хороший источник данных для других таблиц и отчетов. Например, если бы у нас таблица содержала только числовые значения, как например результаты каких то наблюдений за показателями, разделенных на несколько столбцов, то что бы просчитать среднее значение всех показаний, достаточно вне таблицы написать формулу =СРЗНАЧ(Таблица1). Где «Таблица1» имя таблицы, в которой содержатся значения, в данном случае мы ссылались бы на всю таблицу целиком. Хотя на самом деле для ссылки в формулах на числовые значения не обязательно что бы таблица содержала только числа. Просто любые не числовые значения будут пропускаться.

Ссылка на столбец таблицы выглядит как Имя_таблицы[имя_столбца]. Так что бы вне таблицы получить сумму всех значений поля SalesAmount можно написать формулу =СУММ(Таблица1[SalesAmount]). То есть что бы сослаться на весь столбец таблицы достаточно указать имя таблицы и имя столбца в квадратных скобках. Такой подход не отменяет стандартных возможностей ссылки на значения таблицы, можно использовать как имя ячейки, например А10 так и диапазон, например А10:С15.

Удобство работы с именами вместо абсолютных ссылок заключается в том, что Вам нет необходимости заботиться о положении данных в таблице, о количестве строк таблицы или том где таблица находится. Ссылка в формулах по именам всегда будет давать верный результат. Укажете имя таблицы, Excel сам найдет ее в рабочей книге, укажете имя столбца Excel сам посчитает, сколько строк в таблице и включит все строки в формулу. Ведь на сколько лучше выглядит формула =СУММА(Таблица1[Сумма_продаж]) чем формула =СУММА(А10:А40). Если Вы не видите, данные Вы не можете точно сказать на что указывает диапазон А10:А40, это тем более может быть не известно другому специалисту, который может работать с книгой вместо Вас, но даже не это главное, если количество строк в таблице изменится то первая формула будет давать правильный результат, а вторую необходимо будет исправить, указав верный диапазон.

2.1 Формулы — Excel для начинающих, первое издание

Цели обучения

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

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

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

Рисунок 2.1 Заполненная рабочая тетрадь по личному бюджету 

Загрузить файл данных: Данные главы 3

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

Чтобы продемонстрировать построение базовой формулы, мы начнем работать с рабочим листом Детали бюджета в рабочей книге личного бюджета, который показан на Рисунок 2.2 . Чтобы завершить этот рабочий лист, мы добавим несколько формул и функций. В таблице 2.1 приведены определения для каждой из категорий расходов, перечисленных в диапазоне A3:A11. Когда вы разрабатываете личный бюджет, эти категории определяются на основе того, как вы тратите свои деньги. Вполне вероятно, что каждый человек может иметь разные категории или определять одни и те же категории по-разному. Поэтому важно пересмотреть определения в Таблица 2. 1 , чтобы понять, как мы определяем эти категории, прежде чем продолжить.

Рисунок 2.2 Рабочий лист сведений о бюджете

Таблица 2.1 Определения категорий расходов

Категория	Определение
Коммунальные услуги	Деньги, потраченные на электричество, тепло и воду, а также на кабель, телефон и доступ в Интернет
Продукты питания	Деньги, потраченные на продукты, туалетные принадлежности и сопутствующие товары
Бензин	Деньги, потраченные на топливо для автомобилей
Одежда	Деньги, потраченные на одежду, обувь и аксессуары
Страхование	Деньги, потраченные на страхование домовладельца или автомобиля
Налоги	Деньги, потраченные на школу и налоги на недвижимость (этот пример личного бюджета предполагает, что мы владеем собственностью).
Развлечения	Деньги, потраченные на развлечения, включая рестораны, билеты в кино и театр, вечеринки и т. д.
Отпуск	Деньги, потраченные на отпуск
Разное	Включает любые другие категории расходов, такие как учебники, программное обеспечение, журналы, школьные или рабочие принадлежности и т. д.

Первая формула, которую мы добавим к Сведения о бюджете  на рабочем листе будут рассчитаны значения ежемесячных расходов. Формула будет построена таким образом, что она берет значения из столбца «Годовые расходы» и делит их на 12. Это покажет, сколько денег будет потрачено в месяц для каждой из категорий, перечисленных в столбце А. Ниже объясняется, как работает эта формула. created:

1. Откройте файл данных с именем Ch3 Data и с помощью команды File/Save As сохраните его под новым именем Ch3 Personal Budget .
2. Щелкните вкладку рабочего листа Сведения о бюджете , чтобы открыть рабочий лист.
3. Щелкните ячейку C3.
4. Введите знак равенства = . Когда первый символ, введенный в ячейку, является знаком равенства, это сигнализирует Excel о необходимости выполнения вычисления или создания логического вывода.
5. Тип  D3 . Это добавляет D3 к формуле, которая теперь является ссылкой на ячейку. Excel будет использовать любое значение, введенное в ячейку D3, для создания выходных данных.
6. Введите символ косой черты /. Это символ деления в Excel. Как показано в Таблице 2.2 , математические операторы в Excel немного отличаются от тех, которые используются в обычном калькуляторе.
7. Введите число  12 . Это делит значение в ячейке D3 на 12. В этой формуле вместо ссылки на ячейку используется число или константа, поскольку оно не изменится. Другими словами, в году всегда будет 12 месяцев.
8. Нажмите клавишу ВВОД.

Таблица 2.2 Математические операторы Excel 9 Степень/Экспонента

Почему?

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

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

На рис. 2.3 показано, как формула появляется в ячейке C3 до нажатия клавиши ENTER. Рисунок 2.4 показывает вывод формулы после нажатия клавиши ENTER. Ежемесячные расходы на коммунальные услуги составляют 250 долларов, поскольку формула берет годовой расход в ячейке D3 и делит его на 12. Если значение в ячейке D3 изменяется, формула автоматически создает новый результат. Мы рассчитываем расходы в месяц для каждой категории, потому что людям часто платят и выставляют счета за эти товары ежемесячно. Эта формула позволяет сравнить ваш ежемесячный доход с вашими ежемесячными счетами, чтобы определить, достаточно ли у вас дохода для оплаты этих расходов.

Рисунок 2.3 Добавление формулы на лист Рисунок 2.4 Вывод формулы для ежемесячных расходов

 

Почему?

Использовать универсальные константы

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

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

1. Щелкните ячейку C3.
2. Наведите указатель мыши на ручку автозаполнения.
3. Когда указатель мыши превратится из белого знака плюса в черный знак плюса, щелкните и перетащите вниз к ячейке C11. Это вставит формулу в диапазон C4:C11.
4. Дважды щелкните ячейку C6. Обратите внимание, что ссылка на ячейку в формуле автоматически меняется на D6.
5. Нажмите клавишу ВВОД.

На рис. 2.5 показаны результаты, добавленные к остальным ячейкам в столбце «Ежемесячные расходы». Для каждой строки формула берет значение из столбца «Годовые расходы» и делит его на 12. Вы также увидите, что дважды щелкнули ячейку D6, чтобы отобразить формулу. Обратите внимание, что Excel автоматически изменил исходную ссылку на ячейку D3 на D6. Это результат относительной ссылки, что означает, что Excel автоматически корректирует ссылку на ячейку относительно ее исходного местоположения, когда она вставляется в новые местоположения ячеек. В этом примере формула была вставлена ​​в восемь ячеек ниже исходной ячейки. В результате Excel увеличил номер строки исходной ссылки на ячейку на единицу для каждой строки, в которую она была вставлена.

Рисунок 2.5 Пример относительной ссылки

 

Почему?

Использование относительных ссылок

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

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

1. Щелкните ячейку F3 на листе Детали бюджета  .
2. Введите знак равенства = .
3. Введите открывающую скобку ( .
4. Щелкните ячейку D3. Это добавит в формулу ссылку на ячейку D3. При построении формул вы можете щелкать ячейки вместо того, чтобы вводить их.
5. Введите знак минус − .
6. Щелкните ячейку E3, чтобы добавить ссылку на эту ячейку в формулу.
7. Введите закрывающую скобку ) .
8. Введите косую черту / для деления.
9. Щелкните ячейку E3. Это завершает формулу, которая вычисляет процентное изменение фактически потраченных долларов в прошлом году по сравнению с запланированными в бюджете долларами на этот год ( см. рис. 2.6 ).
10. Нажмите клавишу ВВОД.
11. Щелкните ячейку F3, чтобы активировать ее.
12. Наведите указатель мыши на ручку автозаполнения.
13. Когда указатель мыши превратится из белого квадрата со знаком плюс в черный знак плюс, щелкните и перетащите вниз к ячейке F11. Это вставит формулу в диапазон F4:F11.

Таблица 2.3 Стандартный порядок математических операций

9

Символ	Заказ
( )	Переопределить стандартный порядок: любые математические вычисления, помещенные в круглые скобки, выполняются первыми и переопределяют стандартный порядок операций. Если в формуле используются слои круглых скобок, Excel сначала вычисляет самые внутренние скобки, а самые внешние — в последнюю очередь.
Сначала: Excel сначала выполняет любые экспоненциальные вычисления.
* или /	Секунда: Excel выполняет любые операции умножения или деления за секунду. Когда в формуле есть несколько экземпляров этих вычислений, они выполняются в порядке слева направо.
+ или —	Третье: Excel выполняет любые вычисления сложения или вычитания третьим. Когда в формуле есть несколько экземпляров этих вычислений, они выполняются в порядке слева направо.

На рис. 2.6 показана формула, которая была добавлена ​​в рабочий лист Детали бюджета для расчета процентного изменения расходов. Скобки были добавлены к этой формуле для контроля порядка операций. Любые математические вычисления, помещенные в круглые скобки, выполняются в первую очередь перед стандартным порядком математических операций (см. Таблицу 2.3) . В этом случае, если скобки не использовались, Excel выдал бы ошибочный результат для этого рабочего листа.

Рис. 2.6 Добавление формулы процентного изменения

На рис. 2.7 показан результат формулы процентного изменения, если убрать круглые скобки. Формула дает результат увеличения на 299900%. Поскольку нет разницы между затратами в LY и годовыми расходами бюджета, результат должен быть равен 0%. Однако без круглых скобок Excel следует стандартному порядку операций. Это означает, что значение в ячейке E3 будет сначала разделено на E3 (3000/3000), что равно 1. Затем значение 1 будет вычтено из значения в ячейке D3 (3000−1), что равно 2,9.99. Поскольку ячейка F3 отформатирована в процентах, Excel выражает вывод как увеличение на 299900%.

Рисунок 2.7 Удаление скобок из формулы процентного изменения

 

Проверка целостности

Имеет ли смысл вывод вашей формулы?

Важно отметить, что точность вывода формулы зависит от того, как она построена. Поэтому всегда проверяйте результат своей формулы, чтобы понять, имеет ли он смысл с данными на вашем листе. Как показано на Рисунок 2.7 , плохо построенная формула может дать неточный результат. Другими словами, вы можете видеть, что нет никакой разницы между Годовыми расходами и LY Расходами на коммунальные услуги. Поэтому результат формулы должен быть 0%. Однако, поскольку в этом случае скобки были удалены, формула явно дает ошибочный результат.

Обновление навыков

Формулы

1. Введите знак равенства = .
2. Щелкните или введите местоположение ячейки. При использовании констант введите число.
3. Введите математический оператор.
4. Щелкните или введите местоположение ячейки. При использовании констант введите число.
5. Используйте круглые скобки там, где это необходимо для управления порядком операций.
6. Нажмите клавишу ВВОД.

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

1. Открыв рабочий лист Сведения о бюджете , щелкните вкладку «Формулы» на ленте.
2. Нажмите кнопку «Показать формулы» в группе команд «Аудит формул». Это отображает формулы на листе вместо отображения математических результатов.
3. Нажмите кнопку «Показать формулы» еще раз. Рабочий лист вернется к отображению вывода формул.

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

Рисунок 2.8 Команда «Показать формулы»

Обновление навыков

Показать формулы

1. Щелкните вкладку «Формулы» на ленте.
2. Нажмите кнопку «Показать формулы» в группе команд «Аудит формул».
3. Нажмите кнопку «Показать формулы» еще раз, чтобы отобразить вывод формулы.

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

Показать формулы

• Удерживая нажатой клавишу CTRL, нажмите символ ударения ` .

Два других инструмента в группе команд «Аудит формул» — это команды «Трассировка прецедентов» и «Трассировка зависимых элементов». Эти команды используются для отслеживания ссылок на ячейки, используемых в формуле. Прецедентная ячейка — это ячейка, значение которой используется в других ячейках. Команда «Отслеживание прецедентов» показывает стрелку, указывающую ячейки или диапазоны (прецеденты), которые влияют на значение активной ячейки. Зависимая ячейка — это ячейка, значение которой зависит от значений других ячеек книги. Команда Trace Dependents показывает, где в формуле есть ссылка на любую заданную ячейку. Ниже приведена демонстрация этих команд:

1. Щелкните ячейку D3 на листе Детали бюджета  .
2. Нажмите кнопку «Отслеживание зависимостей» в группе команд «Аудит формул» на вкладке «Формулы» на ленте. Появится двойная синяя стрелка, указывающая на ячейки C3 и F3 (см. Рисунок 2.9 ). Это указывает на то, что на ячейку D3 ссылаются формулы, введенные в ячейки C3 и F3.
3. Щелкните команду «Удалить стрелки» в группе команд «Аудит формул» на вкладке «Формулы» на ленте. Это удалит стрелку зависимостей трассировки.
4. Щелкните ячейку F3 на листе Детали бюджета  .
5. Нажмите кнопку «Отслеживание прецедентов» в группе команд «Аудит формул» на вкладке «Формулы» на ленте. Появится синяя стрелка, проходящая через ячейки D3 и E3 и указывающая на ячейку F3 (см. Рисунок 2.10 ). Это указывает на то, что ячейки D3 и E3 являются ссылками в формуле, введенной в ячейку F3.
6. Щелкните команду «Удалить стрелки» в группе команд «Аудит формул» на вкладке «Формулы» на ленте. Это удалит стрелку прецедентов трассировки.
7. Сохраните файл личного бюджета Ch3 .

На рис. 2.9 показана стрелка «Отслеживание зависимостей» на рабочем листе Сведения о бюджете . Синяя точка представляет собой активированную ячейку. Стрелки указывают, где ячейка упоминается в формулах.

Рисунок 2.9. Пример трассировки зависимостей

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

Рисунок 2.10 Пример прецедентов трассировки

Обновление навыков

Зависимые трассировки

1. Щелкните ячейку, содержащую число или формулу.
2. Перейдите на вкладку «Формулы» на ленте.
3. Нажмите кнопку «Отслеживание зависимостей» в группе команд «Аудит формул».
4. Используйте стрелки, чтобы определить, где ячейка упоминается в формулах и функциях.
5. Нажмите кнопку «Удалить стрелки», чтобы удалить стрелки с рабочего листа.

 

Обновление навыков

Прецеденты трассировки

1. Щелкните ячейку, содержащую формулу или функцию.
2. Перейдите на вкладку «Формулы» на ленте.
3. Нажмите кнопку «Отслеживание прецедентов» в группе команд «Аудит формул».
4. Используйте точки вдоль линии, чтобы определить, на какие ячейки ссылаются в формуле или функции.
5. Нажмите кнопку «Удалить стрелки», чтобы удалить линию с точками.

Ключевые выводы

• Математические вычисления выполняются с помощью формул и функций.
• Знак равенства = предшествует всем формулам и функциям.
• Формулы и функции должны создаваться со ссылками на ячейки для выполнения сценариев «что, если», когда математические выходные данные пересчитываются при изменении одного или нескольких входных данных.
• Математические операторы в обычном калькуляторе отличаются от тех, которые используются в Excel. Таблица 2.2 «Математические операторы Excel» перечисляет математические операторы Excel.
• При использовании числовых значений в формулах и функциях используйте только неизменяемые универсальные константы, например дни в неделе, месяцы в году и т. д.
• Относительная ссылка автоматически корректирует ссылки на ячейки в формулах и функциях, когда они вставляются в новые места на листе. Это избавляет от необходимости повторно вводить формулы и функции, когда они нужны в нескольких строках или столбцах на листе.
• Скобки должны использоваться для управления порядком операций, когда это необходимо для сложных формул.
• Инструменты аудита формул, такие как Trace Dependents, Trace Precedents и Show Formulas, следует использовать для проверки целостности формул, которые были введены в рабочий лист.

Адаптировано Мэри Шац из серии «Как использовать Microsoft Excel: карьера на практике», адаптировано Фондом Сейлора без указания авторства в соответствии с запросом первоначального создателя или лицензиата и распространяется по лицензии CC BY-NC-SA 3.0.

10 лучших советов по оптимизации и ускорению формул Excel

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

10 советов по оптимизации и ускорению формул Excel

1. Используйте таблицы для хранения данных

Начиная с Excel 2007, вы можете хранить все связанные данные в таблице. Например, данные колл-центра на нашей недавней информационной панели хранятся в таблице. Таблицы можно использовать в формулах со структурными ссылками, их можно использовать в качестве источника для сводных таблиц и т. д. А поскольку таблицы увеличиваются и уменьшаются по мере добавления или удаления данных, ни одна из ваших формул не должна быть динамической. Например, если у вас есть таблица с именем cs , то формула sum(cs[имя_столбца]) относится к сумме всех значений в столбце имя_столбца таблицы cs. Даже если вы добавите больше данных в CS, формула все равно будет работать.

Ресурсы для изучения таблиц Excel:

• Знакомство с таблицами Excel — что это такое и как их использовать?
• Пример: информационная панель обслуживания клиентов — данные и расчеты

2. Используйте именованные диапазоны, именованные формулы

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

Ресурсы для изучения именованных диапазонов и именованных формул :

• Школьная программа Excel : В этом всеобъемлющем курсе я рассказываю о том, как думать и писать лучшие формулы для анализа данных.
• Размышлений в календаре Live [Excel Hero]
• Примеры именованных формул — Календарь на 2023 год в Excel

3. Использование динамических массивов и диапазонов заполнения

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

• ФИЛЬТР для получения списка значений, соответствующих одному или нескольким критериям.
• SORT для сортировки значений
• UNIQUE для устранения повторяющихся значений на лету
• XLOOKUP для выполнения различных поисков
• VSTACK/HSTACK для объединения наборов данных
• TOCOL / TOROW для преобразования таблиц данных в форматы одной строки или столбца
• # или Оператор разлива для управления диапазонами разлива

Узнайте больше о функциях динамического массива здесь:

• Функции динамического массива — подробное введение
• Функции динамического массива — как их использовать [Видео]
• Как использовать XLOOKUP

4. Используйте сводные таблицы

Много раз, даже когда нам не нужны формулы, мы их используем, потому что можем. Сводные таблицы — отличный способ рассчитать множество сводных значений за несколько кликов. После того, как сводная таблица построена, вы можете обращаться к опорным значениям с помощью GETPIVOTDATA или простых ссылок на ячейки. Это уменьшит количество ненужных вычислений. Если вы меняете данные, вы можете просто перейти на ленту ДАННЫЕ и обновить все сводки за один раз. Этот процесс работает гладко, когда вы используете таблиц для хранения данных.

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

Ресурсы для изучения сводных таблиц:

• Введение в сводные таблицы Excel
• Примеры, подробности и файлы для загрузки по сводным таблицам Excel

5. Сортировка данных

Одна из причин низкой производительности заключается в том, что вы ищете что-то в большом количестве несортированных данных. Вы заставляете Excel искать иголку в стоге сена. Много раз мы наследуем несортированные данные посредством импорта данных. Сортируя данные и используя правильные операторы в формулах поиска, мы можем мгновенно ускорить медлительную книгу. Если вы чувствуете, что сортировка данных утомительна, вы даже можете автоматизировать ее с помощью Power Query или процедуры сортировки (с помощью простого макроса VBA).

Примеры сортировки:

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

6. Используйте режим ручного расчета

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

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

7. Использование энергонезависимых формул

В Excel существует класс формул, называемый энергозависимыми формулами. Эти формулы пересчитываются каждый раз при изменении рабочей книги. Примерами изменчивых формул являются RAND, NOW, TODAY, OFFSET и т. д. Поэтому, когда на вашем листе много изменчивых формул, каждый раз, когда вы вносите изменения, все эти формулы необходимо пересчитывать. Таким образом, ваш рабочий лист становится медленным.

Решение? Просто, не используйте изменчивые формулы. Например, вместо использования OFFSET для построения динамического диапазона вы можете использовать INDEX. Поскольку INDEX является энергонезависимым, он работает быстрее. Или, что еще лучше, используйте таблицу.

Дополнительные ресурсы:

• Функция ИНДЕКС Excel
• Неустойчивые функции в Excel

8. Храните формулы на отдельном листе

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

9. Пишите формулы лучше

Вот несколько рекомендаций, которым я следую при написании формул.

• Встроенные формулы, как правило, лучше, чем ваша собственная версия — например, СУММЕСЛИМН проще писать и так же быстро, как СУММПРОИЗВ.
• Не обращайтесь ко всему столбцу, если вам нужно всего несколько значений. Не пишите SUM(A:A), если вы знаете, что значения только в A1:A10.
• ЕСЛИОШИБКА вместо длинных формул ЕСЛИ(ЕОШИБКА. Используйте ЕСЛИОШИБКА, чтобы упростить проверку ошибок.
• Удалить или исправить ошибки формул [как узнать, почему формулы не работают]
• Используйте новые формулы динамического массива вместо старых неуклюжих формул массива
• Удалить или уменьшить количество ссылок на другие книги. Вместо этого используйте Power Query.
• Удалите все именованные диапазоны, которые приводят к ошибкам или отсутствующим ссылкам.
• Попробуйте придумать альтернативные формулы: это не только обострит ваш ум, но и позволит найти лучшие решения.
• Не рассчитывайте что-то, если вам это не нужно.
• Не вычисляйте одно и то же дважды. Используйте первый результат и во второй раз. Используйте LET для выполнения этого.

Ресурсы для написания лучших формул :

• Введение в формулу СУММЕСЛИМН
• Знакомство с формулой XLOOKUP
• Как использовать новые функции динамического массива в Excel
• Введение в формулу СУММПРОИЗВ
• Введение в формулу ЕСЛИОШИБКА
• Экспертиза формулы Excel
• Excel Школьная программа

10. В отчаянные времена нужны отчаянные меры

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

• Замените формулы значениями. Делаю резервную копию формул. Затем я выбираю все, CTRL+C, ALT+ESV (или CTRL Shift V). Сделанный!
• Разработка рабочей книги с нуля: Иногда полезно разработать рабочую книгу заново.
• Заменить ссылки на внешние данные фактическими данными: При необходимости импортируйте данные путем копирования и вставки.
• Уменьшение функциональности: Посмотрите, сможет ли конечный пользователь жить с меньшим количеством функций в рабочей книге.
• Найдите альтернативное решение: Глупо пытаться делать все в Excel. Посмотрите, есть ли какой-либо внешний инструмент, который может сделать это лучше и быстрее.

БОНУС: изучите новые формулы и поэкспериментируйте с ними

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