ВПР и Сводные таблицы Excel

Сергей Заидович Карамов

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

Ссылки

Ссылка — это самая элементарная операция в Excel.

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

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

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

Операции с данными в Excel проводятся в ячейке листа (в группе выделенных ячеек) над данными других ячеек листа.

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

Вид операции зависит от указанного оператора.

Адресная ссылка

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

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

Прямоугольные координаты ячейки: буква столбца и номер строки называют еще адресом ячейки. Таким образом каждая ячейка Excel имеет свой индивидуальный адрес (см. Рис.30).

Адрес ячейки — это еще и имя ячейки, заданное ячейке по умолчанию.

Таким образом, адрес ячейки A3 — это имя ячейки, расположенной в первом столбце (столбце А) и третьей строке, а D5 — это имя ячейки, расположенной в четвертом столбце (столбце D) и пятой строке. На Рис. 30 выделена ячейка A3.

Рис. 30. Выделенная ячейка A3

Слева вверху на одном уровне со строкой формул расположено окно имен. В строке имен можно увидеть имя этой ячейки — A3.

Введем в ячейку A3 число 100.

В другую ячейку, например, в ячейку D5, запишем операцию следующего вида «=A3» (выделяем ячейку D5, с клавиатуры последовательно вводим знак равно, затем A3 (А в английской раскладке клавиатуры и прописной буквой) и нажимаем клавишу ввод — Enter).

Результат операции должен выглядеть как на Рис. 31.

Рис. 31. Ссылка в ячейке D5 на ячейку A3

После ввода операции в ячейке D5 появилось число 100, то есть число, записанное в ячейке A3. Вид операции «=A3» можно видеть в строке формул справа вверху.

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

Введенная операция и есть элементарная операция — операция ссылки или ссылка. Мы в ячейке D5 как бы сослались на ячейку A3 и получили те данные, которые хранятся в ячейке А3.

Такой вид ссылок будем называть адресными ссылками.

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

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

Поэтому правильно ввести операцию ссылки следует путем последовательного выполнения следующих действий: выделить ячейку D5, ввести равно, выделить ячейку A3, нажать Enter.

Обратите внимание на то, как сделана эта операция. Мы просто выделили ячейку A3, а координаты ячейки появились в ячейке с операцией.

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

Редактирование операции ничем не отличается от редактирования данных.

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

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

Рис. 32. Выделение цветом ячеек, участвующих в операции в режиме редактирования

Характерной особенностью операции ссылки является то, что ссылка постоянно отражает данные той ячейки, на которую она ссылается. Если меняется информация в ячейке A3, автоматически меняется информация в ячейке D5. То есть данные в ячейке А3 влияют на данные ячейки D5. Но изменения в ячейке D5 никак не повлияют на информацию в ячейке А3.

Схематично это можно изобразить в виде стрелки, как на Рис. 33.

Рис. 33. Изображение зависимости ячеек в операции ссылки

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

Ссылку можно создать и с помощью операций копирования-вставки.

Если в ячейке D5 нужно создать ссылку на ячейку А3, можно просто скопировать ячейку А3 и, выделив ячейку D5, выбрать в параметрах вставки позицию «Вставить ссылку» (самая правая позиция вставки). Ссылка в этом случае будет иметь вид абсолютной ссылки.

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

Например, операция вида =A3+D5 применительно к данным — это арифметическая операция сложения чисел из ячеек A3 и D5, результатом выполнения которой будет число 200.

Виды адресных ссылок

Несмотря на кажущуюся простоту, ссылка — понятие достаточно сложное.

Рассмотрим такой пример. В ячейке D5 есть ссылка на ячейку А3, в которой находится число 100. Скопируем ячейку D5 и вставим в ячейку D8. Результат показан на Рис. 34.

Рис. 34. Копирование ссылки в другую ячейку

Как мы видим, в ячейке вместо числа 100, появился 0. А в строке формул хорошо видно, что после вставки ссылка стала А6, а ведь первоначально ссылка была А3. Почему так произошло?

Все дело в том, что ссылка имеет определенное свойство, которое проявляется лишь тогда, когда с ячейкой, в которой введена ссылка, начинают выполнять определенные операции, например копировать перетягиванием или путем операции «копировать — вставить».

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

Рис. 35. Классификация адресных ссылок

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

Относительные ссылки

Относительная ссылка — это ссылка, которая при перетягивании ячейки изменяет свои параметры. Рассмотрим на примере данных, как изменяется относительная ссылка при перетягивании. Сначала скопируем перетягиванием ячейку D5 (см. Рис. 34) со ссылкой вправо по строке на несколько ячеек, а затем эту же ячейку перетянем вниз по столбцу на несколько ячеек. Изменения относительной ссылки показаны на Рис. 36. То, что Вы видите на рисунке — это искусственно созданные построения, чтобы показать, какой будет ссылка при перетягивании в других ячейках. Реально на экране Вы увидите в ячейках нули.

Рис. 36. Изменение относительной ссылки при перетягивании

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

Конец ознакомительного фрагмента.