​​Невидимые ячейки

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

1. Выделяете ячейку со значением, которое хотите скрыть.
2. ПКМ по ячейке. Формат ячеек – Число – (все форматы).
3. В поле Тип указываете 3 точки с запятой - ;;;
4. Нажмите ОК.

Видите значение, нет? А оно есть.
И даже можно использовать в формулах. Это решение поинтереснее, чем красить текст в белый цвет 😊

Видео⬇️

Подписывайтесь на Hello Excel
#фичанедели

​​📉МИН и МАКС в массивах📈

Продолжаем цикл постов по формулам массивов.
Сегодня применим их с функциями МИН и МАКС. Вы можете проводить математические операции с массивами данных и «поверх» использовать МИН / МАКС.

Кейс №1. Разница

Перед вами цены товаров на 1-ый месяц и на 2-ой месяц после применения праздничной скидки. Суммы разные. Вы хотите понять максимальный и минимальный размер скидки во 2-ом месяце.

1. Встаем в одну ячейку, прописываем формулу поиска минимума, указывая адреса одного и второго диапазона ячеек с ценами, =МИН(B2:B7-C2:C7)
2. Нажимаете Ctrl + Shift + Enter.
3. Для нахождения максимума в соседней ячейки вписываете: =МАКС(B2:B7-C2:C7)
4. Нажимаете Ctrl + Shift + Enter.

Excel поочередно вычитает из одну цену из другой. Далее ищет минимум/максимус.

Кейс №2. Находим МАКС/МИН по условию

А сейчас заставим трепетать кадровиков, которые пишут в вакансиях требуется знание ВПР 😅
Сделаем аналог ВПР: будем искать максимальную цену среди цен товара Кресло К44.

— В А10 находится искомое значение - Кресло К44.
— В A2:A7 – наименования товаров.
— В B2:B7 – цены товаров.

Поехали:

1. Встаем в ячейку, где планируем получить результат поиска.
2. Прописываем формулу =МАКС(ЕСЛИ(A10=A2:A7;B2:B7)).
3. Нажимаете Ctrl + Shift + Enter.

Мы фактически сказали системе, если где-то в диапазоне A2:A7 будет Кресло К44, то верни его значение. И она возвращает несколько значений. Какая молодец!
А затем среди них находит максимум.

Формулы массивов – это весьма интересно и даже красиво. Видео⬇️

Подписывайтесь на Hello Excel.

​​⚡️Молниеносная фильтрация⚡️

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

Вместо этого предлагаю молниеносный способ фильтрации по найденному значению:

1. ПКМ по ячейке.
2. Фильтр – Фильтр по значению выделенной ячейке.

Готово. У вас стоит автофильтр с выбранным значением.

Подписывайтесь на Hello Excel
#фичанедели

​​Константы в Константинополе

Итак, мы уже повертели формулы массивов, знаем как они работают. Теперь обсудим одну, казалось бы, бесполезную тему (однако нет).
В Excel можно создавать массивы констант.

Теория

Что такое константа? Это постоянное значение, которое невозможно изменить. Как год вашего рождения.

Допустим вы можете забить в «память» Excel массив в виде строки со значениями 1 2 3.
Чтобы это сделать, вам нужно:

1. Выделить 3 ячейки в длину.
2. Прописать формулу = {1;2;3}.
3. Нажать Ctrl + Shift + Enter.

Захотели сделать вертикальный массив? Не вопрос:

1. Выделяйте 3 ячейки в высоту.
2. Пропишите формулу = {1:2:3}.
3. Нажмите Ctrl + Shift + Enter.

Обратите внимание: когда делаете горизонтальный одномерный массив (строку) в качестве разделителя используйте точку с запятой (;). Когда вертикальный массив – двоеточие (:).

Хотите сделать двумерный массив 2х2? Пойдем, покажу:

1. Выделяйте 4 ячейки: 2 столбца на 2 строки.
2. Пропишите формулу = {1;2:3;4}.
3. Нажать Ctrl + Shift + Enter.

Вы говорите системе: запиши 1 и 2 друг за другом одной строкой, а затем перейдите на следующую строку и запиши 3 и 4.

Так, теорию посмотрели, прекрасно. Теперь двигаем к живым примерам.

Кейс №1

Массивам констант, как и любым диапазонам можно давать имена.

1. Перейдите на вкладку ленты Формулы – Диспетчер имен – Создать.
2. В поле Имя назовите ваш массив.
3. В поле Диапазон указывайте массив констант со всеми правилами из блока выше: фигурные скобки, разделители. Нажмите ОК.

Далее используйте массив констант по имени в формулах. Если назвали массив Мебель, то прямо так и пишите в формулах, Excel покажет его в списке.

Кейс №2

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

НО. Писать вручную массив констант на сотни строк – себя не уважать. Как сделать проще?

1. В свободной ссылке сделайте ссылку на диапазон значений, который хотите спрятать: =А2:В240. Не уходите с ячейки.
2. Встаньте в строку ввода формулы и нажмите F9 (отладка формул). Вместо формулы в строке ввода покажется массив.
3. Выделяйте и копируйте его.
4. Затем создавайте имя на этот массив как описано выше. Сделали имя Мебель.
5. Удаляйте данные в А2:В240, они вам не нужны.
6. Используем ВПР: =ВПР(D2;Мебель;2;0). Данных нет, а ВПР работает, магия.

Надеюсь, что последний кейс вам понравился. Теперь вы понимаете как правильно использовать массивы констант. Видео⬇️

Подписывайтесь на Hello Excel