Excel Everyday

Иногда возникает необходимость выделить цветом те ячейки, из которых складывается результат формулы. Один из способов - применение окна "Выделить группу ячеек".

Сначала выделяем саму ячейку с формулой. Затем вызываем это окно (можно командой на ленте, а можно нажать F5 или CTRL+G, вызвав окно "Переход", а уже там выбрать "Выделить").

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

#УР1 #Простые_Вычисления

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

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

#УР1 #Работа_с_листами_книги

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Начиная с версии Excel 2013 данная задача решается очень просто. Надо лишь не забыть при создании сводной подгрузить таблицу в модель данных. Тогда в списке доступных вычислений появится "Число разных элементов".

А вот в версии 2010 и более старых - решение чуть сложнее. Можно, например, создать дополнительный столбец с формулой вида "=1/СЧЁТЕСЛИМН". В саму функцию задаются диапазон с товарами для подсчета и диапазон условия. Затем останется подсчитать сумму по этому столбцу в сводной таблице.

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

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

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

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

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

Начиная с Excel 2016 поля с датами в сводной таблице автоматически группируются при помещении их в строки или столбцы. Многим пользователям это показалось не слишком удобным. Поэтому уже в версии 2019 (и, соответственно, в Office 365) есть возможность легко отключить эту автогруппировку в "Параметрах" на вкладке "Данные".

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

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

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

После создания макроса не забудьте сохранить файл в формате Книга Excel с поддержкой макросов или Двоичная книга Excel.

#УР4 #Макросы

Настроек печати в Excel достаточно много. Если у вас есть лист, на котором всё уже настроено нужным образом, и надо задать те же настройки на других листах, то самый быстрый способ сделать это выглядит так:
1) Активируем лист, где всё настроено
2) Зажимаем CTRL и выделяем листы, на которые надо перенести настройки печати
3) Переходим на вкладку "Разметка страницы" и открываем окно "Параметры страницы"
4) Тут же закрываем его, нажав ОК
5) Разгруппировываем листы.

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

#УР1 #Печать_таблиц

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

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

#УР2 #Условное_форматирование

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

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

#УР2 #Условное_форматирование

При работе с фильтрами даты есть маленький нюанс. Если мы хотим найти только все даты 18 числа любого месяца любого года, то простой ввод числа 18 в строку поиска может не дать нужный результат. Например, если в датах встречается 2018-й год, то все даты этого года также будут включены в результат отбора.

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

#УР1 #Фильтрация_и_сортировка

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

Такой же трюк работает и для перемещения между листами. Если зажать клавишу ALT, то диапазон при перетягивании можно вырезать/переместить на другой лист. А если нужно скопировать, а не вырезать - зажимайте при перетаскивании CTRL+ALT.

#Горячие_клавиши

Размеры объектов в Excel очень удобно изменять используя некоторые полезные клавиши. Например, если зажать SHIFT и менять размер фигуры, перетягивая маркер в ее углу, то размер будет меняться, но соотношение сторон останется прежним. Это удобно, например, при рисовании круга или квадрата.

А если зажать клавишу ALT и менять размер фигуры, то можно подстраивать ее точно под границы ячеек, над которыми она расположена. Для изображений такой прием тоже работает, но сначала нужно будет отключить в настройках "Сохранение пропорций".

#Горячие_клавиши

Создать точную копию сводной таблицы, превратив её по пути в обычную, можно следующим сочетанием действий:

1) Копируем сводную таблицу целиком
2) Вставляем в новое место только значения
3) Не очищая буфер обмена, вставляем поверх значений форматы
4) Не очищая буфер обмена, вставляем поверх значений и форматов настройку ширины столбцов

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

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

Всем привет!
Сегодня 3 января, а значит настало время подвести итоги нашего Новогоднего розыгрыша!😉

Функция СЛУЧМЕЖДУ помогла нам в определении победителей из тех участников, которые зарегистрировались в нашей школе с момента старта розыгрыша. Итак, сертификаты получат:

serb???@gmail.com
ruslan???@gmail.com
art-g???@yandex.ru

Кто узнал свой email в списке выше - проверяйте почту. Именно на нее мы и отправим подарок.
Поздравляем победителей и желаем всем как следует отдохнуть в оставшиеся праздничные дни!👏

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

Желаем всем вам крепкого здоровья, удачи в делах и, конечно же, успехов в освоении нашего любимого Excel !! 🥂

До встречи в 2021 году! С уважением, tdots.ru

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

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

#УР2 #Работа_с_большими_табличными_массивами