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

В этой главе будут рассмотрены следующие темы:

• Понимание функций и формул
• Работа с функциями поиска
• Вспомогательные функции
• Сводные таблицы и диаграммы
• Подводные камни, которых следует избегать

Чтобы ввести формулу или функцию, сначала необходимо ввести знак =. Формула — это выражение, включающее один или несколько операндов (+, -, /, *, ^), например, =34+7 или =A3-G5 (эта формула вычитает содержимое ячейки G5 из содержимого ячейки A3). Функция также может быть включена как часть формулы, например, =СУММ(B3:B7)*A3.

Функция — это команда, содержащая последовательность инструкций для выполнения Р7 Офис. Функция содержит один или несколько аргументов, предлагая пользователю указать входную ячейку или диапазон ячеек, к которым должны применяться инструкции, например, ПОИСКПОЗ(A5; F4:F23; 0).

Функция может включать формулу как часть аргумента, например, =ЕСЛИ(A4*B4>C4; D4; E4).

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

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

Функции поиска являются одними из наиболее широко используемых функций в Р7 Офис. Как правило, цель состоит в том, чтобы извлечь значение из одной таблицы (источника) в активную ячейку, в которой вы вводите формулу (цель). По сути, функция указывает Р7 Офис определить строку и столбец в таблице-источнике. Пересечение этой строки и столбца даст вам исходную ячейку, значение которой вы хотите извлечь.

Например, предположим, у вас есть отчет о продажах, который включает данные о различных продуктах, проданных за определенный период, и вы хотите заполнить поле «Стоимость продукта» стоимостью каждого из продуктов в вашем отчете.

Следующий скриншот представляет собой пример отчета о продажах, показывающий детали ежедневных продаж, включая Продукт, Продавца и другие детали:

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

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

Следующий скриншот представляет собой пример таблицы База данных продуктов, показывающий коды продуктов и себестоимость единицы каждого продукта:

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

Существует ряд функций поиска, каждая из которых лучше всего подходит для определенных сценариев.
Мы рассмотрим некоторые из наиболее популярных.

Любой аргумент, заключенный в квадратные скобки [], является необязательным; следовательно, если для этого аргумента не введено значение, принимается значение по умолчанию. Значения должны быть введены для всех остальных аргументов; в противном случае формула приведет к ошибке.

Для функции ВПР необязательным аргументом является `интервальный_просмотр`. Он требует выбрать ИСТИНА (или 1), если вы ищете приблизительное совпадение для вашего искомого значения, или ЛОЖЬ (или 0), если вы ищете точное совпадение. Если значение не выбрано, то аргумент по умолчанию принимает значение ЛОЖЬ (или 0 в некоторых версиях).

Эта функция указывает Р7 Офис найти искомое значение в первом столбце слева от указанного вами массива поиска. Обратите внимание, что это может быть не первый столбец таблицы-источника.

В нашем примере искомое значение — это код продукта; следовательно, для первой записи в нашем отчете о продажах искомое значение — BN001, которое находится в ячейке D5.

Следующий скриншот показывает построение формулы ВПР в таблице Отчет о продажах:

Наш массив поиска должен начинаться со столбца C в таблице База данных продуктов, так как именно здесь находится поле Код продукта — обратите внимание, что это второй столбец таблицы База данных продуктов.

Затем Р7 Офис найдет позицию искомого значения в этом первом столбце, как показано на следующем скриншоте:

Из предыдущего скриншота мы видим, что продукт BN001 находится в строке 11 таблицы База данных продуктов, в поле Код продукта.

Следующий аргумент — `номер_столбца` (column index number), который относится к позиции исходного поля в массиве поиска, начиная с уникального поля как столбца 1. Исходное поле — это поле, из которого вы хотите извлечь данные. В нашем примере исходное поле — Себестоимость единицы, которое является столбцом D, вторым столбцом нашего массива поиска. Это дает нам `номер_столбца` равный 2:

