Файл: методичка моя.docx

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

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

Например, при копировании ячейки D2 (рис. 9) на нижерасположенные ячейки, в ячейке D3 будет формула =В3*С3, в ячейке D4 будет формула =В4*С4 и т. д.

Рис. 9.  Копирование формул

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

Рис. 10.  Использование абсолютных ссылок

Для того чтобы ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка на ячейку имеет формат $A$1.

Чтобы ссылка на ячейку была абсолютной при создании формулы, после указания ссылки на ячейку следует нажать клавишу клавиатуры F4. Ссылку можно преобразовать из относительной в абсолютную и при редактировании ячейки с формулой. Для этого к заголовкам столбца и строки в адресе ячейки следует добавить символ $. Например, для того чтобы ссылка на ячейку G2 стала абсолютной, необходимо ввести $G$2.

Абсолютными ссылками по умолчанию являются имена ячеек.

Ссылка может быть не только относительной или абсолютной, но и смешанной.

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

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

Например, в ячейке Е2 таблицы на рис. 12 достаточно было ввести смешанную ссылку G$2.

Использование трехмерных ссылок

Трехмерные ссылки используются при необходимости выполнения действий с данными из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. При этом в формулу включаются все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Январь:Июнь!B2) суммирует все значения, содержащиеся в ячейке B2 на всех листах в диапазоне от Январь до Июнь включительно (рис. 13).

Рис. 13.  Использование трехмерных ссылок

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

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

Практическая часть

Задание1. Создать шаблон для заполнения электронной таблицы, показанный на рисунке.

Алгоритм выполнения задания.

1. Набрать заголовки таблицы, для этого:

1. В ячейке 1 набрать текст Ведомость выдачи заработной платы.

2. Завершить ввод текста нажатием клавиши Enter, или стрелкой вниз ↓, или щёлкнуть левой кнопкой мыши в ячейке А2.

3. В ячейке А2 набрать №.

4. В ячейках А3 и А4 набрать соответственно цифры 1 и 2.

5. Перейти в ячейку В2 стрелками ↓ и → или щёлкнув в ней мышью.

6. Набрать Фамилия, затем в ячейках В3-В10 набрать указанные фамилии.

7. В ячейке С2 набрать Январь.

8. В ячейках С3 по D4 набрать цифры в соответствии с шаблоном.

9. В ячейке H2 набрать Итого, в ячейке В11 набрать Сумма.

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

2. Заполнить таблицу с помощью операции Автозаполнение, для этого:

   1. Выделить диапазон ячеек А3:А4, прокатив по нему указатель мыши (в виде белого креста) с нажатой левой кнопкой.

   2. Установить указатель мыши на правый нижний угол выделенной области, указатель должен принять вид тонкого чёрного креста +, протащить с нажатой левой кнопкой до А10, ячейки заполнятся цифрами до 10.

   3. Выделить ячейку С2, установить указатель мыши на правый нижний угол выделенной ячейки, указатель должен принять вид тонкого чёрного креста +, протащить с нажатой левой кнопкой до ячейки G2, ячейки заполнятся названием месяцев до Мая.

   4. Выделить диапазон ячеек С3:С4, установить указатель мыши на правый нижний угол выделенной области, указатель должен принять вид тонкого чёрного креста +, протащить с нажатой левой кнопкой до ячейки С10, ячейки С3: С10 заполнятся цифровыми значениями.

   5. Выделить диапазон ячеек D3:D4, проделать операцию Автозаполнение как в п. 2.4, заполнив диапазон D5:D10.

   6. Выделить диапазон ячеек С3:D10, проделать операцию Автозаполнение, протащив маркер автозаполнения до ячейки G10, в результате должна быть заполнена вся таблица, кроме строки Сумма и столбца Итого.

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

   1. Выделить ячейку Н3, щёлкнуть по кнопке ∑ (Автосуммирование), расположенной вкладке Главная Редактирование  кнопка ∑

   2. В этой ячейке в строке формул появится формула =СУММ(С3:G3), а диапазон ячеек, используемых в этой формуле, выделяется п3унктирной рамкой. Для закрепления формулы нажать клавишу Enter.

   3. Можно записывать формулу суммы в каждую ячейку столбца, но удобней воспользоваться функцией автозаполнения. Выделить ячейку Н3, проделать операцию автозаполнения для диапазона Н4:Н10. Этот диапазон должен заполниться суммами, соответствующими каждому работнику.

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

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

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

   1. Выделить ячейку С11, щёлкнуть на кнопке ∑ (Автосуммирование), в этой ячейке и строке формул появится формула =СУММ(С3:С10), а диапазон ячеек, используемый в этой формуле выделяется пунктирной рамкой. Для закрепления формулы нажать клавишу Enter.

   2. Диапазон ячеек заполнить формулами с помощью операции автозаполнения.

