Файл эксель для тренировки сводных таблиц

Excel для финансиста

Поиск на сайте

Глава 7. Сводные таблицы

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

Скачайте файл svodnie-tablici. На листе данные этого файла находятся двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:

• Дата совершения продажи;
• Наименование товара;
• Наименование покупателя товара;
• Сумма сделки.

Относительно этих данных может возникнуть множество вопросов:

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

На все эти вопросы помогают ответить сводные таблицы.

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

Создание сводной таблицы

Перед тем, как сделать сводную таблицу, нужно задать данные, которые будут в ней отражены. В нашем случае – вся таблица. Проще всего выделить таблицу, выбрав любую ячейку в ней и нажав Ctrl-A. Теперь в меню Вставка нажмите кнопку Сводная таблица, в открывшемся окне проверьте выбранный диапазон данных, выберите, что создание сводной таблицы произойдёт на новом листе, ОК.

Поля сводной таблицы

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

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

Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировки – По убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.

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

Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.

Скачать пример сводных таблиц Excel: svodnie-tablici

Источник

Файл эксель для тренировки сводных таблиц

Проблемы с отображением видео:

Генератор примеров (массивы, таблицы, отчеты).

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Генератор таблиц (v. 14.09)	38 Кб	2964

Как быстро построить сводную таблицу из отчета 1C или SAP?

Суть проблемы, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на основе отчета, полученного из учетной системы. В одном столбце расположены разнотипные данные и Клиент, и Категория товара, и Наименование товара. Значения же, например, объем продаж разбит по нескольким столбцам, по месяцам: Январь в своем столбце, Февраль в своем и так далее.

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример	52 Кб	2254

Как быстро преобразовать таблицу в массив для сводной таблицы?

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример	69 Кб	2282

Как отфильтровать столбец сводной таблицы?

На тренингах я часто задаю вопрос участникам: Кто знает, как отфильтровать отдельный (не итоговый) столбец сводной таблицы по значениям? И что я слышу в ответ: Это невозможно! В сводных таблицах нет такой возможности! Вопрос настолько древний, что люди уже перепробовали всякие варианты и не найдя решения сделали вывод — невозможно. Хотя решение существует.

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример	290 Кб	2033

Как построить сводную таблицу по нескольким массивам (листам)?

Типичная задача при обработке информации полученной из разных источников. Типовое решение — взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

Однако решение существует! И оно не очень сложное.

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример	27 Кб	1619

Как построить сводную таблицу?

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

Как проводить вычисления в сводной таблице?

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример	109 Кб	3566

Что такое сводные таблицы?

Приходилось ли вам когда-нибудь попадать в такую ситуацию?:

Вы очень долго «рисовали» сложную таблицу с множеством строк и столбцов и большим содержанием данных, например, таблицу — отчет по продажам в разрезе Подразделений, Городов, Менеджеров, Типов клиентов, с группировкой по кварталам, по количеству и суммам, с вычислением процентов и долей. И когда уже все готово и «раскрашено», вы показываете эту таблицу, например, руководителю, а он говорит: «Все круто, вот только бы добавить сюда еще разрез по Товарным категориям, вообще было бы замечательно».

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

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

Источник

MS Excel Сводные таблицы + Практический файл

Курс MS Excel. Продвинутый уровень. На этом уроке создаем СВОДНЫЕ ТАБЛИЦЫ! Не смотря на то, что все кажется легко, рекомендуется скачать ПРАКТИЧЕСКИЙ ФАЙЛ и проделать те же шаги (а может и больше) с тестовыми данными. Обязательно попрактикуйтесь!

Практический файл можно скачать здесь.

Еще один урок про настройку сводных таблиц:

Дубликаты не найдены

Офисные будни

2.8K постов 12.8K подписчиков

Правила сообщества

— добавляйте посты связанные с тематикой сообщества;
-делитесь опытом организации жизни в офисе и проживания на работе;
-делитесь управленческим опытом;

— не нарушайте правила Pikabu и чтите закон.

М-м-м. Сортировка. Итоги. Вычисляемые поля. ТС, ты не раскрыл и 20% сводных таблиц.

Отдельный батхерт вызывает ютуб-формат. У тебя проблемы с изложением своих мыслей, что ты не можешь всё обычным текстом с картинками изъяснить?
Позитивное «Подписывайтесь, пожалуйста, на канал. «

Большое спасибо за файл для тренировки и начала освоения сводных таблиц!

Спасибо Вам за внимание!

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

Да ты заебал со своим «школьным» экселем. Если уж пилить пост про эксель, то запили про сводные таблицы, а не про этот школотрон

Основы Excel. Форматирование данных

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

04:31 — Форматирование ячеек (границы, заливка, готовые стили).

09:00 — Выравнивание содержимого.

