Абсолютные и относительные ссылки

Каждая ячейка таблицы имеет свой уникальный адрес, для использования значения этой ячейки в формулах. Адрес ячейки состоит из имени столбца и номера строки, на пересечении которых она находится. В разных ситуациях этот адрес воспринимается по-разному. Чаще всего он используется в формулах. Формула представляет собой различные действия над значениями ячеек, а сами располагаются в ячейках таблицы. Формулой называется последовательность символов, начинающаяся со знака равенства. В эту последовательность могут входить постоянные значения, ссылки на ячейки, функции или операторы. Результатом работы формулы является новое значение. Несмотря на то что в формуле используются имена конкретных ячеек, в формулах используются не эти адреса, а координаты по отношению к той ячейке, где расположена формула. Поэтому формулы можно копировать в другие ячейки. Если значение в ячейках, на которые есть ссылка в формуле меняется, то результат изменяется автоматически. Если же в формуле нужно использовать не изменяющуюся при копировании ссылку на конкретную ячейку – используйте абсолютную адресацию, например, =$A$1. Создадим документ следующего вида.

1. Создайте новую книгу. Для этого выполните команду Файл/Создать/ Новая книга и нажмите Создать.

2. Введите в ячейки В1-K1, A2-A11 числа от 1 до 10 соответственно.

3. Введите в ячейку В2 формулу: =$B$1*A2 и нажмите Enter, а затем скопируйте ее в ячейки В3-В11.

4. Проделайте то же самое для остальных столбцов С-К, только в формуле вместо $B$1 введите $C$1, $D$1, $E$1,…, $K$1.

5. Выделите таблицу и установите ширину столбцов 2,29. Для этого выполните команду Главная/(ячейки) Формат/Ширина столбца, введите значение 2,29 и нажмите ОК.

 

6. Выделите таблицу и установите все границы. Для этого выполните команду: Главная/(Шрифт)/Границы/Все границы.

7. Выделите первую строку. Выполните команду: Главная/(ячейки)Формат/Формат ячеек / Граница. Установите жирную нижнюю границу и нажмите ОК.

 

8. Выделите первый столбец. Выполните команду: Глав-

ная/(ячейки)Формат/Формат ячеек / Граница установите жирную правую границу (аналогично с пунктом 6) и нажмите ОК.

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

 

Задания для самостоятельной работы

1.Создайте список с формулами следующего вида.

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

Пятый урок

Трехмерные ссылки

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

Создадим таблицу следующего вида, и сохраним его под именем Книга23.

1. Выделим ячейки А1:G16 ( Запись А1:G16 означает, что мы выделяем прямоугольную область от A1 до G16).

2. Правой кнопкой мыши выбираем Формат ячеек.

3. Во вкладке граница выбираем Граница внешние и внутренние.

4. Выделяем 11-ую строчку (диапазон А11:G11), щелкаем правой кнопкой мыши, выбираем объединить ячейки.

(Или выбираем значок «объединить и поместить в центре».)

5. В ячейке B10 вводим формулу =сумм(B$2:B$9) (Знак $ нам нужен для того, чтобы при копировании у нас не изменялся номер строки. Чтобы получить знак $ перед цифрой нажимаем 2 раза F4). Копируем эту формулу в C10:G10. Для этого выделяем ячейку B10, в правом нижнем углу нажимаем крестик и копируем ячейку вправо.

6. В ячейку B12 вводим формулу, которая считает общее количество проданных деталей за 6 дней только в Москве

=СУММ(B2:G2)+СУММ(B4:G4)+СУММ(B6:G6)+СУММ(B8:G8).

(Для удобства, достаточно в ячейке выбрать формулу =СУММ и выделить диапазон, указанный в скобках.)

7. В ячейку B13 введем формулу =СУММ(B3:G3).

8. В ячейку B14 введем =СУММ(B5:G5). 9. В ячейку B15 введем =СУММ(B7:G7).

10. В ячейку B16 введем =СУММ(B9:G9).

11. В ячейках C12:G16 нужно убрать внутренние границы. Выделяем эти ячейки, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница убираем внутренние.

12. Все оставшиеся ячейки заполняем так, как указано в примере.

Создадим файл следующего вида и сохраним его под именем Книга5.

1. Выделяем ячейки A1:B6, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница выбираем внешние и внутренние.

2. Ячейки А1:А6, В1 заполняем так, как показано в примере.

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

4. В ячейку B2 введем формулу =[Книга23.xlsx]Лист1!$B$12.

Для этого в ячейку вводим знак «=», затем переходим в файл Книга 23, нажимаем курсором на ячейку В12 и нажимаем Enter. Теперь мы видим, что в ячейке В12 появилось нужное нам значение. Аналогично вводим формулы в ячейки В3:В6.

5. В ячейку B3 введем формулу =[Книга23.xlsx]Лист1!$B$13.

6. В ячейку B4 введем формулу =[Книга23.xlsx]Лист1!$B$14.

7. В ячейку B5 введем формулу =[Книга23.xlsx]Лист1!$B$15. 8. В ячейку B6 введем формулу =[Книга23.xlsx]Лист1!$B$16.

Консолидация данных.

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

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

1. Создать новую Книгу.

2. Ввести данные в ячейки

3. Сохранить Книгу под именем таблица.xlsx.

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

Сначала сделаем пустую таблицу.

1. Введите имена строк и столбцов таблицы.

2. Выделите диапазон A1:E3 и выполните команду Главная / (ячейки) Формат /Формат ячеек.

3. Во вкладке Границы сделайте внутренние и внешние рамки, выберите цвет «белый» и нажмите ОК.

4. Выделите шапку таблицы. Выделите ячейки A1:E3 и выполните команду Главная/(ячейки)Формат/Формат ячеек.

5. Во вкладке Заливка измените цвет ячеек таблицы на черный.

6. Во вкладке Шрифт измените цвет текста на белый.

Вот что должно получиться после наших изменений:

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

Чтобы вставить ссылки в последнюю таблицу сделайте следующее:

1. Выделите ячейку B2 второй таблицы.

2. В этой ячейке поставьте знак "=".

3. Перейдите в файл таблица.xlsx, где находятся исходные данные.

4. Найдите в первой таблице строчку, в которой значения ячейки в столбцах Спорт, Год соответствует значению Гольф, 93 соответственно, и выделите её, нажать Enter. В ячейку вставится ссылка на ячейку исходной таблицы.

5. Аналогично проделайте действия с ячейками C2 – E2, B3 – E3.