5. Отформатировать заголовок таблицы, для этого выделить диапазон А1:Н1, щёлкнуть по кнопке Объеденить и поместить в центре

6. Установит отображение данных в денежных единицах (в рублях):

   1. Выделить цифровые данные, т. е. диапазон С3:Н11.

   2. Щёлкнуть кнопку денежный формат на вкладке Главна Число, выбрать числовой формат Денежный.

1. В списке Обозначение выбрать р.

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

   1. Установить указатель мыши на границу заголовков столбцов, например, между С и D, расширить столбец С с нажатой левой кнопкой мыши (аналогично п. 1.10)

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

Задание 2. Создать таблицу, показанную на рисунке.

Алгоритм выполнения задания.

1. Записать заголовок и шапочки таблицы (ячейки А1:Н2).

2. Заполнить боковик таблицы, используя функцию Список.

   1. В ячейку А3 записать Январь.

   2. Выделить ячейку А3, подвести указатель мыши к правому нижнему углу ячейки, указатель примет вид тонкого чёрного креста +, протащить с нажатой левой кнопкой до ячейки А14 (операция Автозаполнения).

3. Заполнить четыре столбца цифровыми данными:

   1. Заполнить две строки указанными на рисунке цифрами.

   2. Выделить диапазон ячеек В3:Е4, выполнить операцию Автозаполнение до строки Итого.

4. Заполнить графу Итого, используя операции Автосумма и Автозаполнение.

5. Рассчитать Среднее в ячейке F3, используя команду Вставка функции.

   1. Выделить ячейку F3, щёлкнуть значок f_x или выполнить команду вкладка ФормулыВставить функция.

   2. В диалоговом окне Мастер функций в поле Категория выбрать Статистические, в правом поле Функция найти и выбрать СРЗНАЧ, нажать ОК.

1. Появится диалоговое окно функции СРЗНАЧ с автоматически подставленным диапазоном В3:F3 в поле Число1 и подсказками, нажать ОК.

1. Заполнить столбец Среднее по Декабрь, используя операцию Автозаполнение.

2. Рассчитать Максимум в ячейке G3, используя команду Вставка функции.

   1. Выделить ячейку G3, щёлкнуть значок f_x или выполнить команду вкладка ФормулыВставить функция..

   2. В диалоговом окне Мастер функций в поле Категория выбрать Статистические, в поле Функция найти и выбрать МАКС, нажать ОК.

   3. Появится диалоговое окно функции МАКС с автоматически подставленным диапазоном B3:F3в поле Число1, этот диапазон неверен, для его исправления:

      1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.

      2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.

3. Заполнить столбец Максимум по Декабрь, используя операцию Автозаполнение.

4. Рассчитать Минимум в ячейкеН3, используя команду Вставка функции.

   1. Выделить ячейку Н3, щёлкнуть значок f_x или выполнить команду вкладка ФормулыВставить функция.

   2. В диалоговом окне Мастер функций в поле Категория выбрать Статистические, в правом поле Функция найти и выбрать МИН, нажать ОК.

   3. Появится диалоговое окно функции МИН с автоматически подставленным диапазоном B3:G3 в поле Число1, этот диапазон неверен, для его исправления:

      1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.

      2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.

5. Заполнить столбец Минимум по Декабрь, используя операцию Автозаполнение.

6. Рассчитать строку 16 Максимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.

7. Рассчитать строку 17 Минимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.