Добрый вечер!

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

▫️ СУММЕСЛИМН
▫️ СРЗНАЧЕСЛИМН
▫️ СЧЁТЕСЛИМН

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

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

Более подробно в материале.

Материал: 4.8 Сумма, среднее и количество по множеству условий
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы

Мой подписчик недавно подкинул отличную идею: “Неплохо бы иметь краткую энциклопедию по функциям с синтаксисом. То есть с сухой информацией, если вдруг что забыл”

Я за любую инициативу)
Поэтому сделал Краткий каталог функций. Здесь информация высушена до синтаксиса функций. И только.

Забыли для чего нужен диапазон условий в СУММЕСЛИ? Зашли в краткий каталог и посмотрели - в этом его основная функция.

Все новые предложения и вопросы по основному материалу можете написать сюда: @excelstudybot.

Краткий каталог функций

Запасная ссылка

Ссылка на каталог добавлена в навигацию канала.
#каталогфункций

Алоха!

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

Ждите в скором времени.
Сегодня же у нас материал по трем логическим функциям:

▫️ Функция И
▫️ Функция ИЛИ
▫️ Функция НЕ

Этот материал тоже 3-в-1, как и прошлый.
Функции составляют костяк логических операторов. Их основная область работы - проверка условий и возвращение значений “истина” или “ложь”.
Читайте об этом в материале↓

Их самостоятельное применение не так эффективно, как совместно с другими функциями. Поэтому следующий материал будет о связке ЕСЛИ + (И / ИЛИ / НЕ).

Материал: 4.9 Логические функции И / ИЛИ / НЕ
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень4
#формулы

Привет!

В прошлый раз я обещал материал о логической связке. Сегодня о ней и поговорим.

Тема дня: логическая связка ЕСЛИ + (И / ИЛИ / НЕ).
Внутри вас ждут лаконичные видеопримеры и немного Газманова.

Материал: 4.10 Связка ЕСЛИ + И / ИЛИ / НЕ
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы

Новая рубрика - Кейсы

Всем отличного понедельника!

Материалов на канале накопилось достаточно и пора бы уже использовать эти знания.

Но простые примеры “посчитай количество пони в загоне” или “ВПР по продажам ракет SpaceX" - уже не фонтан.

Поэтому сегодня я запускаю новую рубрику на канале - Кейсы.

▫️ Каждый кейс - это интересное и нетривиальное применение, уже знакомых нам, функций. И чаще всего с нетривиальными задачами.

▫️ Кейс - это небольшой пост на канале. В сопровождении с фото/видео. Без статей в Телеграфе.

▫️ Кейсы будут выходить в промежутках между основными материалами.

Первый кейс уже здесь↓

#кейсы

​​Кейс №1. Поиск последней заполненной ячейки

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

В таких случаях может помочь ВПР/ГПР.
Вот это поворот!

В Excel есть хак.
Заключается он в том, что если в аргументе “искомое значение” ВПР/ГПР написать нереально большое число (9 999 999) и включить интервальный/приблизительный поиск, то функция будет искать последнюю непустую ячейку.

Это фишка работает с числами. Если хотите искать последнюю заполненную текстовую ячейку, то в искомое значение поставьте “ЯЯЯЯЯЯЯ”.

Пробелы для этой формулы не страшны. Но если в столбцах/строках будет одновременно и текст, и числа, то формула отработает некорректно.

Посмотрите пример в видео.
В первой таблице, последние непустые ячейки ищет ГПР, во второй - ВПР.

1. =ГПР(9999999;B2:G6;1)

2. =ВПР(9999999;A12:E17;1)

И не забывайте включать интервальный поиск: либо пишите последний аргумент “1”, либо вообще не пишите последний аргумент (как в примере).

#кейсы
#ВПР
#ГПР

Ну что, соскучились?

Сегодня у нас блиц-материал. Почти как в “Что, где, когда”.
Блиц-материал - это быстро, кратко и доступно.

Одна статья содержит 10 функций: группа функций Е и ЕСЛИОШИБКА.

