Предположим, что лист в Р7 Офис разделен на более чем 1 миллион строк и более 16 000 столбцов. Строки нумеруются 1, 2, 3 и так далее, до 1 048 576, а столбцы обозначаются A, B, C и так далее, до XFD. Строки и столбцы пересекаются, образуя более 16 миллиардов ячеек на одном листе.

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

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

• Введение в систему ссылок
• Относительная адресация
• Абсолютная адресация
• Смешанная адресация
• Реализация системы ссылок

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

Следующий скриншот является простейшим примером этого. Введя =D4 в ячейку F5, содержимое ячейки D4, "Happy day", было продублировано в ячейке F5:

Себестоимость продаж — это Количество проданных единиц × Себестоимость единицы, что в данном случае составляет 30 × 65 000. Строка формул показывает, что мы ввели =30*65000, чтобы получить 1 950 000.

Два основных недостатка этого метода заключаются в следующем:

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

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

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

Строка формул на предыдущем скриншоте показывает, что мы ввели =F5*I5.

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

Еще одним преимуществом ссылок является то, что по умолчанию Р7 Офис регистрирует положение ссылок на ячейки относительно активной ячейки. Так, в предыдущем примере F5 регистрируется как четыре ячейки слева, а I5 — как одна ячейка слева от активной ячейки J5.

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

Таким образом, если формула копируется на 15 ячеек вниз, строковая часть ссылки корректируется на 15 строк вниз, и F5*I5 автоматически становится F20*I20. Таким образом, поскольку формула одна и та же, то есть Количество проданных единиц × Себестоимость единицы, мы можем просто скопировать нашу формулу вниз по списку и по-прежнему получать правильные ответы. Это можно увидеть на следующем скриншоте:

Это не сработало бы в примере, который мы видели в предыдущем разделе, где мы вводили значения непосредственно в активную ячейку. Если бы мы скопировали вниз в этом случае, мы бы получили одно и то же значение, 1 950 000, по всему списку.

Этот метод ссылок на ячейки вместо их фактических значений называется относительной адресацией.

Существует несколько различных способов копирования в диапазон ячеек:

• Первый способ — выбрать ячейку или диапазон ячеек для копирования, нажать Ctrl + C, выбрать диапазон ячеек, в который вы собираетесь копировать, а затем нажать Enter или Ctrl + V.

Если вы нажмете Ctrl + V, Р7 Офис поместит значок Ctrl (или аналогичный элемент управления параметрами вставки) в правом нижнем углу последней ячейки диапазона. Затем вы можете щелкнуть значок или просто нажать Ctrl, и появится окно с параметрами специальной вставки, как показано на следующем скриншоте:

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

Эта функция недоступна, если вы нажимаете Enter для вставки.

• Второй способ встроен в Р7 Офис.

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

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

По мере продвижения вниз по списку номер строки изменяется, так что ссылка на продажи, сделанные продавцами, перемещается с H5 на H6, на H7 и, в конечном итоге, на H20, что является последней записью в нашем списке.

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

Мы делаем это, ставя знак $ перед частями ссылки, обозначающими столбец и строку. Таким образом, H2 становится $H$2.

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

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

Первый скриншот показывает формулу, ссылающуюся на H2:

На следующем скриншоте мы видим, что формула была введена в ячейку K5 как =H5*$H$2, что становится 46 800 при нажатии Enter:

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

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

Это пример абсолютной адресации.

Как мы упоминали ранее, ссылка на ячейку состоит из строк и столбцов, которые пересекаются, образуя эту ячейку. Таким образом, если ячейка находится в столбце G, строке 59, ее ссылка — G59; G — часть столбца, а 59 — часть строки ссылки на ячейку. Две ячейки не могут иметь одинаковую ссылку.

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

Вы должны принять к сведению две вещи:

• Во-первых, система ссылок актуальна только тогда, когда вы хотите скопировать формулу в другое место.

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

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

Следующий пример, использующий тот же лист Отчет о продажах, стремится сравнить продажи, полученные с использованием наценок 15%, 20% и 25%.

