КАК: Excel Trick для суммирования количества ячеек с переменным спектром — 2021

Потом сделайте формулу в ячейке F1. Ах так:

1. Избрать ячейку F1, Тут будет показан итог этого примера.
2. Избрать Формулы.
3. изберите Math & Trig чтоб открыть раскрывающийся перечень функций.
4. ИзбратьSUM в перечне, чтоб открыть диалоговое окно «Аргументы функций СУММ».

Гнездо НЕПРАВИЛЬНАЯ функция в функцию СУММ

Потом введите функцию INDIRECT в функцию SUM, используя это диалоговое окно.

в Номер 1 введите последующую функцию INDIRECT:

Когда вы осознаете, как отформатировать SUM и вложенные функции INDIRECT, вы можете ввести всю функцию, показанную на шаге 4, конкретно в ячейку результата (в этом примере ячейка F1).

Постройте аргумент для НЕПРЕРЫВНОЙ функции

В приведенном выше примере ссылки на ячейки E1 и E2 находятся вне кавычек. Это, в сочетании с & знак конкатенации, встраивает все, что содержится снутри ячеек E1 и E2, в формулу.

Ах так работают элементы формулы:

• & употребляется для объединения текстовых данных (в этом случае буковкы D) со ссылкой на ячейку (E1 и E2), которая содержит числовое значение.
• ‘ ‘ должен окружать текстовые данные снутри ссылок на ячейки (в этом примере — буковка D).
• : является разделителем меж первой ячейкой и крайней ячейкой в ​​спектре SUM.

В приведенном выше примере исходная точка спектра определяется знаками: "D", & Е1.

Конечная точка спектра определяется знаками: ": D" и E2

Двоеточие нужно включить вовнутрь кавычек.

3-ий амперсанд посреди употребляется для объединения 2-ух частей в один аргумент:

Спектр конфигурации функции SUM

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

Используя способ конкатенации на шаге 3, используйте числа, хранящиеся в ячейках E1 и E2 в электрической таблице, чтоб поменять спектр ячеек для функции SUM.

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

1. Избрать ячейку E1.
2. Введите число 3.
3. Нажмите Войти.
4. Избрать ячейку E2.
5. Введите число 6.
6. Нажмите Войти.
7. Ответ в ячейке F1 изменяется на 90. Это сумма чисел, содержащихся в ячейках D3-D6.

Чтоб узреть функцию INDIRECT в действии, вставьте новейшую ячейку в ячейку D3. Это сдвигает все другие ячейки вниз. Новенькая сумма — это сумма ячеек D3-D7. Функция INDIRECT динамически изменяет весь спектр, чтоб включить весь перечень, который вы сначало ввели, плюс добавленная новенькая ячейка.

#REF! Значение ошибки

#REF! значение ошибки возникает в ячейке F1, если функция INDIRECT:

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

Как применять функцию CONCATENATE Excel для объединения ячеек

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

Используйте функцию DGET Excel для суммирования огромных таблиц

Функция DGET является одной из функций базы данных Excel; он употребляется для подсчета количества записей в базе данных, соответственных определенным условиям.

Функция Excel ISNUMBER для поиска ячеек с номерами

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

Функция SUBTOTAL в Гугл Таблицах (обычное управление)

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

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

• Вычисляет метрики со сокрытыми либо отфильтрованными строчками данных
• Динамически выбирает функции

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

Что такое многофункциональный контроль?

Элементы управления функциями докладывают определенной функции в Гугл Таблицах, как себя вести. В случае функции SUBTOTAL вы сможете отыскать SUM, AVERAGE, MAX и т. д.

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

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

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

SUBTOTAL- Элементы управления функциями

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

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

Давайте поглядим, как элементы управления функциями вписываются в синтаксис.

SUBTOTAL Синтаксис

Для работы функции SUBTOTAL для вас будет нужно как минимум два аргумента. 1-ый — это один из частей управления функциями, вышеперечисленных, а 2-ой — спектр данных.

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

В качестве примера воспользуемся последующими аргументами синтаксиса.

Так как 1-ый аргумент функции SUBTOTAL является элементом управления, он докладывает функции, что необходимо применить хотимое агрегирование. В этом примере 9 — это избранный многофункциональный отран управления.

Если вы поглядите на приведенную выше таблицу, вы увидите, что внедрение 9 в синтаксисе применит функцию SUM к данному спектру B2: B7, включая любые сокрытые значения.

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

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

Это также будет включать в расчет набор данных из A2: A7.

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

Почему вы должны применять функцию SUBTOTAL

Может показаться, что заместо этого для расчета каждой промежной суммы проще применять обычную встроенную функцию. В конце концов, в нашем первом примере вы могли бы так же просто применять синтаксис = SUM, (B2: B7) и все равно получить верный ответ.

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

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

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