▫️ Функции Е часто используются в логическом выражении ЕСЛИ. Необходимы для проверки значений на признак текста/числа/ссылки/ошибки.
▫️ ЕСЛИОШИБКА - отличный чистильщик, заменяет ошибки на другое значение.

Must use.

Материал: 4.11 Функции Е и ЕСЛИОШИБКА
Если не открывается, используйте запасную ссылку
Сложность: 3/10
Связь: @excelstudybot

#уровень4
#формулы

Кейс №2. Генератор случайных чисел

Салют всем!
Сегодня кейс о полном рандоме.

▫️Нужно провести конкурс и в случайном порядке рассчитать места?
▫️Нужны случайные числа для математической модели?
▫️Или ваяешь примеры для Excel?)

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

Функция СЛУЧМЕЖДУ генерируют случайные числа в указанных рамках.

= СЛУЧМЕЖДУ (нижняя граница; верхняя граница)

Всё, что вам нужно сделать - это указать нижнюю и верхнюю числовую границу. Максимально просто. Даже синтаксис объяснять не требуется.

Плюс этой функции в том, что повторное нажатие на Enter генерирует новый массив случайных чисел.

Помните, что генерировать случайные числа вручную - отстой. СЛУЧМЕЖДУ отвечает💪🏻

#кейсы

Всем привет!

Сегодня мы оставляем функции и переходим к визуализации данных в Excel.
Don’t worry. К функциям мы еще вернемся. Не хватайтесь за сердце.

Визуализация в Excel - это условное форматирование, диаграммы, именованные списки и прочие визуальные прелести. Всё то, что помогает увидеть главное.

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

Когда важная информация грамотно выделена на фоне остальных, то и решения принимаются быстрее. Проверено.

Сделаем же первый шаг в сторону визуализации данных.
Тема дня: Условное форматирование ячеек↓

#уровень5

Условное форматирование - это набор инструментов для автоматического форматирования ячеек по правилам.

Этот набор поможет изменять формат ячеек с содержимым больше 0, меньше 100, а может одновременно по двум условиям >50 и <150. Залить цветом, поменять шрифт и т.д.

Или покрасить в зеленый все значения выше среднего. Как вам такое?
Если не нашли подходящее правило, то можете создать свое правило с помощью формулы.
Штука достаточно гибкая.

Заходите в статью telegraph.

Материал: 5.1 Условное форматирование
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень5
#форматирование
#визуализация

Кейс №3. Конкатенация. Часть 1

Конкатенация - склеивание различных строк в одну. В случае с Excel можно склеить значения из разных ячеек в одну строку.

2 способа, чтобы добиться склеивания значений.

1. Функция = СЦЕПИТЬ (текст 1; текст 2….)
Через точку с запятой можно указывать текст в кавычках, пробелы или адреса ячеек со значениями.
На выходе мы получаем объединенную строку значений.

2. Оператор конкатенации - амперсанд (&). Это тот самый знак, который означает английское “and” (и).

Если записать = В2 & “Тест” & F10, то результатом будет объединенная строка из значения ячейки В2, Тест и значения из F10.

У двух инструментов один и тот же эффект, но есть отличия в порядке применения.
Когда дело касается математики, то СЦЕПИТЬ имеет преимущество перед амперсандом.

Что это значит?

▫️ Если написано = 4 & 8 х 8, то результатом будет 464. Сначала умножение, потом склеивание.
▫️ Если написано = СЦЕПИТЬ (4;8) х 8, то результатом будет 384. Сначала СЦЕПИТЬ, потом умножение.

Завтра будет мощный кейс о том, как дуэт Амперсанд + ПРОСМОТР ищет значения по нескольким критериям.

See you soon!

#кейсы

​​Кейс №4. Конкатенация. Часть 2

Всем добрый вечер!
Склеивание проявляется не только в объединении А и Б. Его можно использовать в создании дополнительных условий для поиска.

