Если создать сводную таблицу в 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).

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

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

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

Есть ДВА способа вычисления номера недели в году Excel:

▪️Первая неделя та, в которой находится первый день года

▪️Первая неделя та, в которой есть первый четверг года

Мало кто знает, но в РФ используется второй способ 😉

Рассмотрим подробней:

1️⃣Первая неделя та, в которой находится первый день года.

Для расчета номера недели по этому принципу используйте функцию

=НОМНЕДЕЛИ()

В качестве аргумента сошлитесь на ячейку с датой или пропишите функцию СЕГОДНЯ() для определения номера недели текущего дня.

2️⃣Первая неделя та, в которой есть первый четверг года.

Способ установлен актами Международной Организации по Стандартизации. Им пользуется более 150 стран.

В России этот стандарт используется с 2002 года.

Для расчета номера недели по этому принципу используйте функцию

=НОМНЕДЕЛИ.ISO()

❗️Обратите внимание, что в 2021-м году первый четверг выпал на неделю с 4 по 10 Января.

Поэтому 1 Января 2021 года это 53-я неделя 2020 года по ISO.

СТРОКА ФОРМУЛ КАК КАЛЬКУЛЯТОР В EXCEL

Выполнить вычисление в Excel как на калькуляторе и ввести в ячейку только результат этого вычисления просто!

Например, введите в ячейке следующую формулу:

🔻 =(1500*1,34)/3

Не торопитесь нажимать клавишу ENTER!

Нажмите клавишу F9 (на компактных клавиатурах Fn+F9),
а затем нажмите Enter.

Excel оставит в ячейке результат вычисления,
а не саму формулу 😉

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

◾️ Условное форматирование
◾️ Преобразовать данные в таблицу
◾️ Посчитать итоги, среднее, количество
◾️ Вывести процент значений от итога
◾️ Отразить нарастающий итог
◾️ Построить диаграмму и спарклайны

Быстрый анализ появился в Excel начиная с 2013 версии.

Активировать его можно выделив левой клавишей мыши диапазон таблицы, а также с помощью горячих клавиш CTRL + Q.

В Excel есть три способа для быстрого перемещения таблицы как внутри листа, так и на другой.

1️⃣ Перемещение таблицы внутри листа

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

2️⃣ Перемещение таблицы на другой лист

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

3️⃣ Создание копии таблицы на другом листе книги Excel

Выполняем все те же действия, что и при обычном перемещении + зажимаем клавиши Alt и CTRL.

P.S. Для переноса таблиц также можно использовать горячие клавиши:

◾ CTRL+C (копировать)
◾ CTRL+V (вставить)
◾ CTRL+X (вырезать)

Создать копию листа Excel можно за одну секунду!

☝️

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

Есть простой способ избежать сдвига ссылок.

Для этого:

1️⃣ Выделим диапазон ячеек для копирования
2️⃣ Нажмем сочетание клавиш "CTRL + H".
3️⃣ Перед нами откроется окно "Найти и Заменить". В поле "Найти" мы укажем знак равно. В поле "Заменить на" важно указать любой символ, не участвующий в формулах в ваших ячейках, например ! или #.
4️⃣ Нажмем ОК.

Так, мы на время деактивировали наши формулы заменив знак равно на восклицательный.

5️⃣ Теперь, скопируем и вставим ячейки нашего диапазона в другую область листа.
6️⃣ Выделим левой клавишей мыши диапазон ячеек с замененным знаком равно.
7️⃣ Снова вызовем инструмент поиска и замены с помощью горячих клавиш "CTRL + H". И теперь заменим восклицательный знак(!) на знак равно (=), чтобы вновь активировать наши формулы.
✅ Готово. Формулы успешно скопированы без сдвига ссылок на ячейки.

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

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

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

Ячейки с формулами на листе теперь будут отражать не результат формул, а сами формулы.

Этот инструмент также легко активировать с помощью горячих клавиш CTRL+Ё.

Итак, выделим диапазон ячеек для копирования. Скопируем его с помощью клавиш CTRL+C

Откроем программу «Блокнот» на компьютере и вставим в него наши данные с помощью клавиш CTRL+V.

Теперь, уже в блокноте снова выделим и скопируем формулы и вставим в нашу книгу Excel на новый лист или в новое место существующего листа.

С помощью клавиш CTRL+Ё отключим режим просмотра формул.

✅ Готово. Ячейки с формулами были успешно скопированы без сдвига.