Вы сможете избежать этого, используя синтаксис SUM функции =SUM(C9+C18+C27), чтоб получить верный ответ. Но это еще наиболее неспешный и мучительный метод, чем внедрение функции SUBTOTAL . Также намного проще ошибиться, щелкая вручную либо вводя каждое поле вручную.

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

Вы сможете созидать, что общая сумма сейчас имеет верный ответ — 70. Это соединено с тем, что функция SUBTOTAL игнорирует повторяющиеся данные, а функция SUM — нет. Это достигается методом игнорирования всех других функций SUBTOTAL в данном спектре.

Эта теория применяется ко всем сравнимым функциям управления, применяемым функцией SUBTOTAL, а не только лишь SUM.

Взаимодействие функции SUBTOTAL с отфильтрованными и сокрытыми данными

Посмотрите на последующий набор данных.

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

• C15 = SUM(C2: C13)
• C16 = SUBTOTAL (9; C2: C13)
• C17 = SUBTOTAL (109; C2: C13)

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

Отфильтрованные данные

В последующем примере мы отфильтровали по брендам, чтоб показать реализации лишь 1-го.

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

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

Сокрытые данные

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

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

Заключение

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

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

Сводные таблицы Excel — фильтрация данных

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

• Фильтрация с внедрением слайсеров.
• Фильтрация с внедрением фильтров отчетов.
• Фильтрация данных вручную.
• Фильтрация с внедрением Label Filters.
• Фильтрация с внедрением Value Filters.
• Фильтрация с внедрением фильтров даты.
• Фильтрация с внедрением Top 10 Filter.
• Фильтрация с внедрением временной шкалы.

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

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

Фильтры отчетов

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

Перетащите область из строк в фильтры в областях сводной таблицы.

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

Вы заметите, что

Значения торговца показываются в строчках.

Значения месяца показываются в столбцах.

Фильтр регионов возникает сверху, по дефлоту выбрано ВСЕ.

Суммирующим значением является сумма суммы заказа.

Сумма суммы заказа зависимо от торговца отображается в столбце Общая сумма.

Сумма суммы заказа По месяцам возникает в строке Итого.

Нажмите на стрелку в поле справа от области фильтра.

Значения торговца показываются в строчках.

Значения месяца показываются в столбцах.

Фильтр регионов возникает сверху, по дефлоту выбрано ВСЕ.

Суммирующим значением является сумма суммы заказа.

Сумма суммы заказа зависимо от торговца отображается в столбце Общая сумма.

Сумма суммы заказа По месяцам возникает в строке Итого.

Нажмите на стрелку в поле справа от области фильтра.

Покажется раскрывающийся перечень со значениями поля Регион. Установите флаг Избрать несколько частей .

По дефлоту все флажки отмечены. Снимите флаг ( Все ). Все коробки будут сняты.

Потом установите флажки — Юг и Запад и нажмите ОК.

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

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

Ручная фильтрация

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

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

Нажмите на стрелку в ячейке Метки столбцов.

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

Снимите флаг (Избрать все) в высшей части перечня значений.

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

Снимите флаг (Избрать все) в высшей части перечня значений.

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

Сводная таблица показывает лишь те значения, которые относятся к избранному значению поля Месяц — февраль. Вы сможете созидать, что стрелка фильтрации изменяется на значок чтоб указать, что фильтр применяется. Расположите курсор на значок.

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

Если вы желаете поменять значение выбора фильтра, сделайте последующие деяния:

Нажмите на значок.

Установите / снимите флажки значений.

Нажмите на значок.

Установите / снимите флажки значений.

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

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

Нажмите на стрелку в ячейке Ярлычки строк.

Нажмите на стрелку в ячейке Ярлычки строк.

Перечень значений поля — Регион отображается. Это поэтому, что Регион находится на наружном уровне Торговца в порядке размещения. У вас также есть доборная функция — Избрать поле. Нажмите на поле «Избрать поле».

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

Снимите флаг (Избрать все) и проверьте Уолтерс, Крис.

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

Снимите флаг (Избрать все) и проверьте Уолтерс, Крис.

Сводная таблица показывает лишь те значения, которые соединены с избранным значением поля Месяц — февраль и значение поля Торговец — Уолтерс, Крис.

Стрелка фильтрации для Метки строк также изменяется на значок чтоб указать, что фильтр применяется. Расположите курсор на значок на метках строк либо метках столбцов.

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

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

Фильтрация по тексту

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

Данные содержат данные о сотрудниках — EmployeeID, Title, BirthDate, MaritalStatus, Gender и HireDate. Не считая того, данные также имеют уровень менеджера сотрудника (уровни 0 — 4).

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