Сегодня поговорим о работе функции ПРОСМОТР совместно с амперсандом (&).
Оказывается, ПРОСМОТР может искать сразу по нескольким критериям.

Базовый синтаксис функции выглядит так:

=ПРОСМОТР (искомое значение; просматриваемый вектор; вектор результатов)

А вот синтаксис с амперсандом:

= ПРОСМОТР (искомое значение1 & искомое значение2; просматриваемый вектор1 & просматриваемый вектор2; вектор результатов)

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

В этом способе есть 2 особенности.

1. ПРОСМОТР работает в режиме приблизительного поиска. Если не найдет, то может вернуть что-то другое.

2. Перед тем как использовать ПРОСМОТР + Амперсанд, необходимо отсортировать по возрастанию столбцы с искомыми значениями. Делайте это через настраиваемую сортировку.

Посмотреть как работает этот прием вы можете на видео ниже↓

Завтра выйдет следующий материал по визуальной составляющей Excel.
До встречи!

#кейсы

Всем отличной пятницы💃

Вчера не успел выложить пост, исправляюсь)
Лейтмотив на сегодня: “Лучше один раз увидеть, чем сто раз услышать”.
Да, да. Это первая часть материала о диаграммах в Excel.

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

Внутри материала:

▫️ Как создавать диаграммы?
▫️ Ключевые понятия
▫️ Как сделать 2 диаграммы на 1 поле?
▫️ Как форматировать диаграммы?

Материал: 5.2 Диаграммы. Часть 1
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень5
#диаграммы
#визуализация

​​Кейс №5. Фильтрация и сортировка по цвету

