Автозаполнение форматом

Немного необычный вариант — в Параметрах автозаполнения можно выбрать Заполнить только форматы. В таком случае на соседние ячейки будет скопирован только сам формат первой ячейки.

👉 @Excel_lifehack

Когда нужно отследить влияние исходной ячейки на несколько результирующих значений, расположенных на разных листах, пригодится функция "Окно контрольного значения"

👉 @Excel_lifehack

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

👉 @Excel_lifehack

​​Команда «Прогрессия» на вкладке Главная

Чтобы заполнить нужный список сразу до определенного конечного значения, удобно использовать команду Прогрессия на вкладке Главная — пункт Заполнить.
Здесь можно будет указать, по строкам или по столбцам нужно заполнить прогрессию, с каким шагом, какого типа (арифметическая или геометрическая), а также предельное значение прогрессии.

👉 @Excel_lifehack

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

👉 @Excel_lifehack

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

👉 @Excel_lifehack

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

👉 @Excel_lifehack

​​Диаграммы в Excel

Второй способ: Вкладка Вставка
Основные команды для вставки диаграммы находятся на вкладке Вставка. Среди них можно обратиться к команде Рекомендуемые диаграммы, либо сразу выбрать какой-то конкретный тип диаграммы (если вы точно уверены, что такой тип подойдет к вашим данным).

👉 @Excel_lifehack

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

👉 @Excel_lifehack

Полезные формулы для контроля финансов

— Функция ПЛТ
Одна из актуальнейших функций, с помощью которой можно рассчитать сумму платежа по кредиту с аннуитетными платежами, то есть когда кредит выплачивается равными частями.

ПЛТ(ставка;кпер;пс;бс;тип)

• Ставка - процентная ставка по ссуде;
• Кпер - общее число выплат по ссуде;
• Пс - приведённая к текущему моменту стоимость;
• Бс - требуемое значение будущей стоимости, или остатка средств после последней выплаты;
• Тип (необязательный аргумент) - число 0, если платить нужно в конце периода, или 1, если в начале периода.

— Функция СТАВКА
Вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости.

СТАВКА(кпер;плт;пс;бс;тип;прогноз)

• Кпер - число периодов платежей для ежегодного платежа;
• Плт - выплата, производимая в каждый период;
• Пс - приведённая (текущая) стоимость;
• Бс (необязательный аргумент) - значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты;
• Тип (необязательный аргумент) - число 0, если платить нужно в конце периода, или 1, если в начале периода;
• Прогноз (необязательный аргумент) - предполагаемая величина ставки.

— Функция ЭФФЕКТ
Возвращает эффективную годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты.

ЭФФЕКТ(нс;кпер)

• Нс - номинальная процентная ставка;
• Кпер - количество периодов в году, за которые начисляются сложные проценты.

👉 @Excel_lifehack

​​Диаграммы в Excel

Третий способ: Клавиша [F11]
Если при выделенном диапазоне ячеек нажать клавишу F11, то на новый лист автоматически будет вставлена диаграмма с типом по умолчанию (чаще всего, гистограмма с накоплением).

👉 @Excel_lifehack

Если нужно применить УФ набором значков вида "меньше ноля - ноль - больше ноля", то придется изменить настройки правила (по умолчанию, Excel применит значки по-своему)

👉 @Excel_lifehack

​Альтернативы примечаний

2) Аннотация к формуле с помощью функции "Ч"

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

Предположим, у нас есть формула, увеличивающая объем продаж 2019 года на 5%. Чтобы пояснить, откуда взялись эти 5%, можно использовать конструкцию, указанную на картинке.

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

👉 @Excel_lifehack

Альтернативы примечаний

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

1) Всплывающее сообщение с помощью блока "Проверка данных"

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

Затем в открывшемся окне перейдите на вкладку Подсказка по вводу, оставьте галочку Отображать подсказку, если ячейка является текущей и в поле Подсказка по вводу введите необходимое сообщение. Нажмите ОК, готово!

👉 @Excel_lifehack

В Excel нет специальной функции для вычисления номера квартала, к которому принадлежит та или иная дата. Но есть функция МЕСЯЦ(), которая позволяет извлекать номер месяца.

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

👉 @Excel_lifehack

Расчет нарастающего (накопительного) итога нужен довольно часто. В Excel его можно сделать с помощью одной лишь функции СУММ. Главная фишка - в правильном задании ссылки на диапазон.

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

Кстати, можно вставить эту же формулу с помощью Быстрого анализа (Excel 2013 и новее)

👉 @Excel_lifehack

​Как изменить регистр?

Чтобы преобразовать текст в ячейке в только прописной или только в строчный, используются функции =ПРОПИСН и =СТРОЧН соответственно.

Однако если вам нужно, чтобы первая буква была заглавной, а остальные — прописными, воспользуйтесь похожей функцией =ПРОПНАЧ. Все остальные буквы, помимо первой, будут преобразованы в прописные вне зависимости от их регистра.

Пример использования этой функции показан на картинке ниже.

👉 @Excel_lifehack

Подсказка при заполнении данных в ячейку

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

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

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

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

👉 @Excel_lifehack

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

Достаточно использовать в формуле функцию ЧИСТРАБДНИ для подсчета количества рабочих дней. Если у вас есть список праздничных дат, то можете разместить их в диапазон ячеек и указать третьим аргументом функции. Тогда эти дни не будут считаться рабочими при расчете.

👉 @Excel_lifehack

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

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

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

👉 @Excel_lifehack

Как скрыть формулу в ячейке?

Для этого выделяем нужную ячейку, нажимаем правой кнопкой мыши и выбираем пункт Формат ячеек.
Затем в всплывающем диалоговом окне во вкладке Защита проставляем галочку возле Скрыть формулы и жмем ОК.

Чтобы изменения вступили в силу, необходимо также установить защиту на сам лист (вкладка Рецензирование, группа Изменения, кнопка Защитить лист).

👉 @Excel_lifehack