​​​​Функция СЧЁТЕСЛИ (COUNTIF)

СЧЁТЕСЛИ позволяет подсчитать количество ячеек, соответствующих указанному условию.

СЧЁТЕСЛИ(диапазон; критерий)

• Диапазон — диапазон ячеек по которым необходимо выполнить подсчет;
• Критерий — условие, определяющее какие ячейки нужно подсчитать. Условие должно быть в виде числа, ссылки на ячейку, выражения и т.д.

Пример использования:
Предположим у нас есть данные по продажам продуктов в магазине.
Подсчитаем количество категорий продуктов, относящихся отдельно к овощам и фруктам (критерий №1 в нашем примере).

В качестве диапазона выбираем ячейки A2:A13 (категория продукта) и задаем критерий подсчета F3 («Овощи»).

​​​​Функция МАКСЕСЛИ (MAXIFS)

МАКСЕСЛИ вместе с функцией МИНЕСЛИ были добавлены в 2016-й версии Excel, она возвращает максимальное значение из ячеек, соответствующих определенным условиям.

МАКСЕСЛИ(макс_диапазон; диапазон_условия1; условие1; [диапазон_условия2; условие2];…)

• Макс диапазон (обязательный аргумент) — диапазон ячеек, для которого определяется максимальное значение;
• Диапазон условия1 (обязательный аргумент) — набор ячеек, оцениваемых с помощью условия;
• Условие1 (обязательный аргумент) — условие, определяющее какие ячейки нужно оценить;
• Диапазон условия2, Условие2, ... (необязательный аргумент) — дополнительные диапазоны и условия для них, можно ввести до 126 пар.

Пример работы формулы приведен на картинке ниже.

​​​​Как быстро перейти к нужному листу?

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

Шаг 1: Щелкните правой кнопкой мыши по любой из кнопок прокрутки листов, находящихся в левом нижнем углу экрана.

Шаг 2: Появится оглавление, которое позволит вам мгновенно перейти к нужному листу.

​​Как скопировать без нарушения форматов?

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

Чтобы избежать этой проблемы, после того как вы потянули за чёрный крест, появится специальный смарт-тег в правом нижнем углу скопированной области. Если выбрать опцию Копировать только значения (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не повредит оформление таблицы.

Создаешь макросы быстрее, чем Илон Маск запускает очередной Starship? Тогда забирай миллион!

Собирай свою команду и участвуй во Всероссийском Чемпионате Р7 офис по макросам. А если ты без команды — поможем её собрать!

Успей зарегистрироваться до 7 мая, пройди все задания, и уже в мае мы объявим победителей на очной защите проектов в Екатеринбурге.

Беги по ссылке, узнавай подробности и регистрируйся.

Реклама. АО "Р7". ИНН 5259019343. erid: 2VtzqxJ7xS6

​​​​Расчёт депозитных взносов

За подсчёт доходности от депозита или любых других инвестиций отвечает функция =БС (будущая стоимость).

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

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

​​​​Что нужно сделать, чтобы шапка печаталась на каждой странице?

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

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

​​​​Полезные фишки в Excel

Подборка функций, благодаря которым вы сэкономите время (и нервы) при работе в Excel.

Первая фишка — ВПР. Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. Открывается функция из «Мастера функций».

Вторая фишка — сводные таблицы. Создание этого вида таблицы позволяет переписать группы данных из одной таблицы в другую. Создаются подобные во вкладке «Вставка».

Третья фишка — макросы. С помощью них в программе записывается выполнение определенных действий, а потом они воспроизводятся автоматически. Для этого зайдите в «Меню разработчика» и найдите одноимённую кнопку.

​​​​Расчёт периодов для выплаты кредита

Чтобы подсчитать количество периодов (месяцев) для выплаты кредита, воспользуйтесь функцией =КПЕР(B2; B3; B4).

B2 — это кредитная ставка за период (в нашем случае за месяц) или ссылка на ячейку с ней;

B3 — это отрицательная сумма выплаты, производимой в каждый период;

B4 — приведенная (нынешняя) стоимость, то есть общая сумма кредита за все периоды.

​​Секретный лист

Если скрывать лист классическим способом «правая кнопка мыши» → Скрыть, его так же легко будет вернуть.

Но если нажать комбинацию ALT + F11, в появившемся окошке выбрать свой лист и изменить параметр Visible на xlSheetVeryHidden, то лист исчезнет, про него будете знать только вы.

Чтобы вернуть этот лист, поставьте параметр Visible обратно.

​​​​Как не ошибиться в сложных расчëтах

Опытные финансисты не используют стандартные формулы Excel в чистом виде. Им по душе создать одну тяжëлую и невероятно сложную формулу из 3-5 обычных.

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

1) ^ (возведение в степень)
2) * и / (умножение и деление)
3) + и - (сложение и вычитание)
4) & (конкатенация)
5) =, < и > (равно, меньше и больше)