В этом случае продажи рассчитываются как Себестоимость продаж × (1 + % наценки).

Это скриншот макета для наценки:

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

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

Базовая формула: =H5*(1+I4).

Обратите внимание, что в формуле есть две ссылки на ячейки, H5 и I4, которые вам нужно будет рассмотреть по отдельности.

Ячейка H5 — это Себестоимость продаж. Часть столбца — H, которую мы рассмотрим при копировании вправо по столбцам. Часть строки — 5, которую мы рассмотрим при копировании вниз по строкам. При копировании формулы вниз по строкам вы хотите, чтобы себестоимость продаж изменялась от одной записи к следующей вниз. Другими словами, часть строки ссылки, 5, не должна быть заблокирована — она должна оставаться относительной; то есть перед ней не должно быть знака $.

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

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

Таким образом, наша первая ссылка — $H5.

Ячейка I4 — это % наценки, который равен 15%. Часть столбца — I, которую мы рассмотрим при копировании вправо по столбцам. Часть строки — 4, которую мы рассмотрим при копировании вниз по строкам.

При копировании формулы вниз по строкам вы хотите, чтобы маржа наценки, равная 15%, оставалась той же от одной записи к следующей вниз. Другими словами, часть строки ссылки, 4, должна быть заблокирована и иметь знак $ перед ней. При копировании формулы по столбцам наценка должна перемещаться с 15% на 20% и так далее. Другими словами, часть столбца ссылки, I, не должна быть заблокирована знаком $ перед ней
(Примечание: Оригинальный текст здесь содержит ошибку, утверждая, что столбец I должен быть заблокирован. Для перемещения по столбцам наценки, столбец должен оставаться относительным).

Следующий скриншот показывает, как определить шаблон адресации второй ссылки (с корректной логикой):

Таким образом, наша вторая ссылка будет I$4, и формула тогда будет =$H5*(1+I$4).

Это приводит к следующему результату:

Теперь мы сделаем это для всех ячеек.

Клавиша F4 на вашей клавиатуре — это клавиша-переключатель, которая циклически перебирает четыре варианта. Используя ссылку на ячейку H5 в качестве примера, нажатие клавиши F4 один раз поставит знак $ перед частями столбца и строки, чтобы получить $H$5. Второе нажатие поставит знак $ только перед частью строки, чтобы получить H$5. Третье нажатие поставит знак $ только перед частью столбца, чтобы получить $H5. Наконец, четвертое нажатие клавиши F4 вернет ссылку к относительной ссылке, H5, без знаков $.

Теперь скопируйте формулу вправо и вниз.

Всегда разумно проверять, дает ли скопированная формула правильный ответ. Вы можете сделать это, проверив ячейку в правом нижнем углу скопированного диапазона. В данном случае это ячейка K20, которая правильно ссылается на ячейки H20 и K4.

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

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

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

• Общие баллы, набранные учеником по всем языкам
• Общие баллы, набранные учеником по всем наукам
• Общие набранные баллы

Система ссылок действительно проявляет себя в таких сценариях.

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

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

Теперь мы начнем сортировать данные.

Рассматривая первый критерий, мы видим, что оценки по языкам находятся в столбцах с B по E. Итак, давайте введем формулу для ученика 1 в соответствующее поле, которое в данном случае — L2.
Поскольку мы хотим рассчитать сумму баллов, наша формула будет =B2+C2+D2+E2 (или =СУММ(B2:E2)), как показано на следующем скриншоте:

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

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

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

В качестве упражнения я оставлю столбец Баллы по наукам пустым, чтобы вы могли попробовать это самостоятельно.

Повторите то же самое для столбца Общие набранные баллы.

Наша окончательная таблица должна выглядеть примерно так:

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

Мы можем сделать это, используя следующую формулу:

Для этого мы применим смешанную адресацию.

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

Теперь мы введем формулу для расчета процента для ученика 1 в ячейку O2, как показано здесь:

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

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

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

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

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

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