Салют!
Фильтрация и сортировка отлично улучшают читаемость таблиц.
В Excel вы можете фильтровать и сортировать данные не только по величине значений, но и по цвету.
Особенно эффективно использовать это совместно с правилами условного форматирования (подробнее тут: https://t.me/exstudy/123 )

Фильтрация

Выделяем шапку таблицы и ставим фильтр. По всей шапке появляются ярлыки раскрываемых списков в виде стрелок. Нажимаем и выбираем пункт “Фильтр по цвету”.

Остается лишь выбрать цвет, по которому хотите отфильтровать данные.

Сортировка по цвету

Выделили фрагмент таблицы для сортировки. Заходим на главной вкладке в пункт “Сортировка и фильтр” - “Настраиваемая сортировка”.
В открытом окне заполняем следующие параметры:

▫️Столбец - выбираем сортируемый столбец.

▫️Сортировка - режим сортировки. Выбираем “цвет ячейки”.

▫️Порядок - выбираем конкретный цвет заливки.

▫️Расположение - Сверху/Снизу. Параметр определяет где будут располагаться цветные значения таблицы: вверху или внизу.

После выбора, цветные ячейки будут расположены вместе.
Если добавить еще один уровень в “Настраиваемой сортировке”, то можно определить порядок сортировки по цветам.

Видео↓

#кейсы

Доброе утро, подписчики!

Сегодня второй эпизод по диаграммам. Внутри материала вы найдете неформальные рекомендации по выбору типа и стиля диаграммы.

Плюс мощная тема, которая сохранит вам нервы и время - быстрое копирование формата диаграмм.

Открывайте статью в телеграфе и начинайте изучать.

Материал: 5.3 Диаграммы. Часть 2
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень5
#диаграммы
#визуализация

​​Кейс №6. Диаграмма воронки

Для анализа продаж в бизнесе часто используют диаграммы в виде воронок. Проще говоря “Воронка продаж”.
Диаграмма отражает все этапы движения клиента от его обращения до совершения покупки.

Пользователям Excel 2019 и Office 365 чертовски повезло. В этих версиях есть предустановленная диаграмма “Воронка”. В версиях постарее такой диаграммы нет и приходится немного потанцевать с бубном.

Сегодняшний кейс для обладателей Excel 2016, 2013 и далее.
Действия по шагам:

1. Чтобы построить воронку, необходимо отсортировать данные по убыванию.

2. Выделяем данные и вставляем диаграмму с типом Объемная нормированная гистограмма с накоплением.

3. ПКМ по ряду данных на диаграмме - Формат ряда данных - в разделе “Фигура” выбрать Полный цилиндр.

4. ПКМ по оси - “Формат оси” - поставить флажок на пункте Обратный порядок значений.

5. Выделяем область построения (диаграмма + сетка + оси). Также переходим в формат. Там будет раздел “Поворот объемной фигуры”. Вращение вокруг осей X/Y ставим на 0%.

Воронка готова. Осталось ее визуально настроить под свои предпочтения.

Те же действия, но только в видео↓

#кейсы

​​​​​​Кейс №7. Добавление данных на диаграмму

Привет всем!
Помимо стандартного способа добавления данных в диаграмму через "Выбрать данные", есть ещё несколько очевидных и не очень вариантов.

1. Протянуть область выделения

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

2. Копировать - вставить

Выделяем новые данные и копируем через Ctrl + C. Встаем на таблицу и нажимаем Ctrl + V. Предельно просто.

Хитрость способа: если копируемые данные находятся в одном столбце с исходными данными, то система подставит данные в текущий ряд.
Если данные находятся в другом столбце, то система подставит их в новый ряд.

3. Изменить аргумент в функции РЯД

Не самый очевидный способ, но имеет место быть.

Выделив ряд данных в диаграмме, в строке формул увидим фунцию =РЯД(Имя; Подписи; Значения; Порядок)

Чтобы добавить новые данные, достаточно изменить аргумент “Значения”.

Вот такие 3 доп. способа есть вдобавок к стандартному (через "Выбрать данные").

Кейс в видео↓

#кейсы

​​Кейс №8. Диаграммы с отрицательными значениями

Бона сера, подписчицы и подписчики!
Кейс о том, как визуально выделить минусы среди плюсов. Или как выделять отрицательные значения на диаграммах.

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

Чтобы в таком случае улучшить диаграмму, достаточно изменить цвет отрицательных значений. Но только не вручную.
“Формат ряда данных” - “Заливка” - поставить флажок “Инверсия для чисел < 0” - появится второй ярлык заливки для минусов. Выбираем цвет, готово.

Второй случай: отрицательные значения находятся в другом столбце.

Если оба столбца добавить в диаграмму, то они окажутся в разных рядах и на разных уровнях. Чтобы сделать их друг напротив друга, также заходим в “Формат ряда данных” и ставим “Перекрытие рядов” на 100%.

Всего пару кликов, а читаемость диаграмм повышена в разы.

Кейс в видео↓

#кейсы

Буэнос ночес, амигос!

Прервем череду материалов по диаграммам. Сегодня поговорим о инструменте “Проверка данных”. Косвенно он также относится к визуализации.

Но основная его функция - организовать корректную работу пользователей в файлах Excel.

Welcome↓

Материал: 5.4 Проверка данных
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень5
#проверкаданных
#визуализация

Кейс №9. Проверка данных – фиксированные списки

Кейс вдогонку материалу о проверке данных.

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

Для этого достаточно в графе "Источник" перечислить все значения списка через точку с запятой.
Например: январь; февраль; март...

Плюс: не нужно выделять место под значения списка.
Минус: очевидно, что значения строго фиксированы.

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

Для тех, кому интересно обучение на втором потоке «Поискового отряда» - сегодня вечером дам ссылку на канал, где расскажу о мероприятии, вышлю бонусы и отвечу на все вопросы.

До встречи!

#кейсы

Хола!

В прошлый раз немного прошлись по проверке данных. Сегодня же разбираем тему именованных диапазонов.

Именованные диапазоны в совокупности с проверкой данных дают неплохие решения) Об этом будет следующий материал.

А пока узнайте как быстро давать имена диапазонам ячеек↓

Материал: 5.5 Именованные диапазоны
Если не открывается, используйте запасную ссылку
Сложность: 3/10
Связь: @excelstudybot

#уровень5
#именованныедиапазоны