Иногда нам достаются списки с данными где внутри ячейки указаны порядковые номера и, например, ФИО или любые другие значения. Чаще всего нам приходится "очищать" такие списки вручную.

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

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

1️⃣ Выделите левой клавишей мыши диапазон ячеек с ФИО
2️⃣ Нажмите сочетание клавиш CTRL + F для вызова меню "Найти и заменить"
3️⃣ Перейдите во вкладку "Заменить"
4️⃣ В поле "Найти" введите значение *.
5️⃣ Поле "Заменить на" оставьте пустым
6️⃣ Нажмите кнопку "Заменить все"

✅ Готово!

Наш список содержит только ФИО.

Подстановочный знак * обозначает любое количество символов.

Так, мы задаем настройку поиска любых значений в диапазоне с точкой на конце и меняем это значение на пустоту.

Поделитесь, пожалуйста, в комментариях был ли для вас полезен этот #эксельхак? 👇

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

Для поиска последнего числового значения в строке нам поможет функция ПРОСМОТР.

Функция ПРОСМОТР ищет слева направо искомое значение в заданном диапазоне ячеек.

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

Задайте функции ПРОСМОТР в качестве искомого значения ОЧЕНЬ большое число, которого нет в диапазоне просматриваемых ячеек. Например, 9999999.

Формула в нашем примере для ячейки F3 будет выглядеть так:

=ПРОСМОТР(9999999;B3:E3)

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

Для быстрого сброса формата ячеек в Excel выполните несколько простых действий:

1️⃣ Выделите левой клавишей мыши диапазон ячеек для очистки формата
2️⃣ На вкладке "Главная" кликните по пункту "Очистить"
3️⃣ В выпадающем меню выберите "Очистить форматы"

✅ Готово! Формат таблицы сброшен!

Сократите количество кликов для сброса формата ячеек:

1️⃣ Кликните правой клавишей мыши по пункту "Очистить форматы"
2️⃣ Выберите "Добавить на панель быстрого доступа"

✅ Кнопка сброса формата теперь на панели быстрого доступа.
Сбрасывайте форматы ячеек в один клик!

Очень простой, но важный совет!

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

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

Для этого я рекомендую использовать функцию ДАТАЗНАЧ (DATEVALUE на англ.). В качестве аргумента укажите ссылку на ячейку с текстовой датой.

Если в ваших таблицах множество БОЛЬШИХ чисел, то для наглядности их можно привести в формат "тысяч".

Например, для преобразования числа 137 500 р. в 137,5 тыс. руб выполните следующие действия:

1️⃣ Выделите левой клавишей мыши ячейку для преобразования
2️⃣ Нажмите сочетание клавиш "CTRL + 1" для вызова меню "Формат ячеек"
3️⃣ На вкладке "Число" перейдите в пункт "Все форматы"
4️⃣ В поле Тип пропишите формат:

0,0 " тыс. руб."

5️⃣ Нажмите "ОК"

✅ Готово!

P.S. Если вы хотите применить формат в миллионах рублей, то добавьте дополнительный пробел после числа ноль и замените "тыс." на "млн."

Формат для миллионов рублей выглядит так:

0,0 " млн. руб."

А вы знаете что такое "Срезы" в Excel? 🧐

"Срез" – это кнопки для удобной фильтрации таблиц в Excel.

Применить "Срезы" вы можете только к данным, отформатированным как таблица, а также к сводным таблицам в Excel.

Добавьте "Срезы" к списку с данными выполнив следующие действия:

1️⃣ Кликните левой клавишей мыши по списку с данными
2️⃣ На вкладке "Главная" на панели инструментов кликните по пункту "Форматировать как таблицу"
3️⃣ В окне "Форматирование таблицы" поставьте галочку если ваш список содержит заголовки и нажмите "ОК"

✅ Готово!

Данные отформатированы как таблица. Теперь, мы можем активировать "Срезы".

4️⃣ Кликните левой клавишей мыши по любой ячейке таблицы
5️⃣ Перейдите на вкладку "Конструктор таблиц" на панели инструментов
6️⃣ Кликните по пункту "Вставить срез"
7️⃣ В меню "Вставка срезов" отметьте те столбцы вашей таблицы по которым вы хотите создать "Срезы"
8️⃣ Нажмите "ОК"

✅ Готово!

Теперь, фильтровать данные в таблице быстро, наглядно и удобно.

Очень часто получаю вопрос: "Почему у меня и строки и столбцы отмечены цифрами на листе Excel?"

Так происходит когда у вас активирован стиль ссылок R1C1.

В Excel два стиля ссылок А1 и R1C1.

A1 - классический стиль, где буква это номер столбца листа Excel, а число – номер строки.

R1C1 - стиль ссылок Excel где номера строки (R) и столбца (C) указываются числами и указывают координаты относительно активной ячейки.

Если в ячейку из первого столбца и первой строки ввести ссылку на ячейку R[1]C[2] то эта ссылка означает, что она сошлется на ячейку, которая находится на 1 строку ниже активной ячейки и на два столбца правей.

✅ Как отключить стиль ссылки R1C1

1️⃣ Нажмите "Файл" => "Параметры"
2️⃣ В окне "Параметры Excel" на вкладке "Формулы" уберите галочку "Стиль ссылок R1C1"
3️⃣ Нажмите "ОК"

✅ Готово!

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

Но, есть #эксельхак 😉

Если у вас версия Excel 2013 и выше, то для подсчета уникальных значений выполните следующие действия:

