Способ 3. Специальная вставка
Предположим, у вас есть набор данных, аналогичный тому, что вы видите в верхней части рисунка ниже. Названия стран организованы в столбцы. Но их список слишком длинный, поэтому нам лучше перевернуть столбцы в строки, чтобы всё поместилось на экране:
Для этого выполните следующие действия:
- Выберите исходные данные. Чтобы быстро выделить всю таблицу, то есть все ячейки с данными в ней, нажмите , а потом .
- Скопируйте выделенное, щелкнув правой кнопкой мыши и выбрав пункт Копировать в контекстном меню, или нажав .
- Выберите первую позицию целевого диапазона.Обязательно выберите клетку, расположенную вне вашей исходной таблицы, чтобы области копирования и области вставки не перекрывались. Например, если у вас сейчас 4 столбца и 10 строк, преобразованная таблица будет иметь 10 столбцов и 4 строки.
- Щелкните правой кнопкой мыши целевую ячейку и выберите «Специальная вставка» в контекстном меню, затем выберите «Транспонировать».
Как вы только что видели, функция «Специальная вставка» позволяет выполнять преобразование из строки в столбец (или из столбца в строку) буквально за несколько секунд. Этот метод также копирует форматирование ваших исходных данных, чем добавляет еще один плюс в свою пользу.
Однако у этого подхода есть два недостатка, которые не позволяют называть его идеальным решением чтобы перевернуть данные в Excel:
- Он не слишком подходит для вращения полнофункциональных таблиц Excel. Если вы скопируете всю таблицу и затем откроете диалоговое окно «Специальная вставка», то обнаружите, что пункт «Транспонировать» неактивен. В этом случае вам нужно либо скопировать ее без заголовков столбцов, либо сначала преобразовать ее в обычный диапазон.
- Операция Специальная вставка > Транспонировать не связывает новую таблицу с исходными данными. Поэтому она хорошо подходит только для одноразовых преобразований. Всякий раз, когда исходные данные меняются, вам нужно будет повторить процесс и повернуть таблицу заново. Никто не захочет тратить свое время на преобразование одних и тех же строк и столбцов снова и снова, верно?
Как транспонировать таблицу и связать ее с исходными данными
Давайте посмотрим, как вы можете превращать строки в столбцы, используя уже знакомую нам технику специальной вставки, но при этом связывая полученную таблицу с исходным набором данных. То есть, всякий раз, когда вы меняете данные в исходной, перевернутая будет отражать изменения и обновляться соответствующим образом. Чуть выше мы назвали отсутствие такой связи недостатком. А сейчас посмотрим, как этот недостаток можно обойти.
- Скопируйте строки, которые нужно преобразовать в столбцы (или столбцы, которые нужно превратить в строки).
- Выберите пустую ячейку на том же или другом листе.
- Откройте диалоговое окно «Специальная вставка», как описано в предыдущем примере, и нажмите «Вставить связь» в нижнем левом углу.
- Результат будет примерно таким:
- Выберите новую таблицу и откройте диалоговое окно Excel «Найти и заменить» (или нажмите , чтобы сразу перейти на вкладку Заменить).
- Замените все знаки «=» на «###» или любые другие символы, которых нет нигде в ваших реальных данных. Таким нехитрым образом мы преобразуем наши формулы-ссылки в текст.
Эта операция превратит ваши данные во что-то немного пугающее, как вы видите на скриншоте ниже. Но не паникуйте – еще 2 шага, и вы достигнете желаемого результата.
- Скопируйте диапазон со значениями «###» в буфер обмена, а затем используйте Специальная вставка > Транспонировать, чтобы преобразовать столбцы в строки.
- Наконец, откройте диалоговое окно «Найти и заменить» еще раз, чтобы отменить изменение, т.е. обратно заменить все «###» на «=», и восстановить ссылки на исходные данные.
Это длинноватое, но элегантное решение, не правда ли? Единственным недостатком этого подхода является то, что исходное форматирование теряется в процессе, и вам нужно будет восстановить его вручную (в дальнейшем в этом руководстве я покажу вам быстрый способ сделать это).
Математические функции
ABS
Чтобы определить модуль числа, используйте функцию ABS.
СЛЧИС и СЛУЧМЕЖДУ
И функция СЛЧИС (RAND), и функция СЛУЧМЕЖДУ (RANDBEWTEEN) могут генерировать случайные числа на лету. СЛЧИС (RAND) создает длинные десятичные числа от нуля до 1. СЛУЧМЕЖДУ (RANDBETWEEN) генерирует случайные целые числа между двумя заданными числами.
ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ и ЦЕЛОЕ
Чтобы округлить значения в большую или меньшую сторону, используйте функцию ОКРУГЛ (ROUND). Для принудительного округления до заданного количества цифр в большую сторону используйте ОКРУГЛВВЕРХ (ROUNDUP). Для принудительного округления в меньшую сторону используйте ОКРУГЛВНИЗ (ROUNDDOWN). Чтобы полностью удалить десятичную часть числа, используйте функцию ЦЕЛОЕ (INT).
ОКРУГЛТ, ОКРВВЕРХ и ОКРВНИЗ
Чтобы округлить значения до ближайшего кратного, используйте функцию ОКРУГЛТ (MROUND). Функция ОКРВНИЗ (FLOOR) и функция ОКРВВЕРХ (CEILING) также округляются до заданного кратного. ОКРВНИЗ (FLOOR) заставляет округлять вниз, а ОКРВВЕРХ (CEILING) заставляет округлять вверх.
ОСТАТ
Функция ОСТАТ (MOD) возвращает остаток после деления. Это звучит скучно, но ОСТАТ (MOD) появляется во всех видах формул, особенно в формулах, которые должны делать что-то «каждый раз». В приведенном ниже примере вы увидите, как ОСТАТ (MOD) возвращает ноль каждое третье число, когда делитель равен 3:
СУММПРОИЗВ
Функция СУММПРОИЗВ (SUMPRODUCT) — это мощный и универсальный инструмент для работы со всеми видами данных. Вы можете использовать СУММПРОИЗВ (SUMPRODUCT) для простого подсчета и суммирования на основе критериев, и вы можете использовать его изящными способами, которые просто не работают с СЧЁТЕСЛИМН (COUNTIFS) и СУММЕСЛИМН (SUMIFS). В приведенном ниже примере мы используем СУММПРОИЗВ (SUMPRODUCT) для подсчета и суммирования заказов в марте.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является «агрегатной функцией», которая может выполнять ряд операций с набором данных. В общем, ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) может выполнять 11 операций, включая СУММ (SUM), СРЗНАЧ (AVERAGE), СЧЁТ (COUNT), МАКС (MAX), МИН (MIN) и т.д.
Ключевой особенностью ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является то, что он будет игнорировать строки, которые были «отфильтрованы» из таблицы Excel и строки, которые были скрыты вручную. В приведенном ниже примере ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) используется для подсчета и суммирования только 7 видимых строк в таблице:
АГРЕГАТ
Как и ПРОМЕЖУТОЧНЫе.ИТОГИ (SUBTOTAL), функция АГРЕГАТ (AGGREGATE) может также выполнять ряд агрегатных операций над набором данных и, при необходимости, игнорировать скрытые строки. Ключевые различия заключаются в том, что АГРЕГАТ (AGGREGATE) может выполнять больше операций (всего 19), а также может игнорировать ошибки.
В приведенном ниже примере АГРЕГАТ (AGGREGATE) используется для выполнения операций МИН (MIN), МАКС (MAX), НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL), игнорируя ошибки. Обычно ошибка в ячейке B9 не позволяет этим функциям возвращать результат.
Использование XLOOKUP для поиска диапазона
Хотя это не так часто, как точное совпадение, очень эффективное использование формулы поиска — поиск значения в диапазонах. Возьмите следующий пример. Мы хотим вернуть скидку в зависимости от потраченной суммы.
На этот раз мы не ищем конкретную ценность. Нам нужно знать, где значения в столбце B попадают в диапазоны в столбце E. Это будет определять заработанную скидку.
XLOOKUP имеет необязательный пятый аргумент (помните, по умолчанию он точно соответствует), называемый режимом соответствия.
Вы можете видеть, что XLOOKUP имеет больше возможностей с приблизительными совпадениями, чем VLOOKUP.
Существует возможность найти ближайшее совпадение меньше (-1) или самое близкое больше (1) искомого значения. Существует также возможность использовать символы подстановки (2), такие как? или *. Эта настройка не включена по умолчанию, как это было с VLOOKUP.
Формула в этом примере возвращает ближайшее значение, которое меньше значения, если точное совпадение не найдено:
Однако в ячейке C7 есть ошибка, в которой возвращается ошибка # N / A (аргумент ‘if not found’ не использовался). Это должно было вернуть 0% скидку, потому что расходы 64 не достигают критерия для любой скидки.
Другое преимущество функции XLOOKUP заключается в том, что ей не требуется, чтобы диапазон поиска находился в порядке возрастания, как это делает VLOOKUP.
Введите новую строку в нижней части таблицы поиска, а затем откройте формулу. Расширьте используемый диапазон, щелкая и перетаскивая углы.
Формула сразу исправляет ошибку. Это не проблема с наличием «0» в нижней части диапазона.
Лично я все равно отсортировал бы таблицу по столбцу поиска. Наличие «0» внизу сведет меня с ума. Но тот факт, что формула не сломалась, является блестящим.
Читаем японские свечи и бары
Выше я уже писал, чтобы вы отобразили график в виде баров или японских свечей. Это нужно для того, чтобы видеть за каждый временной интервал четыре цены финансового инструмента.
Четыре цены, которые есть на рынке, это:
- Цена открытия – та первая цена, которая была на начало временного интервала. Если у вас дневной график – то на начало дня, если минутный – то в первую секунду минуты. По-английски цены открытия обозначаются как «Open» (открывать).
- Цена закрытия. Это последняя цена, которая была в рамках временного интервала. Если у вас дневной график, то последней ценой будет та, которая была, скажем, в 23-59-59. Ее английское название – «Close» (закрывать).
- Максимальная цена. Это то наибольшее значение цены, которое было за определенный период времени. Английское обозначение – «High» (высокий).
- Минимальная цена. То наименьшее значение, которое было в течение временного интервала. По-английски – «Low» (низкий).
Например. Когда утром началась торговля на Форексе, американский доллар стоил 74 рубля. Это цена открытия. Когда день закончился, доллар стоил 75 рублей – это цена закрытия. В течение дня доллар дорожал до 76 рублей (это максимальная цена) и опускался в цене до 73 рублей (минимальная).
Всю эту информацию вам как раз дают японские свечи и бары.
Например, вот минутный график индекса Доу Джонс. Каждая свеча на нем показывает ценовые колебания в течение минуты. Вот эта – с 21.33 до 21.34.
А вот эта – с 21.34 до 21.35.
«Тени» свечек показывают максимальные и минимальные значения в течение временного интервала. То есть в течение одной минуты цена поднималась и опускалась до вот этих уровней:
А границы «тела» – это цены открытия и закрытия. То есть в первую секунду минуты цена была на синем уровне, а в последнюю секунду – на красном.
С барами все абсолютно то же самое. Палочка «влево» показывает цену открытия, палочка «вправо» – цену закрытия. А сам «столбик» – максимальные и минимальные цены.
Вот так выглядит наша свечка в виде бара:
Анализ графика происходит именно через бары или японские свечи. Потому что они сразу показывают четыре цены в течение временного интервала. А «линия» показывает всегда только одну цену.
По умолчанию линия строится по ценам закрытия. Данный параметр можно изменить и построить линию по ценам открытия или максимальным/минимальным ценам. Но это бессмысленно, потому что она все равно будет неинформативной.
Теперь важный момент. Когда вы читаете график в виде японских свечей, посмотрите на «необычные» свечи. То есть такие, у которых нет тела или нет одной тени.
Если свечка без тела или тело у нее очень маленькое – это может быть сигналом разворота тренда. Например, как тут:
В формате баров это будет выглядеть так:
Видно намного хуже, с японскими свечами в тысячу раз проще.
Если вы видите свечу без тени – она указывает на очень сильную тенденцию. То есть у рынка столько сил, что он вообще не колеблется.
Пример:
В виде бара это будет выглядеть как-то так:
Как вы суммируете общее изменение ячейки в Excel?
Диапазон переменных функции SUM
- Выберите ячейку E1.
- Введите цифру 3.
- Нажмите Ввод.
- Выберите ячейку E2.
- Введите цифру 6.
- Нажмите Ввод.
- Ответ в ячейке F1 изменится на 90. Это сумма чисел, содержащихся в ячейках от D3 до D6.
Как вы используете сумму смещения?
Как суммировать только определенные столбцы? Просто организуйте свои данные в таблице (Ctrl + T) или отфильтруйте данные так, как вы хотите, нажав кнопку «Фильтр». После этого выберите ячейку непосредственно под столбцом, который вы хотите суммировать, и нажмите кнопку «Автосумма» на ленте. В SUBTOTAL формула будет вставлена, суммируя только видимые ячейки в столбце.
Как суммировать конкретные столбцы? Использование функции СУММ для ручного вычисления суммы
- Выберите ячейку, в которой вы хотите получить сумму ячеек/диапазона.
- Введите следующее: =СУММ(
- Выберите ячейки, которые вы хотите суммировать. …
- Нажмите клавишу Enter.
Синтаксис функции
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции
;ссылка1
;ссылка2;…))
Номер_функции
— это число от 1 до 11, которое указывает какую функцию использовать при вычислении итогов внутри списка.
|
Номер_функции (включая скрытые значения) |
Номер_функции (за исключением скрытых значений) |
Функция |
|---|---|---|
| 1 | 101 | СРЗНАЧ |
| 2 | 102 | СЧЁТ |
| 3 | 103 | СЧЁТЗ |
| 4 | 104 | МАКС |
| 5 | 105 | МИН |
| 6 | 106 | ПРОИЗВЕД |
| 7 | 107 | СТАНДОТКЛОН |
| 8 | 108 | СТАНДОТКЛОНП |
| 9 | 109 | СУММ |
| 10 | 110 | ДИСП |
| 11 | 111 | ДИСПР |
Например, функция СУММ()
имеет код 9. Функция СУММ()
также имеет код 109, т.е. можно записать формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10)
или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10).
В чем различие — читайте ниже. Обычно используют коды функций от 1 до 11.
Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;… (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Важно
: Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных или горизонтальных наборов данных (ее использование в этом случае может приводить к непредсказуемым результатам)
Использование промежуточного итога в Excel — примеры формул
Когда вы впервые сталкиваетесь с ПРОМЕЖУТОЧНЫМИ ИТОГАМИ, это может показаться сложным, запутанным и даже бессмысленным. Но как только вы приступите к делу, вы поймете, что освоить его не так уж и сложно. Следующие примеры покажут вам несколько полезных советов и вдохновляющих идей.
Пример 1. Промежуточный итог 9 против промежуточного итога 109
Как вы уже знаете, Excel SUBTOTAL принимает 2 набора номеров функций: 1-11 и 101-111. Оба набора игнорируют отфильтрованные строки, но номера 1-11 включают скрытые вручную строки, тогда как 101-111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.
Для суммирования отфильтрованных строк вы можете использовать формулу «Промежуточный итог 9» или «Промежуточный итог 109», как показано на снимке экрана ниже:
Но если вы скрыли ненужные элементы вручную с помощью Скрыть строки команда на Дом вкладка > Клетки группа > Формат > Скрыть и показатьили щелкнув строки правой кнопкой мыши, а затем щелкнув Скрыватьи теперь вы хотите суммировать значения только в видимых строках, промежуточный итог 109 — единственный вариант:
Аналогично работают и другие номера функций. Например, для подсчета непустых отфильтрованных ячеек подойдет формула «Итог 3» или «Итог 103». Но только промежуточный итог 103 может правильно подсчитывать видимые непустые строки, если в диапазоне есть какие-либо скрытые строки:
Примечание. Функция ПРОМЕЖУТОЧНЫЕ ИТОГОВ Excel с номером_функции 101–111 игнорирует значения в скрытых строках, но не в скрытых столбцах. Например, если вы используете такую формулу, как ПРОМЕЖУТОЧНЫЕ ИТОГ(109, A1:E1) для суммирования чисел в горизонтальном диапазоне, скрытие столбца не повлияет на промежуточный итог.
Пример 2. ЕСЛИ + ПРОМЕЖУТОЧНЫЙ ИТОГ для динамического суммирования данных
Если вы создаете сводный отчет или информационную панель, где вам нужно отобразить сводку различных данных, но у вас нет места для всего, решением может быть следующий подход:
- В одной ячейке создайте раскрывающийся список, содержащий имена функций, такие как Total, Max, Min и т. д.
- В ячейку рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточного итога, соответствующими именам функций в раскрывающемся списке.
Например, предположим, что промежуточные значения находятся в ячейках C2:C16, а раскрывающийся список в A17 содержит Общий, Средний, Максимума также Мин. элементов, формула «динамического» промежуточного итога выглядит следующим образом:
= ЕСЛИ (A17 = «итого», ПРОМЕЖУТОЧНЫЙ ИТОГ (9, C2: C16), ЕСЛИ (A17 = «среднее», ПРОМЕЖУТОЧНЫЙ ИТОГ (1, C2: C16), ЕСЛИ (A17 = «минимум», ПРОМЕЖУТОЧНЫЙ ИТОГ (5, C2: C16) ), ЕСЛИ(A17=»max», ПРОМЕЖУТОЧНЫЙ ИТОГ(4,C2:C16),»»))))
И теперь, в зависимости от того, какую функцию из выпадающего списка выберет ваш пользователь, соответствующая функция Subtotal будет вычислять значения в отфильтрованных строках:
Кончик. Если вдруг выпадающий список и ячейка с формулой исчезнут с вашего листа, обязательно выберите их в списке фильтров.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.
Описание
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис
Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.
Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.
Номер_функции (с включением скрытых значений)
Номер_функции (с исключением скрытых значений)
Ссылка1 Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги.
Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.
Примечания
Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;. » (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Написание формулы промежуточных итогов вручную
Есть еще один способ посчитать промежуточные итоги – с помощью специальной функции.
- Для начала отмечаем ячейку, где должен быть выведен итог подсчета. Далее нажимаем на значок «Вставить функцию» (fx) рядом со строкой формул с левой стороны от нее.
- Откроется Мастер функций. Выбираем категорию “Полный алфавитный перечень”, находим из предложенного перечня функцию “ПРОМЕЖУТОЧНЫЕ.ИТОГИ”, ставим на нее курсор и нажимаем OK.
- Теперь нужно задать настройки функции. В поле «Номер_функции» указываем цифру, которой соответствует нужному варианту обработки информации. Всего опций одиннадцать:
- цифра 1 – расчет среднего арифметического значения
- цифра 2 – подсчет количества ячеек
- цифра 3 – подсчет количества заполненных ячеек
- цифра 4 – определение максимального значения в выбранном массиве данных
- цифра 5 – определение минимального значения в выбранном массиве данных
- цифра 6 – перемножение данных в ячейках
- цифра 7 – выявление стандартного отклонения по выборке
- цифра 8 – выявление стандартного отклонения по генеральной совокупности
- цифра 9 – расчет суммы (ставим в нашем варианте согласно задаче)
- цифра 10 – нахождение дисперсии по выборке
- цифра 11 – нахождение дисперсии по генеральной совокупности
- В поле «Ссылка 1» указываем координаты диапазона, для которого требуется просчитать итоги. Всего можно указать до 255 диапазонов. После введения координат первой ссылки, появится строка для добавления следующей. Прописывать координаты вручную не совсем удобно, к тому же, велика вероятность ошибиться. Поэтому просто ставим курсор в поле для ввода информации и затем левой кнопкой мыши отмечаем нужную область данных. Аналогичным образом можно добавить следующие ссылки, если потребуется. По завершении подтверждаем настройки нажатием кнопки OK.
- В итоге в ячейке с формулой будет выведен результат подсчета промежуточных итогов.
Примечание: Как и другие функции Эксель, использовать “ПРОМЕЖУТОЧНЫЕ.ИТОГИ” можно, не прибегая к помощи Мастера функций. Для этого в нужной ячейке вручную прописываем формулу, которая выглядит следующим образом:
= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер обработки данных;координаты ячеек)
Далее жмем клавишу Enter и получаем желаемый результат в заданной ячейке.
Использование функции «Промежуточные итоги» в Excel
К сожалению, не все таблицы и наборы данных подходят для того, чтобы применять к ним функцию промежуточных итогов. К главным условиям относятся следующие:
- Таблица должна иметь формат обычной области ячеек;
- Шапка таблицы должна состоять из одной строки и размещаться на первой строке листа;
- В таблице не должно быть строк с незаполненными данными.
Создание промежуточных итогов в Excel
Переходим к самому процессу. За использование этого инструмент отвечает отдельный раздел, вынесенный на верхнюю панель программы.
- Выделяем любую ячейку в таблице и переходим на вкладку «Данные». Нажимаем по кнопке «Промежуточный итог», которая расположена на ленте в блоке инструментов «Структура».
Откроется окно, в котором нужно настроить выведение промежуточных итогов. В нашем примере нам надо просмотреть сумму общей выручки по всем товарам за каждый день. Значение даты расположено в одноименной колонке. Поэтому в поле «При каждом изменении в» выбираем столбец «Дата».
В поле «Операция» выбираем значение «Сумма», так как нам требуется подбить именно сумму за день. Кроме суммы доступны многие другие операции, среди которых можно выделить: количество, максимум, минимум, произведение.
Так как значения выручки выводятся в столбец «Сумма выручки, руб.», то в поле «Добавить итоги по», выбираем именно его из списка столбцов таблицы.
Кроме того, надо установить галочку, если ее нет, около параметра «Заменить текущие итоги». Это позволит при пересчете таблицы, если вы проделываете с ней процедуру подсчетов промежуточных итогов не в первый раз, не дублировать многократно запись одних и тех же итогов.
Если поставить галочку в пункте «Конец страницы между группами», при печати каждый блок таблицы с промежуточными итогами будет распечатываться на отдельной странице.
При добавлении галочки напротив значения «Итоги под данными» промежуточные итоги будут устанавливаться под блоком строк, сумма которых в них подбивается. Если же снять галочку, тогда они будут показываться над строками. Для большинства удобнее размещение под строками, но сам выбор сугубо индивидуален.
По завершении жмем на «OK».
В результате промежуточные итоги появились в нашей таблице. Кроме того, все группы строк, объединенные одним промежуточным итогом, можно свернуть, просто кликнув по знаку «-« слева от таблицы напротив конкретной группы.
Следует также отметить, что при изменении данных в строчках таблицы пересчет промежуточных итогов будет производиться автоматически.
Формула «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»
Помимо вышесказанного существует возможность вывода промежуточных итогов не через кнопку на ленте, а путем вызова специальной функции через «Вставить функцию».
- Предварительно кликнув по ячейке, где будут выводиться промежуточные итоги, жмем указанную кнопку, которая расположена слева от строки формул.
Откроется «Мастер функций», где среди списка функций ищем пункт «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Выделяем его и кликаем «OK».
В графе «Ссылка 1» укажите ссылку на тот массив ячеек, для которого вы хотите установить промежуточные значения. Допускается введение до четырех разрозненных массивов. При добавлении координат диапазона ячеек сразу появляется окно для возможности добавления следующего диапазона. Так как вводить диапазон вручную не во всех случаях удобно, можно просто кликнуть по кнопке, расположенной справа от формы ввода.
Окно аргументов функции свернется и можно просто выделить курсором нужный массив данных. После того, как он автоматически занесется в форму, кликните по кнопке, размещенной справа.
Опять появится окно аргументов функции. Если понадобилось добавить еще один или несколько массивов данных, воспользуйтесь тем же алгоритмом, который был описан выше. В обратном случае просто нажмите «OK».
Промежуточные итоги выделенного диапазона данных будут сформированы в ячейке, в которой находится формула.
Синтаксис самой функции выглядит следующим образом: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;адреса_массивов_ячеек) . В нашей ситуации формула будет выглядеть так: «ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C6)». Эту функцию, используя этот синтаксис, можно вводить в ячейки и вручную, без вызова «Мастера функций»
Только важно не забывать перед формулой в ячейке ставить знак «=».
Итак, существует два основных способа формирования промежуточных итогов: через кнопку на ленте и через специальную формулу. Кроме того, пользователь должен определить, какое именно значение будет выводиться в качестве итога: сумма, минимальное, среднее, максимальное значение и т.д.
голоса
Рейтинг статьи