​​Сортировка по нестандартным спискам

На вкладке Данные в наборе команд Сортировка и фильтр есть 3 команды для выполнения сортировки: от А до Я (по возрастанию), от Я до А (по убыванию) и команда Сортировка для более сложных настроек сортировки.

Стандартная сортировка не всегда подходит — иногда может потребоваться выполнить сортировку в столбце с текстовыми наименованиями не совсем по алфавиту (или совсем не по алфавиту).
Ярким примером такой сортировки является список месяцев в году, где идет порядок Январь-Февраль-Март… . И при этом названия месяцев — не формат даты, а просто текст.

Чтобы выполнить такую сортировку, выделяем ячейку в таблице, нажимаем кнопку Сортировка, в поле Сортировать по указываем значение Месяц, для поля Порядок — Настраиваемый список. Затем выбираем список с месяцами и жмем ОК, готово.

В окне Списки можно увидеть списки месяцев и дней недели — это стандартные списки в Excel. Нестандартные списки можно создать самостоятельно в том же окне, занеся элементы списка через запятую.

​​​​Функция ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления])

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

Совет: Функцией ПОИСКПОЗ следует пользоваться вместо одной из функций ПРОСМОТР, когда требуется найти позицию элемента в диапазоне, а не сам элемент.

​​​​Проверка данных на ошибки

Excel может предотвратить допущение ошибок и ограничить некорректные действия.

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

Для доступа к функции Проверка данных, перейдите на вкладку Данные.

​​​​Как обрезать изображение по кругу?

При оформлении некоторых документов будет уместна обрезка изображений по кругу или любой другой фигуре.

Чтобы это реализовать, выберите нужную картинку и перейдите в раздел Формат → Обрезать → Обрезать по фигуре.

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

​​​​Небольшой лайфхак при работе с листами

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

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

​​​​Функция СЧЁТЕСЛИ (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», найдите функцию «Форма» и добавьте еë на панель быстрого доступа.

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