Таким образом, мы определили столбец D и строку 11 для исходной ячейки.
Затем Р7 Офис извлечет данные из ячейки D11 (65 000) и поместит их в нашу целевую ячейку в таблице Отчет о продажах:

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

Функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) обычно используются вместе. Функция ИНДЕКС имеет аргументы для указания строки и столбца исходной ячейки.
Чтобы сделать формулу динамической, вы заменяете аргумент ИНДЕКС для строки, столбца или обоих на функцию ПОИСКПОЗ.
Это очень мощная формула, которая имеет аргументы как для массивов, так и для простых формул:

Первая строка аргументов формулы ИНДЕКС предназначена для диапазона массива. Формулы массива возвращают диапазон значений, в отличие от простых формул, возвращающих только одно значение. Мы коснемся формул массива позже в этой книге, но пока сосредоточимся на простых формулах.

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

Если вы ограничиваете ссылку ИНДЕКС одним столбцом, вы фактически определяете столбец исходной ячейки. В нашем примере мы выбрали ячейки с D5 по D13 в качестве нашей ссылки:

Следующий аргумент — `номер_строки`. Чтобы подставить его вместо аргумента строки, вам нужно будет встроить функцию ПОИСКПОЗ в функцию ИНДЕКС, которая все еще активна. Для этого просто начните вводить новую функцию после запятой.
Пока вы еще не ввели последнюю закрывающую скобку для функции, Р7 Офис распознает, что формула все еще активна, поэтому нет необходимости снова вводить =.

Первый аргумент в функции ПОИСКПОЗ — это `искомое_значение`, которое вы ищете в массиве поиска. В этом случае, однако, нет ограничений на расположение массива поиска. Мы используем то же искомое значение, что и в примере ВПР, BN001, в ячейке D5 листа Отчет о продажах.

Здесь, опять же, как показано на следующем скриншоте, вы можете ограничить массив поиска одним столбцом. В нашем примере мы знаем, что совпадения для наших искомых значений находятся в поле Код продукта в столбце C листа База данных продуктов. Поэтому мы выбираем ячейки с C5 по C13 в качестве нашего массива поиска:

Обратите внимание, что массив поиска ПОИСКПОЗ должен начинаться с той же строки листа, что и ссылка в функции ИНДЕКС. В нашем примере обе начинаются со строки 5 листа. Вы завершаете формулу ПОИСКПОЗ аргументом `тип_сопоставления`, который аналогичен `интервальный_просмотр` в функции ВПР. Вам нужно указать, хотите ли вы приблизительное совпадение, меньше искомого значения (1), больше искомого значения (-1) или точное совпадение (0).

Функция ПОИСКПОЗ возвращает целое число, соответствующее позиции строки в массиве поиска, в которой найдено искомое значение. Это не следует путать с номером строки листа.

В нашем примере функция ПОИСКПОЗ вернет число 7, потому что искомое значение, BN001, найдено в 7-й строке нашего массива поиска — строка 11 листа. Как только вы закроете последнюю скобку формулы ПОИСКПОЗ, Р7 Офис вернет вас к функции ИНДЕКС.

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

Определив строку 7 и столбец D, мы теперь имеем исходную ячейку D11, которая возвращает значение 65000. Вы заметите, что ИНДЕКС и ПОИСКПОЗ преодолевают ограничение ВПР, где уникальное поле должно быть первым столбцом массива поиска.

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

Функция ВЫБОР позволяет создать список значений или действий для выполнения, а затем выбрать, какое значение использовать или какое действие выполнить, выбрав номер, соответствующий позиции значений или действий в списке. Синтаксис ВЫБОР — как показано на следующем скриншоте — имеет два обязательных аргумента: `номер_индекса`, затем список значений или действий, показанных как `значение1`, `значение2` и так далее.

Этот скриншот показывает аргументы функции ВЫБОР:

