Статистика ВК сообщества "MS Excel Tips&Tricks"

Шпаргалка с Excel-формулами

Распечатай и проблем станет меньше :)

Подборка решений от [club55420427|Финансовый анализ в Excel и Power BI]

30 необычных диаграмм для Dasboard-ов, презентаций и отчетов.
5 файлов Excel с пошаговыми инструкциями.

Качайте, изучайте, применяйте :)

Сумма ячеек по цвету заливки

Меня часто спрашивают, как суммировать ячейки в зависимости от цвета заливки?
Стандартными формулами – никак, Excel не реагирует на заливку ячеек.
В этом случае поможет простой макрос:

Public Function Cумма_Цвет (Диапазон As Range, Цвет As Range) As Double
Dim Sum As Double
Application.Volatile True
For Each cell In Диапазон
If cell.Interior.Color = Цвет.Interior.Color Then
Sum = Sum + cell.Value
End If
Next cell
Сумма_Цвет = Sum
End Function

По сути этот макрос – функция (как СУММ, ЕСЛИ или любая другая), с той лишь разницей, что она создана пользователем (то есть Вами).

После вставки этого макроса в Вашу книгу, используйте формулу «Сумма_Цвет» как обычную формулу.

У функции «Сумма_Цвет» 2 аргумента:
- Диапазон: диапазон ячеек с заливкой.
- Цвет: та ячейка, по цвету которой нужно суммировать.

Функции ИНДЕКС + ПОИСКПОЗ + Формулы массива

Ранее в группе было опубликовано 3 видео с подробным разбором работы функций ИНДЕКС + ПОИСКПОЗ + формул массива на примере нахождения дат платежей по компаниям.

Видео 1: нахождение даты первого платежа
Видео 2: нахождение даты последнего платежа
Видео 3: нахождение второй и последующей дат платежа

Сохраните, чтобы не потерять!

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

Удалить лишние пробелы

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

Стандартный способ удаления пробелов – это опция «найти и заменить» (для ее вызова используйте клавиши Ctrl+F). Однако такой способ приведет к удалению всех пробелов в строке.

К счастью, в Excel существует функция СЖПРОБЕЛЫ (TRIM), которая удаляет именно лишние пробелы, а именно:
1) Все пробелы перед первым символом в строке.
2) Все пробелы после последнего символа в строке.
3) Оставляет 1 пробел между словами.

Посмотрите примеры в прикрепленном файле и работу функции СЖПРОБЕЛЫ (TRIM).

P.S. Кто какие варианты удаления лишних пробелов еще знает?

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

Диаграмма Ганта с элементами контроля сроков в Excel

Вторая часть серии "диаграмма Ганта", теперь с элементом автоматического контроля сроков + файл с примером.

Сводные таблицы Excel с нуля до профи за полчаса + Дэшборды

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

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

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

***
Мастер-класс по сводным диаграммам и фильтрам
Уровень освоения: выше среднего.

Сопоставление русских и английских формул

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

Получилось целых 328 формул)

Сумма ячеек по цвету заливки

Меня часто спрашивают, как суммировать ячейки в зависимости от цвета заливки?
Стандартными формулами – никак, Excel не реагирует на заливку ячеек.
В этом случае поможет простой макрос:

Public Function Cумма_Цвет (Диапазон As Range, Цвет As Range) As Double
Dim Sum As Double
Application.Volatile True
For Each cell In Диапазон
If cell.Interior.Color = Цвет.Interior.Color Then
Sum = Sum + cell.Value
End If
Next cell
Сумма_Цвет = Sum
End Function

По сути этот макрос – функция (как СУММ, ЕСЛИ или любая другая), с той лишь разницей, что она создана пользователем (то есть Вами).

После вставки этого макроса в Вашу книгу, используйте формулу «Сумма_Цвет» как обычную формулу.

У функции «Сумма_Цвет» 2 аргумента:
- Диапазон: диапазон ячеек с заливкой.
- Цвет: та ячейка, по цвету которой нужно суммировать.