10:27 — Перенос текста, ориентация, автоподбор ширины.

12:59 — Объединение ячеек.

15:25 — Копирование формата.

17:07 — Поиск и замена формата.

18:39 — Очистка форматирования.

Сводные таблицы

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

Запрос больших объемов данных различными понятными способами.

Подведение промежуточных итогов и вычисление числовых данных.

обобщение данных по категориям и подкатегориям

создание пользовательских вычислений и формул

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

Перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных.

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

Представление кратких наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.

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

Вот эти пустые ячейки сводная таблица и не любит, особенно в заголовках столбцов. Первое правило сводной таблицы – (никому не говорить о сводной таблице) все столбцы исходных данных, из которых мы формируем сводную таблицу, должны быть озаглавлены, и озаглавлены понятно и по-разному.
Итак, давайте разбираться сначала и на примере. Есть у нас какой-то большой объем данных, для примера я накидал такую таблицу.

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

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

Что, же уважаемый Excel, вызов принят! выбираем поля «вид продукта», «количество на складе». Получаем

красотень
жмакаем номер склада, немного не то что хотел, перетаскиваем поле № склада из раздела «Итоги» в раздел «Названия столбцов».

Вот! то что нужно было!
В общем у нас есть 4 области:
«Фильтры» — это фильтр для всей сводной, данные которые этот фейс-контроль не проходят не попадают в клуб «сводная таблица»
«Названия строк» — здесь то, что у нас будет в строках
«Названия столбцов» — то, что будет в столбцах
«Значения» — те значения что будут в самой таблице.
Поля по этим столбцам можно перетаскивать на ваше усмотрение, поэкспериментируйте. Если поле не нужно его можно выкинуть, перетащив мышкой за пределы таблицы, либо отжав галочку. Не стоит перегружать столбцами Значения и названия столбцов, так вы только запутаете того, кто будет смотреть эту таблицу. Если вам не нравится порядок результатов в столбцах или в строках их можно перетащить в самой таблице.

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

Давайте нажмем на правой кнопкой мыши на сводной таблице, выберем «параметры сводной таблицы», вкладка «вывод» , галочка «Классический макет сводной таблицы»

Теперь у нас «Продукт» в отдельном столбце, появился Итог по приправам отдельной строкой, вся таблица немного изменилась и больше похожа на классическую таблицу. Может кому то такой вид больше пригодится, но позже я расскажу Вам как его можно использовать.
Вот ссылка на гугл диск https://drive.google.com/file/d/0B8QwhfN2DgusNDNtRjloN1E5MWV.
На этом давайте пока остановимся, продолжение следует.

Волшебная формула

Всем привет. Продолжаем постигать Excel. Если Вы не знакомы с моими предыдущими уроками, то советую ознакомиться. Сегодня я хочу продолжить тему, поднятую в предыдущем посте Как я делаю шаблоны о составлении шаблонных форм. Однажды мне потребовалось сделать такой лист, содержимое которого зависит от названия листа. Я нашел одну чудесную формулу и хочу ею с Вами поделиться. Также дополнительно я хочу показать, как в простом листе запутать формулы так, чтобы потом в них было практически невозможно разобраться. Спросите, зачем это нужно? Иногда бывает полезно – скидываешь человеку таблицу и если в ней нужно что-то переделать, то он опять обращается к Вам, только не забудьте сохранить у себя исходник. Но обо всем по порядку.

Сначала немного теории.
Рассмотрим работу функции ЯЧЕЙКА (тип_сведений;[ссылка]), она имеет 2 аргумента: тип сведений и ссылку на ячейку соответственно. Сейчас нас интересует тип «имяфайла», выбираем его и ссылаемся на саму ячейку, где мы это пишем (остальные типы вам на самостоятельное изучение). Для корректной работы этой функции необходимо чтобы файл был сохранен где-нибудь. Итак, в имени файла мы видим непосредственно имя файла и после него имя листа, на котором прописываем эту функцию (точнее на лист, куда ссылаемся ссылкой). Давайте попробуем вырезать то, что нам нужно, а именно имя листа. Для этого нам нужно знать длину текста в ячейке с именем файла, получаем ее с помощью функции ДЛСТР(),затем нам нужно найти позицию закрывающейся квадратной скобки, которая ограничивает имя файла при помощи функции ПОИСК, разница этих чисел и будет длиной имени листа. Отрезаем справа от первоначальной ячейки эту длину и получаем ячейку с именем файла. В результате у нас должно получиться нечто подобное.

Формулы при этом выглядят так:

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

назавем этот лист «База».
Здесь рассмотрим еще одну полезную функцию Excel, которая называется именованные области (или как-то так). Выделяем область к которой нам нужно будет часто обращаться, в нашей таблице это столбцы от А до G на листе «База», и жмакаем на строку которая расположена слева от значка формулы