В приведенном здесь примере мы хотим показать результаты отдельно от среднего и медианного значений себестоимости единицы продукции на листе База данных продуктов. Сначала мы настраиваем проверку данных в пустой ячейке, чтобы значения 1 или 2 можно было выбрать, щелкнув стрелку раскрывающегося списка, которая появляется рядом с ячейкой. Ячейка с проверкой данных будет `номер_индекса` формулы ВЫБОР.

Это скриншот проверки данных:

Затем мы перечисляем действия на выбор. В данном случае у нас есть среднее или медиана. Если мы перечислим их в таком порядке, то если `номер_индекса` показывает 1, будет выбрано среднее, а если 2, то будет выбрана медиана.

Следующий скриншот показывает полную формулу ВЫБОР:

Когда номер индекса равен 1, выбирается функция СРЗНАЧ (AVERAGE), и она возвращает значение 43 444,44.

Это скриншот функции ВЫБОР с Номер_индекса равным 1:

Когда номер индекса равен 2, выбирается функция МЕДИАНА (MEDIAN), и она возвращает значение 45 000,00.

Это скриншот функции ВЫБОР с Номер_индекса равным 2:

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

Вы всегда можете выбрать все случаи продаж, а затем рассчитать их сумму. После этого вам нужно снова выбрать все случаи сделанных закупок и найти сумму закупок. Частое выполнение таких действий было бы очень трудоемким и скучным. Вот где на помощь приходит функция ВЫБОР! Мы можем реализовать простую модель, которая сделает все за вас одним щелчком мыши, выполнив следующие шаги:

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

Первое значение, которое нам нужно ввести, — это ячейка, где мы разместили опции,
которая в нашем случае — F4:

Теперь мы назначим два варианта для F4, 1 и 2, используя проверку данных.
Мы отредактируем формулу так, чтобы, если мы выберем 1, ячейка, где введена формула, отображала сумму всех продаж, сделанных за эту неделю, а если мы выберем 2, она отображала сумму всех закупок.

Итак, сначала мы выберем все ячейки в столбце Продажи, с C6 по C14, и введем это в ячейку формулы как СУММ(C6:C14), как показано здесь:

Далее мы выберем все ячейки в столбце Закупки и введем их в следующее поле формулы как СУММ(D6:D14), следующим образом:

Теперь, как видно на предыдущем скриншоте, когда выбрано 1, становится видимой стоимость продаж, которая в нашем случае составляет 315 455,00.

Если мы выберем 2, мы получим стоимость закупок, как показано здесь:

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

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

Некоторые примеры вспомогательных функций: ЕСЛИ (IF), И (AND), ИЛИ (OR), МАКС (MAX), МИН (MIN) и
ПОИСКПОЗ (MATCH).

Теперь мы рассмотрим некоторые из них.

Это одна из наиболее широко используемых функций в Р7 Офис. Ее можно использовать самостоятельно или как часть другой формулы. Функция ЕСЛИ проверяет, выполняется ли условие, затем возвращает одно значение, если оно выполняется, и другое значение, если нет.

Синтаксис содержит три аргумента:

• `логическое_выражение`: Логический тест — это утверждение, которое возвращает значение ИСТИНА, если условие выполняется, или ЛОЖЬ, если условие не выполняется.

• `значение_если_истина`: Этот аргумент позволяет указать, какое значение вы хотите вернуть, если условие выполняется и результат логического теста — ИСТИНА.

• `значение_если_ложь`: Этот аргумент позволяет указать, какое значение вы хотите вернуть, если условие не выполняется и результат логического теста — ЛОЖЬ.

Допустим, вы хотите вознаградить своих продавцов 2% от продаж всякий раз, когда прибыль превышает 300 000. Вы можете написать формулу ЕСЛИ для автоматизации этого. Логическим тестом будет утверждение — прибыль больше 300 000. В следующем примере для первой записи это K5>K2. Утверждение будет либо истинным, либо ложным. Если результат истинный, возвращаемое значение — Продажи × Комиссия (2%). В нашем примере это H5*H2. Если результат ложный, то возвращаемое значение будет 0.

