Лабораторная работа 6

«Создание ссылок на ячейки другого листа, форматирование данных и ячеек в Microsoft Office Excel.»

Цель работы: Научиться использовать ссылки на ячейки другого листа по средствам MICROSOFT EXCEL

Образовательные результаты, заявленные во ФГОС третьего поколения:

Студент должен

уметь:

- обрабатывать текстовую и числовую информацию;

- применять мультимедийные технологии обработки и представления информации;

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

знать:

- назначение и виды информационных технологий;

- технологии сбора, накопления, обработки, передачи и распространения информации;

- состав, структуру, принципы реализации и функционирования информационных технологий;

- базовые и прикладные информационные технологии;

- инструментальные средства информационных технологий.

Краткие теоретические и учебно-методические материалы по теме практической работы:

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

Внешние ссылки можно использовать для:

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

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

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

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

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

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

• абсолютные ссылки, которые перед именем столбца и номером строки имеют символ - $. Назначение абсолютной ссылки производится следующим образом: в строке ввода перед ссылкой устанавливается курсор и нажимается клавиша < F4>, например $А$4 Можно сделать то же самое, вводя символ $ с клавиатуры. При копировании абсолютные ссылки остаются неизменными.

• частично абсолютные ссылки, которые при копировании корректируются частично. Символ $ стоит или перед именем столбца, или перед номером строки ($R2, F$5). Например, при копировании формулы, содержащей SF5, сохранится имя столбца F, а номер строки будет изменен;

• имена блоков, например ЦЕНА. Имя связывается с данными блока, а не с его местоположением. Можно блок перенести в другое место, что не повлияет на его имя. Формулы можно копировать в другие ячейки. При этом в зависимости от типа ссылок, входящих в копируемую формулу, осуществляется их корректировка: автоматическая (для относительных ссылок) или полуавтоматическая (для частично абсолютных ссылок).

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

Обработать формулы с циклическими ссылками в режиме обычных вычислений нельзя. Когда вводится формула с циклической ссылкой, появляется предупреждающее сообщение. Если циклическая последовательность ссылок образовалась случайно, нажмите кнопку OK. На экране появится панель инструментов Циклические ссылки и стрелки зависимостей, указывающие на каждую ячейку циклической ссылки. С помощью панели инструментов Циклические ссылки можно провести анализ циклической ссылки и изменить ссылки так, чтобы циклическая ссылка исчезла. Циклические ссылки часто используются в научных и инженерных расчетах.

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

Задания для практического занятия:

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

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

Рисунок 1- Шаблон таблица учета продаж

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

3. Создать аналогичные заготовки таблицы на листах, отображающих расчеты продаж в 1, 2, 3 и 4 кварталах, и итогов продаж за год. Выделить диапазон A1: D9 и скопировать таблицу на другие листы, для чеговыделив указанный диапазон таблицы, выбрать в меню Правка команду Копировать. Затем, указав другой лист, установить курсор в начало в листа, выделив ячейку А1, и вставить таблицу из буфера обмена командой Вставить из меню Правка. Если в книге будет недостаточно листов то командой Лист в меню Вставка вставить недостающий лист.

4. Переименовать листы, задав им названия: 1 квартал, 2 квартал, 3 квартал, 4 квартал, Год.

5. Удалить на листе Год столбец С (Цена), для чего, выделив этот столбец, выбрать в меню Правка команду Удалить.

6. Заполнить таблицы продаж мороженого по кварталам на листах: 1 квартал, 2 квартал, 3 квартал, 4 квартал.

7. В столбец В (Количество) на листе Год ввести формулу, суммирующую количество проданного мороженого по сортам =СУММ(«1 квартал: 4 квартал»! ВЗ), где: «1 квартал: 4 квартал»! - ссылка на диапазон листов; ВЗ — ссылка на ячейку на всех указанных листах.

Эту формулу можно вставить и другим способом: на листе Год указать ячейку В3, в которую вводится функция, ввести знак равенства (=) ввести имя функции СУММ, а затем — открывающую круглую скобку. После этого указать ярлычок листа 1 квартал, и выделить ячейку В3. Затем, удерживая прижатой клавишу Shift, указать последний лист, на который необходимо сослаться, 4 квартал, и ячейку В3, после чего ввести закрывающую скобку. Скопировать формулу =СУММ(«1 квартал. квартал»! В3) из ячейки В3 на листе Год в диапазон В4: В9.

8. В столбец С (Сумма) на листе Год ввести формулу расчета суммы выручки от продаж мороженого по сортам и всего за год =СУММ(«1 квартал: 4 квартал»! D3). Скопировать формулу =СУММ(«1 квартал: 4 квартал»! В3) из ячейки С3 на листе Год в диапазон С4: С9.

9. Поочередно открывая листы: 1 квартал, 2 квартал, 3 квартал, 4 квартал, ввести данные о продажах мороженого разных сортов (количество и цену). Пронаблюдать, как на листе Год суммируются итоги продаж по кварталам.

10 Построить круговую диаграмму, отражающую долю выручки от продажи каждого сорта мороженого за год в % от общей суммы. Выделив диапазон данных А3: С8, выбрать в меню Вставка команду Диаграмма. Следуя указаниям Мастера диаграмм, выбрать Объемный вариант разрезанной круговой диаграммы и щелкнуть кнопку Далее. Затем уточнить диапазон отображаемых данных Год! $А$3: $С$8, указать на отображение рядов данных в столбцах, на вкладке Ряд удалить Ряд 1, оставив для отображения данные только Ряд 2 из столбца с суммой выручки от продаж в столбце С на листе Год. Щелкнув кнопку Далее, задать заголовки диаграммы и включить подписи долей на диаграмме. На последнем шаге диалога с Мастером диаграмм включить размещение диаграммы на имеющемся листе Год и щелкнуть кнопку Готово. Просмотреть полученную диаграмму и уточнить ее позицию на листе.

11 Закрыть окно Excel, сохранив файл под именем Продажа мороженого.

Контрольные вопросы:

1. Для чего используется финансовый формат в ячейках?

2. Что такое ссылка?

3. Что такое внешняя ссылка и для чего она используется?

4. Какие бывают ссылки? Описать каждый вид