VBA.Excel

VBA.Excel

https://www.youtube.com/c/ExcelVBABillK/featured
На Пикабу
поставил 8 плюсов и 2 минуса
отредактировал 1 пост
проголосовал за 3 редактирования
Награды:
самый сохраняемый пост недели самый сохраняемый пост недели более 1000 подписчиков
48К рейтинг 4483 подписчика 4 подписки 56 постов 53 в горячем

Как в Excel суммировать профильтрованные таблицы – Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Если в Excel нужно просуммировать (или вычислить среднее, максимальное, минимальное и т.д.) значение из значений лишь профильтрованных ячеек таблицы, использовать придётся не привычную функцию СУММ (СРЗНАЧ, МАКС, МИН и т.д.), а придётся прибегнуть к использованию функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Вот в чём особенность. В таблице ниже я суммировал значения с помощью функции СУММ:

Как в Excel суммировать профильтрованные таблицы – Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ Microsoft Excel, Функция, Таблица, Видео, Длиннопост

Ниже теперь подсчитаем сумму с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Как и говорилось, эта функция может проводить разные математические действия. Нужно е математическое действие указывается в первом аргументе функции (в нашем примере 9 - СУММ), а во втором указывается диапазон значений:

Как в Excel суммировать профильтрованные таблицы – Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ Microsoft Excel, Функция, Таблица, Видео, Длиннопост

Подтверждаю ввод функции и сперва результаты идентичны:

Как в Excel суммировать профильтрованные таблицы – Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ Microsoft Excel, Функция, Таблица, Видео, Длиннопост

Но это так лишь до тех пор, пока мы не применим фильтры (выбираем таблицу -> Данные -> Фильтр) и не профильтруем строки по определенному значению. Я, например, профильтровал таблицу лишь по региону «Юг», и вот результаты:

Как в Excel суммировать профильтрованные таблицы – Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ Microsoft Excel, Функция, Таблица, Видео, Длиннопост

СУММ всё также выводит 2460, тогда как ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает лишь прошедшие фильтр значения – 604.

Вот такая интересная функция. При этом, если посмотреть более подробно на доступные вычисления функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, то можно заметить, что у каждого действия есть дубликат (так, например, СУММ имеет два аргумента – 9 и 109). В чём разница этих вычислений я рассказал в следующем видео, в котором помимо прочего также наглядно представлено всё показанное выше. Предлагаю его посмотреть:

Показать полностью 4 1

Числа прописью в Excel быстро и без вспомогательных инструментов!

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


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


Вот пример её действия:

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

Файл с формулой можно скачать по следующей ссылке:


https://drive.google.com/file/d/1YLsHzifuVgath7ruzLlsxywOdTt...


Как можете заметить, в функции очень много ссылок на одну и ту же ячейку – H6. Естественно, вручную перетаскивать все ссылки H6 не вариант (их 97!). Поэтому вот как работаем с этой формулой:

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


2. Выбираем две ячейки, включая ячейку с формулой (показываю на примере самого файла по ссылке – в формуле с выводом сотых заменим ссылку на новое значение в ячейке C6):

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

Лучше всего, чтобы вторая выбранная ячейка была пустой, или в крайнем случае не содержала той же самой ссылки, так как мы её сейчас будем автоматически менять.


3. Вызываем окно поиска и замены с помощью CTRL+H


4. В «Найти» вписываем заменяемый адрес – H6, в «Заменить на» - адрес новой нужной ячейки – например, C6

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

5. Нажимаем на «Заменить все». Замена происходит лишь в обеих выбранных ячейках (именно для этого мы и выбирали вторую ячейку, чтобы ограничить замену лишь на выбранный диапазон). Готово! Формула сразу ссылается на нужное значение:

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

Из особенностей формулы:

1. Формула абсолютно самостоятельна и не использует VBA или сторонние Add-Ins

2. Формула работает с числами до 999.999.999

3. Формула правильно склоняет слова

4. Вся формула в свою очередь состоит в основном из формул ЛЕВСИМВ, ПСТР, ЕСЛИ, ТЕКСТ, И, ВЫБОР

5. Количество символов в формуле с прописью целых и сотых: 6034

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

Показать полностью 3 1

Условное форматирование сводных таблиц

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


В качестве примера, выбрав значения следующей сводной таблицы применяем к ней классическое цветовое УФ (Главная -> Условное Форматирование -> Цветовые шкалы -> Цветовая шкала «Зеленый-Желтый-Красный»):

Условное форматирование сводных таблиц Microsoft Excel, Таблица, Видео, Длиннопост

Если присмотреться, то можно увидеть этот самый неброский элемент для настройки примененного правила форматирования:

Условное форматирование сводных таблиц Microsoft Excel, Таблица, Видео, Длиннопост

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


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

Условное форматирование сводных таблиц Microsoft Excel, Таблица, Видео, Длиннопост

Настройка «Ко всем ячейкам, содержащим значения «Выручка»» привязывает условное форматирования абсолютно ко всем значениям выручки, в том числе и к общему итогу:

Условное форматирование сводных таблиц Microsoft Excel, Таблица, Видео, Длиннопост

Последний же вариант применяет условное форматирование лишь к значениям отдельных городов (что в нашем примера и имеет смысл):

Условное форматирование сводных таблиц Microsoft Excel, Таблица, Видео, Длиннопост

Теперь, даже если мы меняем размещение полей, Excel всегда знает, к какому именно диапазону следует применять условное форматирование. Вот, например всё та же сводная таблица, только теперь поле «Город» находится в разделе столбцов:

Условное форматирование сводных таблиц Microsoft Excel, Таблица, Видео, Длиннопост

Вот такая особенность работы УФ со сводными таблицами. При этом, есть еще пара особенностей применения УФ к сводным таблицам, которые имеют множество полей как в разделе строк, так и в разделе столбцов. О них (а также о всём рассказанном выше, но в наглядной форме) я рассказал вот в этом видео, предлагаю его посмотреть:

Показать полностью 5 1

Формулы Excel – все особенности, тонкости и трюки в одном видео!

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


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


Приятного просмотра! 😊

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем

Наверняка тебе уже доводилось встречаться с функцией ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel. Эта функция автоматически вставляется в ячейку, если мы прописываем формулу и ссылаемся на одну из ячеек сводной таблицы.


Так, в следующем скриншоте я хотел сослаться на ячейку С3, но вместо привычной нам ссылки в итоге была вставлена функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем Microsoft Excel, Таблица, Видео, Длиннопост

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


Делается это очень просто. Выбираем одну из имеющихся сводных таблиц (1) и во вкладке «Анализ» (2), щелкнув по треугольнику в выпадающем списке "Параметры" убираем галочку напротив пункта «Создать GetPivotData», просто нажав на него (3):

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем Microsoft Excel, Таблица, Видео, Длиннопост

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

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем Microsoft Excel, Таблица, Видео, Длиннопост

Вот и всё! Настолько просто можно отключить автовставку функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. При этом, обрати внимание на то, что измененная нами настройка является настройкой на уровне программы Excel, то есть если мы откроем любой другой файл Excel, в нём уже точно также данная функция будет отключена. Если же мы при этом откроем этот же файл на другом компьютере, где настройка «Создать GetPivotData» еще не была отключена, то там будет всё также вставляться рассмотренная функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Но не переживай! Все уже прописанные формулы не будут изменяться. В конце концов ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — это самая обычная функция, которая позволяет получать данные из сводных таблиц с помощью указания полей сводной таблицы вне зависимости от их физического расположения на рабочем листе.


Также предлагаю посмотреть это короткое видео, в котором всё описанное в этом посте наглядно показано:

Показать полностью 2 1

ВСЁ о сводных таблицах с нуля за полчаса

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

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


Приятного просмотра! 😊

Показать полностью

Как создать Дашборд в Excel

Знаю, что хорошо заходят посты, имеющие определённое количество текста/картинок. Тем не менее, создание дэшбордов в Excel – эта такая тема, которую уместить в несколько картинок и листов текста ну очень сложно! А поскольку поделиться еще одним из вариантов создания дэшбордов всё-таки очень хочется, решил поделиться в этом посте информацией в формате видео:

Что рассмотрено в видео:

• как создавать интерактивные дэшборды в Excel со сводными и не только диаграммами на основе сводных таблиц

• оптимальная структура рабочей книги для дэшборда

• подход в создании дэшбордов для их долгосрочного использования и развития (разработка отдельных деталей дэшборда на отдельных листах)


Приятного просмотра! 😊

Показать полностью

ПКМ и Drag & Drop в Excel

Интересный функционал правой кнопки мыши в Excel, о котором немногие знают. С помощью ПКМ в Excel можно проводить самые частые действия над диапазонами (копирование, вставка, связывание и т.д.).


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


Работает этот подход очень просто:

1. Выбираем привычным образом, с помощью левой кнопки мышки, нужный диапазон

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

2. Наводим мышку на выделенный край выбранного диапазона

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

3. Зажимаем правую кнопку мышки и перетягиваем выбранный диапазон в нужное место

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

4. В открывшемся контекстном окне выбираем нужную функцию – например вставку только значений, вставку только форматирования и т.д. Я вставил в качестве примера только значения и вот результат:

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

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

Показать полностью 4 1
Отличная работа, все прочитано!