Друзья! Поздравляем всех вас с Наступающим Новым Годом! 🎊 Желаем успехов в делах и достижении новых высот! 🌟

А в качестве подарка небольшой новогодний урок. Учимся рисовать в Excel симпатичную ёлку с гирлядной (спасибо за идею нашему подписчику Сергею)

Хороших праздников и выходных! До встречи в Новом Сезоне 2️⃣0️⃣2️⃣0️⃣!

Фильтровать таблицы можно только по цвету заливки ячеек и цвету шрифта. А вот по начертанию - нельзя. Не получится задать такой фильтр, который будет отображать только ячейки, выделенные жирным шрифтом.

Но можно применить небольшой трюк. С помощью команды "Найти и заменить" все жирные ячейки в столбце заменяются на жирные с определенной заливкой. И уже после этого применяется фильтр по данной заливке. Простой и эффективный приём.
#УР1 #Фильтрация_и_сортировка

Сортировка позволяет учитывать регистр букв. Слова, начинающиеся со строчных букв, будут расположены выше, чем те же слова с прописных. Включается в параметрах сортировки
#УР1 #Фильтрация_и_сортировка

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

А можно даже включить в сводную несколько промежуточных итогов. Например, считать сумму, количество и среднее по каждой категории (для выбора нескольких расчётов достаточно зажать Ctrl в окне "Параметры поля").

#УР2 #Сводные_таблицы

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

Решается очень просто. Сначала считаем для каждого товара минимальную цену стандартной функцией МИН. Для наглядности добавляем условное форматирование.

А потом подсчитываем, сколько раз у каждого поставщика цена на товар совпала с минимальной ценой на этот товар. Тут уже выручит простенькая формула на основе СУММПРОИЗВ и сравнения диапазонов.

#УР2 #Примеры_формул

Функция МИН при подсчетах замечательно умеет игнорировать текст, пустые ячейки и логические значения ИСТИНА и ЛОЖЬ. Но часто задача сводится к тому, чтобы подсчитать минимальное значение в диапазоне без учета нулевых значений.

В этом случае можно использовать функцию МИНЕСЛИ (если у вас Excel 2019 или Office 365) или же написать простенькую формулу массива, сочетающую функцию ЕСЛИ и обычную функцию МИН. Такая формула заменит все нулевые значения на ЛОЖЬ. А значение ЛОЖЬ функция МИН при подсчёте просто проигнорирует. Разумеется, всё это справедливо и для функции МАКС.

#УР2 #Применение_встроенных_функций

При поиске минимального значения по условию с помощью функции МИНЕСЛИ или формулы массива МИН(ЕСЛИ…)) есть один тонкий момент. Если условие не будет выполнено ни разу, то формула будет возвращать 0. И будет невозможно понять, что это за ноль - это реально минимальное значение по условию или же условие не выполнено ни разу.

Чтобы при невыполнении условия получать вместо нуля значение ошибки - используйте НАИМЕНЬШИЙ вместо МИН. Полученную ошибку можно потом преобразовать во что-то более подходящее функцией ЕСЛИОШИБКА.

#УР2 #Применение_встроенных_функций

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

Это сработает, даже если в ячейке уже что-то есть. Достаточно добавить к обычному формату ячейки символ "звездочка" и после него указать символ-заполнитель.
#УР3 #Пользовательские_форматы

Вопросы про создание простых выпадающих (раскрывающихся) списков всё чаще попадаются в нашем боте. На самом деле всё очень просто. Чаще всего для такой задачи используется инструмент "Проверка данных".

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

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

#УР2 #Проверка_данных

Часто работаете с одними и теми же файлами? Сложите их в одну папку и укажите ее как каталог автооткрытия. Excel при каждом запуске будет автоматически открывать все файлы из такой папки
#Справка

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

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

#УР2 #Сводные_таблицы

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

Это может пригодиться, например, при расчете процента выполнения плана по прибыли. Диапазон значений - от 0% до 100%. Перевыполнение - всё равно 100%. Убыток - всё равно 0%.
#УР2 #Примеры_формул

У нас на канале уже был урок про то, как получить из даты название месяца. Но что, если имеется только номер месяца (от 1 до 12)? И нужно по этому номеру получить название.

Вариантов решения есть множество. ВПР с небольшой справочной табличкой, функция ВЫБОР и т.д. Показываем пару вариантов на основе функции ТЕКСТ. Вся суть в том, чтобы из номера месяца получить любую дату этого месяца. А уже из нее ТЕКСТ легко извлечет название.

#УР2 #Примеры_формул

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

1) Всё та же функция ТЕКСТ. Обратите внимание, что текстовый аргумент вида "1."&НомерМесяца функция вполне легко воспринимает как дату.

2) Функция ВЫБОР. Удобно, когда нужно каждому номеру месяца назначить не совсем привычное название. Спойлер: в следующем уроке покажем, как ускорить ввод аргументов в функцию.

3) Функция ВПР. Классический и очевидный вариант. Плюсы те же, что и у ВЫБОР. А если вспомогательная табличка мешает, её легко можно вычислить прямо в формуле в виде массива (поможет клавиша F9)

#УР2 #Примеры_формул

В прошлом посте мы вручную вводили названия месяцев в качестве отдельных аргументов функции ВЫБОР. Это можно было сделать быстрее.

Если аргументы для ввода в ВЫБОР - это значения ячеек на листе, то можно использовать приём с клавишей F9. Указываем в качестве второго аргумента функции нужный диапазон, затем вычисляем его в формуле и убираем фигурные скобки в начале и в конце. Имейте в виду, что если на листе столбец - добавляем ТРАНСП, если строка - делаем простую ссылку. Это нужно для получения правильного разделителя между значениями.

Ну и разумеется, для такой задачи, как показана в примере, можно просто использовать ИНДЕКС вместо ВЫБОР. Результат будет тот же, а приём с переносом значений в формулу через F9 будет еще проще (не важно, в строке данные или в столбце, и не нужно удалять фигурные скобки).

#УР2 #Примеры_формул

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

В некоторых случаях помогает выделение целиком нужных строк и отключении опции "Переносить текст" на Главной. А в некоторых можно использовать другой приём.

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

#УР2 #Сводные_таблицы

Если у Вас есть ячейка,оформленная как Вам нравится,то вместо постоянного копирования формата просто сохраните такое оформление как пользовательский стиль и применяйте в 1 клик
#УР1 #Оформление_таблиц

Популярный инструмент "Текст по столбцам" предоставляет много интересных и полезных возможностей. Одна из них - пропуск указанных столбцов при формировании итоговой таблицы.

Запускаете инструмент, указываете разделитель, а на последнем шаге - выделяете те столбцы, которые не должны остаться после разбора, и включаете для них опцию "Пропустить столбец". В итоге после нажатия "Готово" из всех "слипшихся" данных останутся только те столбцы, которые не были отмечены как ненужные. Остальные будут удалены.

#УР1 #Обработка_таблиц

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

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

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

#УР3 #Диаграммы

Инструмент "Мгновенное заполнение" в Excel есть уже достаточно давно (с 2013 версии). И спектр задач, которые он может помочь решить, достаточно широк. Вот одна из них.

Есть список сотрудников в формате ФИО. Нужно изменить порядок так, чтобы фамилия была в конце. Задача несложная. Можно разбить данные на столбцы с помощью "Текст по столбцам", а потом пересобрать формулой в нужном порядке. Но с мгновенным заполнением это делается ещё быстрее. Вводим пару примеров результата - и Excel угадывает, что мы пытаемся сделать.

#УР1 #Обработка_таблиц