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

Добрый вечерочек, друзья)

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

Залетайте на материал↓

🔗 Изучить материал | Связь
⠀

​​7.2 Варианты расчета значений в сводной

Салют!
Сегодня короткий пост о вариантах расчета значений в сводной таблице.
Из прошлого материала могло показаться, что сводная таблица только суммирует значения по ФИО, датам.
Но нет. Есть возможность посчитать среднее, кол-во значений, мин, макс и многое другое.

— Шаг 1 —
Нажимаете ПКМ по значениям в таблице и выбрать пункт Параметры полей значений.

— Шаг 2 —
Открывается окно параметров.
На первой же вкладке Операция выбираете что вы хотите сделать со значениями. Наиболее популярные варианты: среднее, кол-во значений, минимум и максимум.

В верхней части ставите имя поля.

Дополнительно
Кому мало одного расчетного поля, обрадую вас) Есть возможность сделать несколько расчетных полей в сводной. Например, поставить сумму баллов и рядом расчет среднего балла.

Для этого требуется сделать нехитрую манипуляцию: взять и повторно перетащить расчетное поле в блок "Значения".
Создается дубль, для которого вы можете уже поставить вариант расчета Среднее.

Еще больше подробностей в коротком видео↓

​​7.3 Группировка полей в сводной

Бонджорно!
Мы уже поняли, что сводная группирует значения и выводит более компактную версию таблицы.
Помимо этого существует функция группировки строк и столбцов. Расскажу о 2-х видах группировки: автоматической и ручной.

Автоматическая группировка

Применяется в-основном для полей времени и дат.
Excel уже априори знает, что дата 01.04.2020 содержит в себе день, месяц, квартал, год.
А время 11:40:50 содержит в себе секунды, минуты и часы.
Система может автоматически разбить подобные значения.

Чтобы разбить даты 01.04.2020, 02.10.2020… по кварталам, требуется:

— Встать на любое значение строки/столбца.
— На ленте, на вкладке Анализ (Работа со сводными таблицами), выбрать раздел Группа и нажать на Группировка по полю.
— В открывшемся окне, выбрать диапазон значений для группировки. В поле С шагом: выбрать единицу группировки. Это может быть день, месяц, квартал, год и т.д.
Есть возможность создания вложенной группировки. Например по годам, а внутри по кварталам. Для этого выбирайте сразу 2 значения: Годы и Кварталы.
— Нажать Ок.

Ручная группировка

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

— Выделить все группируемые значения в строках/столбцах.
— На ленте, на вкладке Анализ (Работа со сводными таблицами), выбрать раздел Группа и нажать на Группировка по выделенному.
— Группу можете переименовать вручную, в строках сводной таблицы.

Удаление группировки

Финальный аккорд.
Когда группировка не нужна, мы это все разгруппировываем.

— Встать на любое группируемое значение
— На ленте, на вкладке Анализ (Работа со сводными таблицами), выбрать раздел Группа и нажать на Разгруппировать.

Видео↓

​​Кейс №15. Условное форматирование по времени и дате

Всем привет!
Пост по рубрике #кейсы. Недавно получил от подписчика вопрос: можно ли сделать условное
форматирование ячеек (заливка цветом), по истечению определенной даты?

Да, это легко можно сделать.

— Заходим в Условное форматирование на главной вкладке ленты.
— Кнопка Создать правило.
— В открывшемся окне выбираем пункт Использовать формулу для определения форматируемых ячеек.

В строке внизу требуется ввести формулу. Какие варианты?

Если требуется заливать цветом ячейки, например до того момента как наступит 30 апреля 2020, то следует написать формулу:

= СЕГОДНЯ() < ДАТА(2020;04;30)

Система будет сравнивать сегодняшнюю дату с 30.04.2020 и если она меньше, то ячейки буду отформатированы.

— Ну и конечно в конце нужно выбрать форматирование – кнопка Формат. В нашем случае – залить зеленым цветом.

Использовать подобные приемы можно на различных графиках исполнения работ, загрузки и пр.
Видео↓

​​7.4 Итоги в сводной таблице

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

1. Промежуточные итоги

Этот инструмент суммирует строки или столбцы по созданным группам в сводной таблице - вспоминаем прошлый материал 7.3 Группировка полей в сводной.

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

Чтобы включить суммирование по группам.
— Встаем на любое поле сводной таблицы. На ленте появляются доп. вкладки. Открываем Конструктор.
— Нажимаем ярлык Промежуточные итоги. Варианты на выбор:

▫️Не показывать промежуточные суммы - отключение всех итогов по группам.
▫️Показывать все промежуточные итоги в нижней части группы - вставляет последнюю строку с итогами для каждой группы.
▫️Показывать все промежуточные итоги в заголовке группы - вставляет итоги в первую строку группы заголовок).

Важный момент

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

