Как быстро ориентироваться в перечне листов файла 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 со страницы по ссылке 👈

Если создать сводную таблицу в Excel и поместить любой элемент с числами в область "Значения", то мы получим заголовок "Сумма по полю…" или "Количество по полю…"

А если убрать лишние слова "Сумма по полю" и оставить заголовок элемента, то Excel выдаст ошибку ⛔️

Как быть? 🤷‍♂️

Как правильно задать заголовок? 🧐

Заголовок столбца сводной таблицы не может иметь схожее название с элементом сводной таблицы! 💡

Обойти это просто. Добавьте «пробел» в конце заголовка 😉

Готово ✅

В Excel есть два популярных
способа ввода функций:

◾️ Через диалоговое окно "Вставка функции"
◾️ Вручную

1️⃣ Через окно "ВСТАВКА ФУНКЦИИ"

Вызвать окно вставки функции можно несколькими способами:

◾️ Кликнуть левой клавишей мыши по значку ƒx слева от строки формул
◾️ Нажать комбинацию клавиш SHIFT + F3
◾️ Найти и выбрать функцию в "Библиотеке функций" на вкладке "Формулы"

В окне "Вставка функции" найдите нужную функцию:

◾️ В поле Поиск функции укажите название и нажмите Найти
◾️ Выберите функцию в окне ниже

Если функцию найти не удалось, смените категорию на "Полный алфавитный перечень".

В окне "Аргументы функции" задайте аргументы и щелкните "ОК".

2️⃣ РУЧНОЙ ВВОД ФУНКЦИИ

Просто начните вводить в ячейке название функции и Excel подскажет вам:

◾️ Название функции. Для выбора функции просто нажмите клавишу TAB.
◾️ Аргументы функции. Полужирным указан текущий аргумент ввода.

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

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

Гистограмма условного форматирования заполняет ячейки цветом на основе диапазона данных выделенных ячеек.

Ячейка с наибольшим значениям будет заполнена полностью (100%), а остальные значения будут закрашены в пропорции от максимального.

Применить гистограмму условного форматирования просто:

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

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

Этот эксельхак особенно пригодится при использовании функций СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН.

Для быстрого копирования аргументов:

◾ В режиме написания формулы кликните левой клавишей мыши по названию аргумента функции в подсказке
◾ Excel выделит аргумент функции
◾ Нажмите сочетание клавиш CTRL + C
◾ Кликните в подсказке по названию аргумента функции в которую вы хотите вставить скопированные значения
◾ Нажмите сочетание клавиш CTRL + V для вставки

Готово 😉

Теперь, не нужно выделять левой клавишей мыши диапазон аргумента для копирования.

Что такое отклонение в процентах?

Это разница между двумя числовыми значениями в процентах.

Например, план продаж 10 000 руб., а факт 14 500 руб.

Разница между фактом и планом 4 500 руб.

4 500 руб. от плана это 45%.

🔻Так будет выглядеть формула в Excel:

=(факт-план)/план

⛔️Но, что, если значение Плана будет отрицательным?

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

Избежать это можно, добавив в формулу функцию ABS().

Функция ABS() возвращает модуль (абсолютную величину) числа.

🔻Такой будет формула с функцией ABS():

=(факт-план)/ABS(план)

Рекомендую использовать ABS() всегда для расчётов процентного отклонения.

Каждый пользователь Excel должен знать как устроена работа с датой и временем в программе.

Собрал для вас краткий перечень основных особенностей работы с датами и временем в Excel.

🔻 ДАТА

Excel определяет дату как порядковое число.

Число 1 это 1 января 1900 года.

Проведите эксперимент: напишите в ячейке дату 28.05.2021 и преобразуйте её в числовой формат. Excel автоматически воспримет 28.05.2021 как дату и сохранит число 44344 как её порядковый номер.

Даты до 1 Января 1900 года Excel воспримет как текст.

Максимальное значение даты в Excel: 31 Декабря 9999 года (числовое значение 2 958 465).

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

🔻 ВРЕМЯ

Время в Excel фиксируется как дробная часть дня. Например, 44344,5 это 12:00 28 Мая 2021 года

Расчет порядкового числа одного часа:

= 1 / 24

Минуты:

= 1 / (24 * 60)

Секунды:

= 1 / (24 * 60 * 60)

Максимальное значение времени без даты: 9999:59:59 (почти 10 тысяч часов)

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

💡 Используйте срезы!

Как их применить к вашей таблице - смотрите этом в коротком видеоуроке.

Если у вас есть список ФИО и для него нужно определить пол, то у меня для вас есть эксельхак!

Этот способ подойдет для определения пола по ФИО большинства жителей стран СНГ.

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

🔻 Формула для определения пола по ФИО:

=ЕСЛИ(ПРАВСИМВ(A2)="а";"Женский";"Мужской")

Разберем в деталях 👇

📌 Функция ЕСЛИ проверяет последнюю букву отчества. Если буква А, то выдаст результат Женский, в противном случае Мужской

📌 Функция ПРАВСИМВ извлекает последний символ ячейки.

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

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

В этом нам поможет функция ПРЕОБР (англ. CONVERT).

Эта функция состоит из трёх аргументов:

◾ Число - ссылка на ячейку со значением для преобразования
◾ Исх_ед_изм - единица измерения аргумента "Число"
◾ Кон_ед_изм - единица измерения для результата преобразования

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