С помощью функций СЧЕТЕСЛИ и СРЗНАЧ можно легко подсчитать количество значений в диапазоне, которые превышают среднее значение. Главное - правильно записать условие сравнения: взять знак ">" в кавычки и не забыть символ склеивания текста - &.

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

👉 @Excel_lifehack

​Настраиваемая сортировка

Параметр учитывает уровни и диапазоны данных — это позволяет более гибко выстроить расположение элементов в таблице. Для ее создания используется специальное меню:

1. Выделите всю таблицу.
2. В разделе «Редактирование» выбирайте пункт «Настраиваемая сортировка».
3. В выпадающем меню «Сортировать по» укажите столбец, который затрагивает сортировка.
4. Кликните на режим сортировки «Значения ячеек».
5. Основная настройка — сортировка от А до Я. Но в меню можно настроить более детально.

👉 @Excel_lifehack

Сегодня показываем небольшую формулу, которая позволяет подсчитать сумму цифр какого-то целого числа. Например, для числа 549 этой суммой будет 5+4+9 = 18.

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

👉 @Excel_lifehack

​Добавление нулей перед числами

Нужно срочно подставить цифры под необходимый формат — проставить нули. А Excel сопротивляется и автоматически меняет все вводимые нами числа. Давайте разбираться в проблеме:

1. Поменять формат. Выделите ячейки, которые нужно изменить, и во вкладке «Главная» найдите «Ячейки». В выпавшем меню кликните на «Формат ячеек» и выберите «Текстовый».

2. Создать свой формат. Повторите действия из первого пункта. Только вместо формата «Текстовый» выберите «Все форматы». В окне назначьте свой тип, например 000 0000.

3. «По-быстрому». Выделите одну ячейку и перед числовым значением поставьте «'». После введите нужное количество нулей и нажмите на «Enter».

👉 @Excel_lifehack

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

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

👉 @Excel_lifehack

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

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

👉 @Excel_lifehack

​Быстрый переход к нужному листу

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

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

👉 @Excel_lifehack

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

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

👉 @Excel_lifehack

Отображение формул текстом

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

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

В результате все формулы на активном листе будут визуально отображены как текст, но это никак не повлияет на их работу.

👉 @Excel_lifehack

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

👉 @Excel_lifehack

​Быстрое возведение числа в степень

Чтобы вознести число в степень, существует одноимённая функция =СТЕПЕНЬ(число; степень).

Однако это можно сделать ещё более простым способом: указать знак ^ в качестве математического действия (например, =3^2).

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

👉 @Excel_lifehack

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

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

👉 @Excel_lifehack

Создание линейчатой диаграммы

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

Первый этап — создание. В шапке программы найдите «Вставка», а после жмите на «Диаграммы» и выберите «Гистограммы».

Второй этап — редактирование. Выберите тип гистограммы: 3D, процентный или нормированная. После выберите «Формат данных» и фигуру.

Третий этап — детализация. В окне «Формат данных» настройте расстояние фронтального и бокового зазора, формат фона и добавьте подписи.

👉 @Excel_lifehack

Способы вставки таблицы

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

— Способ 1

Выделите область данных → зажмите «CTRL+T» → подтвердите диапазон значений и нажмите «Ок».

— Способ 2

Для тех, кому нужно перенести таблицу из Word: выделяем область значений → кликаем правой кнопкой мыши и жмём «Копировать» → возвращаемся в Excel и кликаем правой кнопкой мыши на любую ячейку → выбираем параметр вставки «Сохранить исходное форматирование».

👉 @Excel_lifehack

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

👉 @Excel_lifehack

Для поиска слова в каком-то тексте использую ПОИСК() и НАЙТИ(). Но они отличаются. ПОИСК() не чувствителен к регистру и позволяет использовать подстановочные знаки

👉 @Excel_lifehack

Если по какой-то причине имеющаяся "умная" таблица Вам больше не нужна (как объект), а вот информацию в ней надо оставить - используйте команду "Преобразовать в диапазон" на контекстной вкладке "Конструктор". Она сохранит всё форматирование и все данные, но таблица больше не будет специальным объектом, а станет обычным диапазоном ячеек.

Если же форматирование тоже не нужно, можно предварительно применить стиль без оформления, а потом - указанную выше команду.

👉 @Excel_lifehack

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

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

👉 @Excel_lifehack

Как скрыть лист знают практически все. А как скрыть лист так, чтобы его не смог найти простой юзер? Меняем одну настройку в редакторе VBE и секретная информация будет скрыта более надежно.

👉 @Excel_lifehack

​Одновременный ввод одинаковой информации в диапазон ячеек

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

— Шаг 1
Выделите ячейки, в которые необходимо ввести одинаковую информацию.

— Шаг 2
Введите данные в активную ячейку.

— Шаг 3
Завершите ввод, нажав CTRL + ENTER.

👉 @Excel_lifehack

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

👉 @Excel_lifehack