Кейс №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. И, так называемая, структура. Классический отчет, где отражены исходные данные и результаты.

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

​​Кейсы. Рабочая область

Хэллоу!

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

1 вариант. Через ScrollArea
— Переходите в Файл - Параметры - Настроить ленту - Включаете вкладку Разработчик. Если вы еще не сделали это ранее.
— Далее переходим на вкладку Разработчик. В группе Элементы управления переходим по пункту Свойства.
— Ищем поле ScrollArea и задаем в значении диапазон ячеек от левой верхней ячейки до нижней правой. В примере это А1:В12. Нажимаем Enter и в путь. Теперь все ячейки будут заблокированы для действий, кроме указанного диапазона.
Отключаем через удаление диапазона в ScrollArea.

2 вариант. Визуальное ограничение через Скрыть
Подойдет, если вы хотите только сделать видимость ограничений для новичков.
— Выделяем столбец, находящийся справа от края рабочей области. Нажимаем Ctrl + Shift + →. Выделяются все столбцы справа. ПКМ - Скрыть.
— Выделяем строку, находящуюся снизу от края рабочей области. Нажимаем Ctrl + Shift + ↓. Выделяются все строки ниже. ПКМ - Скрыть.

Вуаля. Остается только рабочая область. Технически ограничений нет, визуально сузили область внимания пользователя)

Позже поговорим про защиту таблицы более серьезными методами.
Чао!

​​Таблица данных

Всем чао!
Сценарии освоили? Так вот есть инструмент поинтереснее - Таблица данных. Рассчитывает в массовом порядке кучу показателей.

Беру понятный пример: вложили 200 тыс. рублей на 2 года под 50%-ную ставку. Может в акции Илона Маска вложились, может биткоинов прикупили...не суть)

Посчитали доход, неплохо, но этого мало нашей широкой душе. Хорошо бы понимать какой доход мы будем получать на процентных ставка от 5% до 90% и за срок от 1 года до 10 лет. Не считать же это вручную!

— Самой левой верхней ячейкой должна быть наша расчетная формула. Как база для таблицы.
— По столбцам проставляю проценты: 5%, 10%, 20% ... 90%.
— По строка проставляю срок в количестве лет от 1 до 10.
— Выделяем таблицу с шапками и идем на ленту - вкладка Данные - Анализ "что если?" - Таблица данных.

Система попросит указать, какие значения мы будем брать для столбцов, какие для строк. И тут главное не облажаться: указываем значения из основной формулы.
— Для столбцов берем первичное значение процентной ставки в 50%. То значение из формулы.
— Для строк берем первичное значение срока в 2 года. Аналогично.

Остальное - дело техники. Тоже самое на видео↓

Обновление знаний по горячим клавишам и комбинациям

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

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

Часто используемые функции

▫️ Ctrl + С – копировать выделенный контент.
▫️ Ctrl + V – вставить скопированный в буфер обмена контент.
▫️ Ctrl + X – вырезать выделенный контент.
▫️ Ctrl + B – полужирное начертание текста.
▫️ Ctrl + I – курсивное начертание текста.
▫️ Ctrl + F – поиск.
▫️ Ctrl + S – сохранить рабочую книгу.
▫️ Ctrl + W – закрыть рабочую книгу.
▫️ Ctrl + A – выделение всего листа. Комбинация работает во многих программах, не только в Excel).
▫️ Ctrl + D – протягивание формулы вниз.
▫️ Ctrl + R – протягивание формулы вправо.
▫️ F4 при вводе формулы – фиксация адреса ячейки с помощью знака $.
▫️ F4 – повторить предыдущее действие.
▫️ Ctrl + Z – отменить предыдущее действие. Шаг назад.
▫️ Ctrl + Y – вернутся к последнему действию. Шаг вперед.

Навигация и редактирование листа

▫️ Ctrl + ↓ - перейти к самой нижней заполненной ячейке. Аналогично работают другие стрелки.
▫️ Ctrl + Shift + ↓ - выделение всех заполненных ячеек по направлению стрелки. Аналогично работают другие стрелки.
▫️ Shift + ↓ - выделение одной ячейки вниз. Аналогично работают другие стрелки.
▫️ Ctrl + Пробел – выделение всего столбца.
▫️ Shift + Пробел – выделение всей строки.
▫️ Ctrl + плюс/минус – добавление ячейки/удаление ячейки. Если выделяете строку или столбец, то в таком случае комбинация добавляет или удаляет строку, столбец.
▫️ Alt + Tab – переключение между активными окнами. Общая комбинация для Windows, но полезная для случаев, когда открыто множество рабочих книг Excel.
▫️ Ctrl + PgUp или PgDn – переключение между листами рабочей книги.
▫️ Ctrl + Backspace – возврат к активной ячейке. На случай если забыли, где остановились.

​​Защита данных в Excel

Защитить данные в таблице возможно следующими манипуляциями:
— Защитить лист
— Защита рабочей книги
— Сохранить информацию в формате PDF без возможности дальнейшего изменения.

Есть еще вариант защиты проектов в VBA, однако это уже другая тема.
Начнем с простого - Защита листа.

1. Нажав на ячейку ПКМ - Формат ячеек, перейдите в раздел Защита.
✅ Если стоит чек-бокс напротив Защищаемая ячейка, то это значит что ячейку можно заблокировать от доступа третьих лиц.
❎ Если чек-бокс не стоит, то ячейку можно изменять, несмотря на включаемую защиту. Будьте внимательны!

2. На ленте выбираете вкладку Рецензирование - кнопка Защита листа.

3. В диалоговом окне возможно выбрать варианты защиты. Подтверждаем. При желании можете ввести пароль для разблокировки защиты.

4. Теперь все защищаемые ячейки заблокированы к изменению.

5. Защита снимается с помощью кнопки Снять защиту листа.

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

Варианты защиты листа в Excel

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

Переходите по вкладке ленты Рецензирование – Защитить лист, перед вами появятся вариант защиты. По-умолчанию включено 2 флажка:

▫️Выделение заблокированных ячеек - разрешает на защищенном листе выделять заблокированные ячейки.

▫️Выделение незаблокированных ячеек - разрешает на защищенном листе выделять незаблокированные ячейки.


Однако это не мешает вам использовать другие опции для расширения возможностей:

▫️Форматирование ячеек - разрешает форматировать заблокированные ячейки.

▫️Форматирование столбцов - разрешает изменять ширину столбцов и скрывать их.

▫️Форматирование строк - разрешает изменять высоту строк и скрывать их.

▫️Вставка столбцов - разрешает вставку новых столбцов.

▫️Вставка строк - разрешает вставку новых строк.

▫️Вставка гиперссылок - разрешает вставлять гиперссылки в ячейки, в том числе и на заблокированные.

▫️Удаление столбцов - разрешает удалять столбцы.

▫️Удаление строк - разрешает удалять строки.

▫️Сортировка - разрешает выполнять сортировку данных, но только если это не заблокированные ячейки.

▫️Использование автофильтра - разрешает использовать уже установленный вами автофильтр. Новый не добавить.

▫️Использование сводной таблицы и сводной диаграммы - разрешает изменять сводную таблицу/диаграмму и создавать новые.

▫️Изменение объектов - разрешает изменять объекты (фигуры), диаграммы, примечания.

▫️Изменение сценариев - разрешает пользоваться сценариями. Если забыли про них, то вот начало: https://t.me/exstudy/194