​​​​Защита документов — разбор функций

Для подробного изучения ограничений вашей работы перейдите в раздел «Файл» и вкладку «Сведения».

— Защита текущего листа. Функция, как несложно догадаться, защищает рабочий лист от внешних воздействий. Пользователь не сможет удалить, скрыть или добавить новые листы в вашу работу.

— Защита структуры книги. Функция ограждает файл от изменения его структуры. Для защиты необходимо установить пароль.

— Добавить цифровую подпись. С помощью него юзер может установить цифровую подпись на нужные листы.

​​​​Недооценëнная функция

Большинство юзеров воспринимают функцию «СЧËТЕСЛИ» не как «рабочую», а как «запасную». Хотя она отвечает за множество полезных операций. Начнëм разбор:

Функция состоит из пары аргументов:

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

2. Логическое условие. Определяет, включать ли ячейку в расчёт.

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

​​Присвоение имени ячейке и формуле

Любое присвоение имени ячейкам, константам или формулам имеет схожий алгоритм.

Для этого найдите блок «Задать имя» и в контекстном меню «Создание имени» введите нужную ссылку на ячейку или формулу.

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

​​​​Настройки гистограммы

Стандартное меню настройки диаграммы открывается по клику на одноимённую функцию в разделе «Вставка».

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

Основное значение инструмента — отразить динамику изменений показателей с течением времени.

Чтобы добавить «Легенду» на гистограмму вы можете нажать на «Название диаграммы» и переписать его. А для изменения внешнего вида гистограммы кликните по появившейся вкладке «Конструктор» и найдите функцию «Стили диаграмм».

​​​​Форма для ввода данных

Excel позволяет вводить собственные данные не в табличной форме, а в привычном виде. Словно вы зашли на сайт и заполняете окошко регистрации.

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

В шапке программы найдите «Ленту», кнопку «Параметры Excel», найдите функцию «Форма» и добавьте еë на панель быстрого доступа.

Осталось открыть соответствующее окно и заполнить данные.

​​​​Как выделить все ячейки одним нажатием?

Для этого есть два способа:

1) Кликнуть на значок треугольника, который находится в углу над левым числовым столбцом и слева от буквенных значений.

2) Использовать комбинацию клавиш Ctrl + A.

​​​​Всплывающие сообщения в Excel

Блок инструментов «Проверка данных» позволяет настроить всплывающие сообщения, которые будут высвечиваться при наведении на ячейку.

Найдите соответствующую функцию в блоке «Редактирование» и кликните по «Подсказка по вводу». Не забудьте поставить галочку у параметра «Отображать подсказку, если ячейка является текущей».

Останется только ввести ваш текст в поле «Подсказка по вводу».

​​​​Вставка пустых ячеек

Следуйте данному алгоритму:

1) Выделяем диапазон, где хотите вставить пустые ячейки.

2) Затем используем комбинацию горячих клавиш CTRL + SHIFT + «плюс».

3) В появившемся окне выбираем Ячейки со сдвигом вниз и кликаем на ОК.

​​​​Пара «тяжëлых» элементов

Excel не без грехов. Есть инструменты, которые «весят» слишком много, из-за чего работа становится невыносимой:

1. Макросы. Этот графический инструмент делает файл подверженным к лагам и техническим ошибкам. Для большей надëжности заранее сохраните файл без использования макросов — так станет значительно проще и безопаснее.

2. Расчëтные формулы. Большое количество расчетных формул в совокупности с «простынëй» исходных данных также могут вам помешать. При возможности, исключите исходные данные и сохраните файл в таком виде.