Промежуточные итоги
Промежуточные итоги в больших таблицах.





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Промежуточные итоги в больших таблицах.

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

Промежуточные итоги в больших таблицах

Для занятия Вам потребуются книги Excel с заготовками таблиц, по которым составлен конспект - эти файлы прикреплены в самом низу страницы (без проблем откроются в Excel версии 2007 и новее)

 

Автоматическая вставка промежуточных итогов и их оформление

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

Первую ситуацию рассмотрим на примере файла - скачайте его внизу страницы. Это реестр договоров и документ уже отсортирован по Филиалам, Видам договоров и прочим параметрам. И было бы совсем неплохо добавить в таблицу строки Итого по Филиалу 1, Итого по Филиалу 2 и т.д. – такая задача решается в Excel в считанные секунды – для этого выберите любую заполненную ячейку таблицы (выделять всю необходимости нет – Excel сам определит границы таблицы до первых пустых строк и столбцов, которыми она окружена) и выполните команду Данные – Структура – Промежуточный итог [Data Outline Subtotal] и далее в открывшемся диалоговом окне выполняем следующие настройки:

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

Теперь выделяем все строки, подлежащие форматированию (в моем случае – с 13 по 26), и чтобы исключить из области выделения скрытые строки (если приступить к настройкам внешнего вида прямо сейчас, то скрытые строки тоже будут отформатированы, а это совсем не тот результат, к которому мы стремимся) – выполняем команду Главная – Редактирование – Найти и выделить – Выделить группу ячеек [HomeEditing Find_&_Select – Go_to_special] либо нажмите F5 и нажмите в открывшемся окне в нижней левой части кнопку Выделить [Specail…], осталось выбрать во втором столбце третий снизу переключатель Только видимые ячейки [Visible_cells_only] и нажать ОК – в выделенном диапазоне Вы увидите «разрывы» в тех местах, где есть скрытые строки. Такого же результата можно было бы добиться при выделении нужных строк с помощью клавиши Ctrl, но для большой таблицы такой вариант конечно же эффективнее :)

 

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

Остался финальный штрих – убрать заливку и границы (а, впрочем, не помешало бы и прочие настройки сбросить, которые были сделаны: полужирное начертание и изменение размера), ведь если их оставить, то можно и на печать нечаянно толпу листов отправить, да и в целом эстетика требует. Как же быстро убрать заливку и границы ячеек до конца листа? А вот так: выделяем столбец, начиная с которого нужно отменить форматирование (у меня это столбец I – я выделила его на предыдущем рисунке), далее нажимаем и удерживаем клавиши Ctrl + Shift и один раз жмем клавишу со стрелкой вправо (будет выделен весь набор ячеек от выбранного столбца и до правой границы листа). А чтобы быстро избавиться от любых настроек внешнего вида ячеек, отличных от формата по умолчанию, выполните всего одну команду: Главная – Редактирование – Очистить – Очистить форматы [HomeEditingClearClear_Formats]

 

Создание группировок для удобного отображения и скрытия столбцов и строк

Далеко не всегда удается применить вышеописанный инструмент – он идеально подходит для обработки реестров или выгрузок из баз данных, но как поступить с таблицей, создаваемой вручную? Например, с такой, которую можно увидеть в файле Ex-РВ-Урок-20-Пром.итоги_Вручную (файл прикреплен внизу страницы). В ней об автоматизации можно забыть, но облегчить ручной труд все равно можно и сейчас разберемся, каким именно образом

Для удобства  работы предлагаю создать структуру таблицы; ручной порядок решения этой задачи следующий: выделяем те строки, которые нужно будет скрывать, и выполняем команду Данные – Структура – Группировать [DataOutlineGroup]. Таблица примет следующий вид (на рисунке я не снимала выделение строк, которые выбрала до момента выполнения команды):

Теперь проделайте то же самое с оставшимися разделами таблицы. Кстати, если выделить следущие строки с данными по материалам, то можно уже не тянуться за кнопкой на ленте, а просто нажать F4 – она повторит последнее совершенное действие. Для создания группировки следующего уровня (всего их, кстати, может быть 8) перед выполнением команды выделите набор строк, как указано на рисунке ниже:

 

Как вручную без ошибок вычислить промежуточные и общие итоги

Чтобы завершить оформление таблицы, нужно посчитать общий итог и для этого хочу предложить целых три варианта. Вариант первый работает очень эффектно, если все подитоги в таблице высчитаны при помощи функции СУММ [SUM] – в примере такую картинку можно наблюдать по столбцу C. В таком случае выделите ячейку C14 (т.е. ту, в которой нужно посчитать общий итог) и нажмите на значок автосуммы либо сочетание клавиш Alt + = Произойдет вещь крайне приятная – автосумма сама выберет из вышележащих ячеек те, в которых результат получен при помощи нее же:

 

В соседней ячейке D14 такая махинация уже не проходит, а приводит к вот какому результату (и все потому, что промежуточные итоги вычислены не при помощи автосуммы, а как простое сложение ячеек):

На самом деле не смущаемся – делим полученное вычисление =СУММ [SUM] (D4:D13) на 2 (получится = СУММ [SUM] (D4:D13)/2) и жмем Enter, ведь каждая цифра участвует в вычислениях ровно два раза – один раз данные учтены по строке с названием материала и они же учтены второй раз в итогах по складам. Это выход в некоторых ситуациях, но далеко не во всех – для таблиц более сложной организации (несколько вложенных уровней, например) такой метод уже не подойдет. К тому же он не страхует от ошибки, которая непременно высплывет в общем итоге, если хотя бы один промежуточный итог был вычислен неверно (недотянули формулу и т.п.). Поэтому любимым инструментом во всех подобных таблицах остается функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ [SUBTOTAL]

Как она работает – будем разбирать на примере крайнего правого столбца таблицы Количество-2. Начать предлагаю с общего итога: в ячейке E14 пишем =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(

После того, как будет открыта скобка, под курсором мыши появится (начиная с 2010-ой версии Excel) список возможных действий – для суммирования выбираем 9 либо вписываем ее от руки и ставим точку с запятой (одним знаком – ;), а далее выбираем диапазон ячеек, которые нужно сложить, как для автосуммы – не пытайтесь выбрать «нужные» ячейки, выбирайте все подряд от края до края, как на рисунке:

Жмите Enter и результат 5.806 будет отображен в ячейке. Теперь самое интересное – если все промежуточные итоги высчитать при помощи этой же функции, то общий результат останется неизменен, так как в противоположность автосуммы функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ [SUBTOTAL] не берет в расчет ячейки, в которых результат вычисляет такая же функция:

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

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

Похожие материалы

Создание и настройка выпадающих списков.
Выпадающие списки.
Как ограничить ввод данных в ячейке списком допустимых значений (справочником)? Где расположить этот справочник. Команда Проверка данных содержит такую настройку.

Текстовые функции для отбора и исправления знаков в ячейке
Текстовые функции
Как взять из ячейки первые или последние несколько знаков? А из середины текста? Как правильно использовать функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР и другие?

Создание сводной таблицы и настройки отчёта
Сводные таблицы. Часть 1
Команда Сводная таблица позволяет получить компактный упорядоченный отчёт по выгрузке из базы данных. Создать и настроить отчёт сводной таблицы очень просто. Главное - начать.

Связи между книгами. Управление внешними ссылками.
Внешние ссылки
Составление формул без переноса данных из одной книги Excel в другую возможно! Для этого нужно научиться создавать внешние ссылки (связи) и управлять ими.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

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



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.