теперь пишем там название нашей области «база».
Все, теперь в формулах можно вместо ссылки на область эту область писать просто база! Пишем в лист с волшебной формулой следующие формулы:

помним что в ячейке D5 у нас название листа, которое будет искаться в первом столбце базы, то есть для создания карточки сотрудника нам нужно будет назвать лист как фамилию нужного нам сотрудника. Я решил не просто тупо подставлять значения из таблицы (это же скучно), а склеить ФИО, вместо дня рождения выводить сколько полных лет, а вместо даты приема на работу стаж в годах. Но это просто в образовательных целях. Можно и эти данные в формуле прописать в базе.
Теперь проверим, назовём лист «Иванов»:

Во как!
на основании такого листочка и нехитрого макроса я на пример делал много отчетов с разбивкой по пятидневкам или по дням из месячной базы. Но создание макросов я намеренно не рассматриваю в своих статьях (сам только учусь их писать)
Теперь перейдем ко второй части нашего балета. То есть запутаем эту формулу так, чтобы сами не могли ничего разобрать. Смотрите, у нас в ячейке D1 прописана формула, ссылающаяся в принципе на любую ячейку этого листа, для удобства поменяем чтобы она ссылалась на ячейку A1. Теперь посмотрим, кто как у нас на этом листе ссылается: ставим курсор на A1, идем в пункт формулы, нажимаем «зависимые ячейки» несколько раз, получаем:

видим что все формулы зависят от А1. Теперь идем в D5, копируем все что там после знака «=», нажимаем поиск с заменой (Ctrl+H), пишем Найти D5, заменить на то что скопировали ПРАВСИМВ(D1;D4). Заменяем везде, затем идем в D4, копируем все что после знака «=»…. и так повторяем пока не придем в ячейку А1. После этого ячейки D1-D5 можно удалить, они у нас были как бы промежуточные. В итоге лист у нас работает также как и раньше, а что же в формулах? Заглянем в B4: «@=ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;1;0)&» «&(ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;2;0)&» «&ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;3;0))»
жуть какая! ничего не разберешь. но мы то с вами знаем что формула ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1)) просто показывает нам имя листа. И это у нас относительно простая зависимость была изначально. Посредством таких вставок с заменой мы убираем промежуточные вычисления, что очень сильно ухудшает читаемость формулы.
вот пример на гугл. докс., но только он не работает потому что файл не сохранен на диск, сохраните и ковыряйте
https://drive.google.com/file/d/0B8QwhfN2DgusTzIxQ1ZHTlZtclc.
На этом пока все, в следующий раз наверное все таки будут сводные таблицы (по многочисленным просьбам)

Как я делаю шаблоны

Всем добра. Продолжаем совершенствоваться в овладении великим и могучим Excel.
Сегодня я решил показать один из способов, как в excel`е можно сделать заполняемые формы. Тут есть что то из старого материала (смотри предыдущие уроки), ну и кое-что новенькое покажу. Итак, допустим у нас есть какая либо форма, которую периодически нужно заполнять (на пример какие ни будь заявления, приказы, объяснения и т.д.). Данные в форме немного варьируются, некоторые слова немного меняются, но основной текст остается прежней, некоторые слова (имена, фамилии) нужно выбирать из списка, какие то значения вбивать и т.д. В таком случае можно просто брать шаблон и вбивать туда данные, но при частом использовании взгляд «замыливается» и возможны допущения ошибок. Так что сделаем такой шаблон, который можно было бы «настраивать». Мой пример будет иметь малое практическое применение, я просто в нем хочу показать варианты использования элементов управления формами. Для начала давайте их найдем. Находятся они на вкладке «Разработчик». Если ее не видно идем в параметры и включаем ее во вкладке «Настройка ленты». Жмакаем вставить и смотрим что нам тут есть полезного (функционал я расскажу сразу в примере):

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

Теперь накидаем пункты, которые будут переключаться.

Теперь будем «связывать». Я обычно делаю все на 1 «техническом» листе, потом «технические» ячейки скрываю. но можно их подтянуть на отдельный лист и скрыть весь лист.
небольшое удобство: если выбрать элемент управления правой кнопкой он выделяется, и его можно на пример передвигать, а в строке ввода формул появляется связанная ячейка. ее это та же ячейка что и в Формат объекта — > элемент управления ->Связь с ячейкой.
Флажок уважаемости связываем с ячейкой G4, переключатели адресата связываем с ячейкой G4, список месяца с G7, список числа с G6, полосу прокрутки времени опоздания с G8, список причины с G9.
Получаем такую табличку:

в столбце H стоят вот такие формулы (есть волшебная комбинация клавиш Ctrl+ё, которая на листе показывает вместо значений формулы в ячейках):

которые подставляют данные посредством функций ВПР и ИНДЕКС из табличек:

Источник