Для этого нужно открыть Параметры поля, которое группирует строки или столбцы.
На вкладке Промежуточные итоги и фильтры есть чек-бокс.
▫️Автоматически - расчет суммы в итогах
▫️Нет - отключить промежуточные итоги для поля.
▫️Другие - для расчета среднего, мин, макс и т.д.

2. Общие итоги

Интуитивно понятно из названия - инструмент рассчитывает итоги по всем строкам/столбцам сводной таблицы.

— На вкладке Конструктор нажимаем на ярлык Общие итоги. Варианты на выбор:

▫️Отключить для строк и столбцов - отключение всех итогов.
▫️Включить для строк и столбцов - включает расчет общих итогов по всем фронтам.
▫️Включить только для строк.
▫️Включить только для столбцов.

Подробнее в видео↓

​​7.5 Рассчитываем долю от общего

Алоха!
Помните пост о рассчитываемых средних значениях, мин, макс? Если забыли, то вот ссылка на пост.

Так вот, есть еще вариант - расчет доли в %.
Этот прием помогает понять вклад персонала в рамках одного дня, или понять какая доля затрат по статьям в рамках одного месяца.
Просто must have в работе со сводными таблицами!

Как рассчитать долю в %

Описываю вариант с созданием дополнительных значений. Если оно вам не требуется - пропустите первый шаг.

— Создаете дубль поля значений. В рамках видеопримера это поле Оценки. Просто перетягиваете его в блок Значения, повторно.
— ПКМ по полю. Выбрать Параметры полей значений. Вкладка Дополнительные вычисления.
— В выпадающем списке выбрать вариант % от суммы по строке. На мой взгляд самый рабочий вариант. Рассчитывает долю в рамках одной строки. Если у вас в строках месяцы, то доля будет в рамках одного месяца.

Видео↓

7.6 Вычисляемые поля сводной таблицы

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

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

Залетайте на материал↓

🔗 Изучить материал | Связь
⠀

​​7.7 Срезы в сводных таблицах

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

Срез - это группа интерактивных кнопок, с помощью которых мы быстро и легко отфильтруем сводную.
Например срез по ФИО, в котором каждая кнопка - это ФИО 1-го сотрудника, или срез по датам.

Чтобы включить срез для сводной:
— Встаем на сводную и залетаем на вкладку Анализ.
— Нажимаем на кнопку Вставить срез.
— В открывшемся окне выбираете поле, по которому хотите построить срез (фильтр).
— Well done. Появилось окно среза.

Варианты использования:
1) Если требуется выбрать одно значение среза, то просто нажимаете ЛКМ.
2) Если требуется выбрать сплошной список, то нажимаете ЛКМ по первому элементу и с зажатым Shift нажимаете на последний элемент списка.
3) Если требуется выбрать несколько значений вразнобой, то в правом углу среза включите режим Выбрать несколько объектов и выбирайте нужные значения.

Прочитали? Предлагаю еще и посмотреть)

Кейс №16. Быстрое протягивание формул

Буэнос диас!
Наверно многие заполняли формулами таблицы с тысячами строк. Хорошо когда соседний столбец заполнен всплошную (без пустых ячеек) и формула тянется двойным кликом по темной точке в углу.

А как быстро протянуть формулу на тысячи строк с разрывами и пустыми ячейками, да еще чтобы не поседеть от этого занятия?

Ловите простой кейс:
— Заполняем формулой первую ячейку в столбце. Выделяете ячейку.
— Зажимайте Shift и с помощью бегунка, прокручиваете вниз таблицы. К последней ячейке, где должна быть формула.
— Выделяете последнюю ячейку и нажимаете комбинацию Ctrl + D. Это сочетание заполняет все выделенные ячейки содержимым ячейки выше. — а там формула)

Посмотрите в видео↓

​​7.8 Временные шкалы

Салют, друзья!
Уверен, что когда я рассказывал о срезах, многие заметили отдельный инструмент Временные шкалы. Сегодня о нем коротко и поговорим.

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

Как сделать?
— Встаем на сводную и переходим на вкладку Анализ.
— Нажимаем на кнопку Вставить временную шкалу.
— В открывшемся окне выбираете поле дат/времени.
— Готово. Достаточно выбрать группировку: год/квартал/месяц/день. И с помощью бегунка выбираем промежуток необходимых дат.

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

Видео↓

7.9 Визуализация в сводных таблицах

Hello, friends!
Давно мы с вами не виделись) Готовлю для вас второй проект, который также поможет преодолеть любые рабочие и личные задачи с помощью таблиц. В ближайшие дни анонсирую его на канале.

Так, у нас осталась самая малость по сводным таблицам - это их визуальная составляющая. Здесь особо не разгуляться, в сводных всего 2 инструмента визуализации:

▫️Стили сводных таблиц
▫️Сводные диаграммы

