MS Excel Tips&Tricks

Недельный дайджест за 30.09 - 06.10

За прошедшую неделю было много новых материалов по Excel, но самые важные посты прошедшей недели, на мой взгляд, вот эти:

- Использование символов для визуализации данных в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18726
- Как быстро ввести римские числа в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18728
- Суммирование по цвету в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18731
- Спарклайны: https://vk.com/excel_tips_tricks?w=wall-111634944_18738
- Сумма по нескольким листам: https://vk.com/excel_tips_tricks?w=wall-111634944_18739
- Ошибки и ограничения в функции ВПР: https://vk.com/excel_tips_tricks?w=wall-111634944_18742
- Автозамена значений в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18746
- Функция ПРОСМОТРХ (XLOOKUP): https://vk.com/excel_tips_tricks?w=wall-111634944_18756
- Живой скриншот таблицы в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18757

Не забудьте подписаться на уведомления, чтобы не пропускать новые посты: зайдите в группу - на телефоне справа вверху "колокольчик"

Желаю продуктивной и эффективной работы в Excel! До встречи на следующей неделе!

Excel базовый: https://vk.cc/czwHWl

Excel для анализа данных: https://vk.cc/cqIouw

10 эффективных трюков в Excel

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

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

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

https://vk.com/video-111634944_456239797?access_key=430a18d8a34199ff57

Живой скриншот таблицы в Excel

https://vk.com/clip-111634944_456240045

Функция ПРОСМОТРХ (XLOOKUP)

Данная функция, по словам разработчиков Excel, призвана заменить такие полезные и всем известные функции как ВПР, ГПР, ИНДЕКС + ПОИСКПОЗ.

Новая функция работает по схожей с ВПР логике, однако имеет для тонкостей и преимуществ.

Подробнее про новую функцию в Excel - смотрите в видео.

https://vk.com/video-111634944_456239726?access_key=aa9cbadcb6f25b8c8b

Ошибки и ограничения в функции ВПР

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

https://vk.com/@-121498745-oshibki-i-ogranicheniya-funkcii-vpr

Спарклайны

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

Для того, чтобы создать спарклайн, сделайте следующее:
1) На вкладке «Вставка» найдите группу «Спарклайны».
2) Выберите вид спарклайна: график или гистограмма.
3) В появившемся окне выберите диапазон данных и ячейку, где будет располагаться спарклайн.
4) Нажмите «ОК».

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

ТОП 10 горячих клавиш в Excel

Смотрите в vk video:

https://vk.com/video-111634944_456240358?access_key=e73edfb80d973d42a7

https://vk.com/video-111634944_456239799?access_key=ce2f3f96e04ea41117

Суммирование по цвету в Excel

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

Поэтому для подобных задач приходится писать пользовательские функции. В этом посте хочу поделиться примером кода UFD (User Defined Function – Пользовательской функции) для суммирования значений ячеек из определенного диапазона, соответствующих заданному цвету заливки.

Вот код VBA:

Option Explicit
Function SUMIFCOLOR(rgCellsToSum As Range, rgColorSample As Range)
Dim rgCellChecked As Range
Dim intColorIndex As Integer
Dim dblSum As Double
Application.Volatile
'Сохраняем указание цвета в числовую переменную
intColorIndex = rgColorSample.Interior.ColorIndex
'Проверяем каждую ячейку и сохраняем её значение в промежуточную переменную
For Each rgCellChecked In rgCellsToSum
If rgCellChecked.Interior.ColorIndex = intColorIndex Then
dblSum = dblSum + rgCellChecked.Value
End If
Next rgCellChecked
'Вносим значение в функцию
SUMIFCOLOR = dblSum
End Function

Итак, что теперь с этим кодом надо сделать.
1. Открываем требуемый файл
2. Открываем окно редактора VBA с помощью комбинации клавиш Alt+F11
3. В нём добавляем новый модуль: Insert (1) -> Module (2)
4. В открывшемся поле вставляем код (отступы, к сожалению, по желанию придётся проставить вручную):

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

При этом обрати внимание – чтобы теперь в файле вставленный макрос сохранился, сохранить файл надо будет в формате xlsm.