Следующий скриншот является иллюстрацией формулы ЕСЛИ:

Эти функции используются для выбора либо максимального (МАКС), либо минимального (МИН) значения из списка значений. Проявив немного воображения, вы можете очень эффективно использовать формулы МАКС или МИН.

Например, в вашей финансовой модели Остаток денежных средств может оказаться положительным или отрицательным.

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

Способ обойти это — использовать формулы МАКС и МИН, как показано на следующем скриншоте:

На предыдущем скриншоте мы просим формулу МАКС отобразить большее значение из Остатка денежных средств и 0. Положительный остаток денежных средств всегда будет больше 0 и, следовательно, будет отображаться как Денежные средства в кассе. Однако всякий раз, когда остаток денежных средств отрицательный, поскольку он всегда будет меньше 0, Денежные средства в кассе будут отображать 0.

Следующий скриншот является иллюстрацией функции МИН:

В этом случае мы используем формулу МИН, чтобы гарантировать, что только отрицательные остатки денежных средств будут отображаться как Овердрафт, поскольку отрицательный остаток денежных средств всегда будет меньше 0. (Примечание: для корректного отображения овердрафта как положительного числа в обязательствах, возможно, потребуется использовать `-МИН(Остаток; 0)` или `ABS(МИН(Остаток; 0))`).

Скопировав формулы, мы можем увидеть, как остатки денежных средств были аккуратно и точно классифицированы как Денежные средства в кассе и Овердрафт, как показано на следующем скриншоте, который показывает полный результат после применения формул МАКС и МИН:

Теперь мы применим функции МАКС и МИН к файлу Marks.xlsx, использованному в Главе 4, «Применение системы ссылок в Р7 Офис». Мы будем использовать функцию МАКС, чтобы найти самый высокий балл в классе, и функцию МИН, чтобы найти самый низкий, выполнив следующие шаги:

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

Сводные таблицы — один из самых мощных инструментов в Р7 Офис. Сводная таблица может суммировать небольшие или большие объемы данных в компактную форму, которая выявляет тенденции и взаимосвязи, не очевидные при просмотре исходных данных.

Сводная таблица позволяет вводить условия на основе исходных данных, чтобы вы могли просматривать суммированные данные с разных точек зрения. Все это делается без необходимости вводить какие-либо формулы. Большинство пользователей полагают, что отчеты сводных таблиц сложны и трудны для подготовки; но на самом деле сложность скрыта «за кулисами» и обрабатывается Р7 Офис. Все, что вам нужно сделать, это следовать нескольким простым рекомендациям, и вы сможете с легкостью создавать сложные сводные таблицы.

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

Идентификация и навигационные сочетания клавиш Р7 Офис зависят от того, находится ли ваша таблица в правильном формате. Большинство действий требуют указания целевого диапазона. Р7 Офис может правильно определить требуемый диапазон и изолировать заголовки полей, но только если данные находятся в правильном формате таблицы, что можно увидеть на следующем скриншоте:

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

Р7 Офис очень эффективно определяет типы данных и обрабатывает различные форматы дат, включая 15.01.2019, 15-Янв-19, 15-01-2019, 01-15-2019 и 2019-01-15, среди прочих. Однако Р7 Офис очень чувствителен, и любая незначительная аномалия в данных может привести к ошибочным результатам. Например, если вы случайно введете начальный пробел перед датой — как на изображении слева на следующем скриншоте — Р7 Офис обработает это как Общий тип данных.

Изображение справа на следующем скриншоте показывает тот же текст без начального пробела; поэтому Р7 Офис правильно распознает его как дату и автоматически назначает ячейке формат Дата:

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

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

Хотя вы можете разместить сводную таблицу на том же листе, что и исходные данные, это иногда может привести к перегруженности. По умолчанию Р7 Офис создаст сводную таблицу на новом листе.

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