О них сегодня и поговорим. И если у вас остались вопросы по сводным таблицам или считаете что материала не хватило - пишите мне в бота - @excelstudybot.

Следующим циклом будет материалов будет Пакет анализа Excel. Многие о нем не знают, а вещь стоящая.

А теперь залетайте на последний материал по сводным↓

🔗 Изучить материал | Связь
⠀

8.1 Пакет анализа Excel. Подбор параметра

Бонджорно, подписчики!
Стартуем с нового цикла материалов — Пакет анализа Excel.
Пакет анализа выручает в тех случаях, когда созданы сложные модели данных с зависимостями, а вам нужно быстро и просто прикинуть какой доход вам нужен, чтобы получить прибыль в 5 млн руб.🍋

Чтобы вручную это не вертеть, есть пакет анализа с инструментом Подбор параметра. Сегодня говорим о нем.

Залетайте на новый материал↓

🔗 Изучить материал | Связь
⠀

Всем привет!

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

Расширенный фильтр за раз отсеет таблицу по всем продажам в мае свыше 150 000 руб., продажам июня меньше 500 000 руб. и по всем продажам менеджера Иванова. В этом примере 3 условия одновременно, и это не предел😈

Этот кейс получился объемным, поэтому заходите на статью в telegraph↓

🔗 Изучить материал | Связь
⠀

​​Добрый вечерочек!

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

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

Для фильтрации затрат выше среднего достаточно вписать в условие такую формулу:
= C7 > СРЗНАЧ ($C$7:$C$25)

▫️C7 — значение первой строки. Само значение никак не используется в фильтрации, просто требуется привязка к исходной таблице.
▫️СРЗНАЧ ($C$7:$C$25) — расчет средних.

Еще момент о режимах работы расширенной фильтрации.
— Если формулу поставить в одну строку с другим условием (статья затрат = Аренда), то Excel будет искать в режиме функции И — найди и затраты по аренде и выше среднего.
— Если формулу поставить в другую строку, то Excel будет искать в режиме функции ИЛИ — найди или затраты по аренде или выше среднего.

Посмотрите аналогичный пример в видео и попробуйте сделать сами)

​Привет, друзья!

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

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

? (знак вопроса) - один неизвестный символ. Имя Василий можно найти например так Васи??? или Ва?????, или Васили??.

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

Покажу на примере статьей затрат. Найду нужные статьи с подстановочными знаками Excel↓

​​Массовое назначение имен

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

Давайте вспомним о прекрасной возможности Excel - об именновых диапазонах. Если забыли, освежите память здесь.
Имена для ячеек - это здорово. Запись в расчете = Доходы - Расходы - Налоги куда понятнее, чем = A4 - B10 - V290 для случайного читателя вашей таблицы.

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

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

Назначаем имена
Чтобы быстро назначить сразу 3 имени для 3-х полей:

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

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

И конечно видеопример с Netflix. Для всех любителей сериальчиков и кино↓

8.2 Сценарии Excel. Часть 1

Чао!

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

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

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

Вот за автоматическу подстановку исходных данных в ваш расчет и отвечает Диспетчер сценариев, один из инструментов пакета анализа в Excel. Он сократит время на расчет ваших прибылей.

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

🔗 Изучить материал | Связь
⠀

​​Хэй, друзья👋🏻

Уже 2021 год дышит в затылок и пора строить планы. Это могут быть планы на виллу в Салерно, чемпионство NBA, получить Пулитцеровскую премию. Нужное подчеркнуть.
Я же начинаю с самого необходимого - с планов на обновление контента на канале)

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

Что изменится?

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

- Добавлю на канал игровых викторин для закрепления теории.

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

- Формат постов изменится и вся информация будет только на канале. Без переходов на telegra.ph и другие сервисы для статей. Хотя бы постараюсь их минимизировать.

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

Никуда не уходим, всё только начинается🕺🏼
Stay tuned

​​Сценарии. Часть 2

Салют!
Завершим тему сценариев.

В примере первой части (https://t.me/exstudy/194) мы рассчитывали доходы через сценарии Excel. Посчитали, здорово, перед глазами красная феррари и вилла в Портофино.
Но не спешим покупать билеты в Италию.
После расчета, последний правильный шаг - это вывод всех сценариев в одну таблицу. Она то и покажет эффективность вариантов.

Как вывести все сценарии в автоматический отчет

— Заходим на вкладку Данные, кнопка Анализ "что если?"., пункт Диспетчер сценариев.
— Внутри Диспечтера будет кнопка Отчет.
— После нажатия на выбор будет 2 вариации:

1. Более привычная - сводная таблица, где можно изменять ее форму в режиме конструктора.
2. И, так называемая, структура. Классический отчет, где отражены исходные данные и результаты.

Классная видео-заставка, не правда ли?↓