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