Когда вы нажмете OK, будет создана сводная таблица. Изначально только список полей будет заполнен именами всех полей вашей таблицы, которые будут расположены вертикально с флажками рядом с ними. Ниже находятся четыре области: Фильтры, Столбцы, Строки и Значения. Вы строите таблицу, перетаскивая имена полей в нужные области.

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

Поскольку вы еще не указали условие или критерии, Р7 Офис просто суммирует поле Значения и называет его Сумма по полю Продажи. Затем вы можете захотеть отобразить продажи по продавцам для каждого продукта.

Следующий скриншот показывает продажи по продавцам:

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

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

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

Иллюстрация продаж продуктов в сумме и в процентах от валовых продаж показана на следующем скриншоте:

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

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

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

Выберите один, и рядом с вашей сводной таблицей появится сводная диаграмма:

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

Этот скриншот показывает сводную таблицу и сводную диаграмму, которые были отфильтрованы, чтобы показать только продажи Иябо:

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

1. Перейдите на лист Top scores в файле, который содержит всех учеников и их проценты в таблице.
2. Теперь, чтобы создать сводную таблицу, мы выберем все столбцы в таблице и создадим сводную таблицу, используя опцию Сводная таблица в меню Вставка:

Поскольку мы хотим узнать топ-10 баллов, мы можем использовать встроенный фильтр в фильтрах сводной таблицы.

Чтобы открыть следующий раскрывающийся список, щелкните значок фильтра в углу столбца Названия строк:

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

Убедитесь, что все в окне выглядит так же, как на предыдущем скриншоте, и нажмите OK. Это приводит к следующему результату:

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

Для этого мы снова щелкнем значок фильтра и перейдем к Дополнительные параметры сортировки..., как показано здесь:

Таким образом, у нас есть результаты топ-10 учеников в классе!
Это показывает, насколько мощной может быть сводная таблица при правильном использовании.

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

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

Рассмотрим следующий пример:

=ИНДЕКС(C5:G10;ПОИСКПОЗ(J20;C5:C10;0);ПОИСКПОЗ(K19;C5:G5;0))

CopyExplain

Эту сложную формулу можно разбить на три части с помощью Alt + Enter, следующим образом:

=ИНДЕКС(C5:G10;
ПОИСКПОЗ(J20;C5:C10;0);
ПОИСКПОЗ(K19;C5:G5;0))

CopyExplain

Как мы видим, это облегчает расшифровку.

Если вы собираетесь делиться своей моделью с другими, важно защитить ваши формулы от случайных изменений, которые могут сделать модель бесполезной. Для этого сначала выделите ячейки без формул, которые вы хотите разрешить изменять, нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек, перейдите на вкладку Защита, снимите флажок Защищаемая ячейка (Locked) и нажмите OK. Это разблокирует ячейки, которые можно изменять.

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

Например, процентная ставка 15% сначала вводится на листе 1, ячейка B5. Если процент требуется на листе 2, ячейка D16, вместо того, чтобы снова вводить 15%, вы просто ссылаетесь на лист 1, ячейку B5, введя =, затем указав лист 1, ячейку B5. Если процент снова появляется на листе 3, ячейка J13, теоретически вы могли бы сослаться на лист 2, ячейку D16. Однако, чтобы сохранить простой аудиторский след, ссылка должна быть на исходную запись этого значения, лист 1, ячейка B5. Старайтесь использовать только одну формулу на строку.

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

В этой главе мы узнали о мощи формул и функций и о том, как мы можем использовать их для ускорения нашего моделирования и сделать его более интересным. Мы также рассмотрели примеры некоторых из наиболее распространенных функций, таких как ВПР, ПОИСКПОЗ и ВЫБОР.

В следующей главе мы рассмотрим одну из функций, составляющих основу Р7 Офис, — систему ссылок. Понимание этой системы и знание того, как применять ее принципы, помогут вам ускорить работу и повысить производительность.