При этом, данная функция является волатильной (перерасчет происходит всегда, даже если изменения произошли в другой ячейке), но изменения формата не являются триггерами перерасчёта – поэтому, если хочешь, чтобы она всегда отражала актуальное значение, её надо немного расширить с помощью событийной процедуры Worksheet_SelectionChange. Это уже более объемная тема, которую тяжело компактно описать в тексте, поэтому предлагаю посмотреть прикрепленное видео. Приятного просмотра! :)

Excel базовый: https://vk.cc/czwHWl

Excel для анализа данных: https://vk.cc/cqIouw

Как быстро ввести римские числа в Excel

Если интересно узнать гораздо больше полезных фишек в Excel, то записывайтесь на новый поток обучения продвинутым возможностям в Excel!
Не уверены, что вам подойдет именно продвинутый курс? Посмотрите программу базового курса Excel с нуля!
Оба курса стартуют уже 7 октября, ссылки на них оставлю в комментариях.
Специально для подписчиков группы промокод «Excel_tips_tricks» даст дополнительную скидку 7% на данные курсы.

https://vk.com/wall-111634944_18728

Найти n-ое наибольшее / наименьшее значение

Ранее в группе разбирались полезные формулы МИН и МАКС, которые позволяют определять соответственно минимальное и максимальное значения массива данных.

Однако как быть, если вам нужно найти не самое большое значение, а, например, 2-е или 3-е наибольшее значение? Для таких задач можно использовать формулы НАИБОЛЬШИЙ и НАИМЕНЬШИЙ.

Синтаксис у обоих функций одинаков:
1) Первый аргумент – массив. Это диапазон данных, в котором нужно определить n-ое максимальное / минимальное значение.
2) Второй аргумент – k. Этот аргумент показывает, какое именно максимальное / минимальное значение по счету нужно взять. Например, в формуле НАИБОЛЬШИЙ k=2 означает, что из массива данных нужно взять 2-ое максимальное значение.

Использование символов для визуализации данных в Excel

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

Смотрите в VK video:

https://vk.com/video-111634944_456240357?access_key=0ef1b1cf5e43a74da3

Недельный дайджест за 23.09 - 29.09

За прошедшую неделю было много новых материалов по Excel, но самые важные посты прошедшей недели, на мой взгляд, вот эти:

- Функция РАЗНДАТ в Excel на примерах: https://vk.com/excel_tips_tricks?w=wall-111634944_18655
- Преобразовать числа-как-текст в нормальные числа: https://vk.com/excel_tips_tricks?w=wall-111634944_18660
- Связанные выпадающие списки в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18662
- Печать документов в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18673
- Как избежать зависания Excel при удалении множества строк: https://vk.com/excel_tips_tricks?w=wall-111634944_18679
- Импорт таблицы из PDF в Excel: https://vk.com/excel_tips_tricks?w=wall-111634944_18681
- Микрографики в ячейках с помощью формул: https://vk.com/excel_tips_tricks?w=wall-111634944_18688
- Как создать сводную таблицу по 3-м файлам: https://vk.com/excel_tips_tricks?w=wall-111634944_18698

Не забудьте подписаться на уведомления, чтобы не пропускать новые посты: зайдите в группу - на телефоне справа вверху "колокольчик"

Желаю продуктивной и эффективной работы в Excel! До встречи на следующей неделе!

Минутка воскресного юмора :)

Как создать сводную таблицу по 3-м файлам

https://vk.com/clip-111634944_456240044

Курс по финансовой аналитике: https://vk.cc/cr7FoX

Как посчитать количество слов в ячейке Excel

В новом видео по шагам напишем формулу для расчета количества слов в каждой ячейке Excel.

Файл с формулой прикреплен ниже

Напоминаю, что уже 3 октября стартует обучение на финансового аналитика длительностью 6 месяцев! Всю информацию по данному обучению ищите в комментариях к посту.
По традиции, для подписчиков группы промокод «Excel_tips_tricks» даст дополнительную скидку 7% на данный курс.

https://vk.com/video-111634944_456239793?list=cf0833d64a8be495a2

Как закрепить шапку таблицы в Excel

Смотрите в VK video:

https://vk.com/clip-111634944_456240355