1️⃣ Кликните левой клавишей мыши по любой ячейке списка с данными
2️⃣ Перейдите на вкладку "Вставка" и кликните по пункту "Сводная таблица"
3️⃣ В окне "Создание сводной таблицы" поставьте галочку в пункте "Добавить эти данные в модель данных"
4️⃣ Нажмите "ОК"
5️⃣ Поместите в область "Значения" сводной таблицы элемент по которому вы хотите получить расчет уникальных значений
6️⃣ Кликните по стрелочке элемента в области "Значения"
7️⃣ Нажмите "Параметры полей значений"
8️⃣ В окне "Параметры поля значений" на вкладке "Операция" выберите пункт "Число разных элементов"
9️⃣ Нажмите "ОК"

✅ Готово!

В вашей таблице будут отражено количество уникальных значений.

Как быстро ориентироваться в перечне листов файла Excel? Выделить их цветом!

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

Для этого:

1️⃣ Кликните правой клавишей мыши по ярлыку листа Excel
2️⃣ Кликните по пункту Цвет ярлычка
3️⃣ Выберите подходящий цвет
✅ Готово!

Поздравляю вас с Днем смеха! 🤣

Как пошутить в этот день над коллегой, который много работает с таблицами в Excel? 😅

1️⃣ На панели быстрого доступа кликните по иконке выпадающего списка и выберите "Другие команды"
2️⃣ В новом окне в поле "Выбрать команды из" выберите "Все команды"
3️⃣ В списке команд выберите "Проговаривать ячейки после ввода"
4️⃣ Кликните по кнопке "Добавить"
5️⃣ Нажмите "ОК"

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

Кликните по ней и после нажатия клавиши Enter в ячейке, Excel будет проговаривать голосом ее содержимое. 🤣

* Обязательно подскажите вашему коллеге, как отключить эту настройку!

Ведете табели учета рабочего времени? Или любые другие документы с датами, где в зависимости от дня недели нужно выделить ячейки особым форматом?

В нашем примере есть табель учета рабочего времени. Мы хотим автоматически выделять столбцы с выходными днями (суббота и воскресенье) особым форматом.

Сделать это мы можем с помощью настроек Условного форматирования Excel.

Выполним следующие действия:

1️⃣ Выделите левой клавишей мыши диапазон ячеек к которым мы хотим применить форматирование
2️⃣ На вкладке Главная панели инструментов кликните по пункту Условное форматирование
3️⃣ Выберите в меню Создать правило
4️⃣ В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек
5️⃣ В окне Форматировать значения, для которых следующая формула является истинной введем формулу

=ИЛИ(ДЕНЬНЕД(B$2)=7;ДЕНЬНЕД(B$2)=1)

◾️ Функция ИЛИ проверяет с помощью функции ДЕНЬНЕД соответствует ли ячейка с датой в табеле субботе (7) или воскресенью (1)
◾️ Числа 7 и 1 по умолчанию в Excel обозначают порядковые номера Субботы и Воскресенья
◾️ Знак $ в ссылке на ячейку B$2 позволяет применить проверку ячеек с датой во второй строке каждого столбца, так как фиксирует номер строки при протягивании формулы
6️⃣ Кликните по кнопке Формат для настройки заливки и шрифта ячеек, соответствующих условиям форматирования
7️⃣ Щелкните ОК

✅ Готово!

Столбцы для субботы и воскресенья в табеле выделены отдельным цветом.

Многие знают как найти максимальное и минимальное значение в Excel. Для этих задач используют функции МАКС() или МИН() (англ. MAX() и MIN()).

Но что если вам нужно найти 2-е по счёту наименьшее значение?

Для такой задачи вам поможет функция НАИМЕНЬШИЙ(массив;k) (англ. SMALL())

📌 Аргументы функции

◾️ массив – диапазон ячеек, среди которых мы хотим найти наименьшее значение
◾️ k – позиция от наименьшего значения

Для нашей задачи составим формулу:

=НАИМЕНЬШИЙ(B2:B11;2)

Где B2:B11 - диапазон в котором мы ищем минимальное значение.
2 - второе наименьшее значение в диапазоне.

✅Готово

В этом видеоуроке я расскажу о том, как ранжировать данные в Excel.

Для расчета ранга в Excel используют функции РАНГ, РАНГ.РВ, РАНГ.СР.

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

Одна из самых популярных функций в Excel это ВПР (англ. VLOOKUP).

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

Функция состоит из 4-х аргументов:

◾️ Искомое_значение – значение или ссылка на ячейку, которую мы ищем;

◾️ Таблица – диапазон ячеек, в котором мы ищем искомое значение. Первый столбец таблицы должен состоять из искомых значений;

◾️ Номер_столбца – номер столбца таблицы с данными для подстановки;

◾️ [интервальный_просмотр] – точность совпадения при поиске. 0 – точное, 1 – приближенное


📌 Ключевые принципы работы функции ВПР

◾️ Поиск данных сверху вниз
Функция осуществляет поиск искомого значения в таблице сверху вниз
◾️ При точном совпадении искомого значения останавливает поиск

Поиск останавливается как только функция находит первое точное совпадение искомого значения в таблице

В работе с ВПР существует масса нюансов. Её мощь и эффективность раскрывается еще больше при комбинации с другими функциями.

Подготовили для вас шпаргалку с ТОП 50 самыми необходимыми горячими клавишами в Excel.

Скачивайте файл выше или отправьте его к себе на Email со страницы по ссылке 👈