Приобретение практических навыков работы с электронной таблицей Microsoft Excel для Windows: Методические указания к лабораторной работе № 3

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

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

Последовательность действий:

-  щелкните по ячейке B2 в окне итоговой таблицы (sale-all.xls), ячейка будет выделена, введите знак = (равно);

-  затем дважды щелкните мышью по ячейке B2 в исходной книге (sale-1.xls) и трижды нажмите клавишу <F4> (чтобы ссылка на ячейку B2 книги sale-1.xls стала относительной);

-  в ячейке B2 итоговой книги и строке формул появится первая половина интересующей нас формулы:

=[sale-1.xls]Лист1!B2;

-  далее в продолжение этой формулы введите знак + (плюс) и дважды щелкните мышью по ячейке B2 в книге sale-2, трижды нажмите клавишу <F4>.

-  в ячейке B2 итоговой таблицы и строке формул появится готовая формула:

=[sale-1.xls]Лист1!B2+[sale-2.xls]Лист1!B2

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

-  нажмите клавишу <Enter>. Формула будет введена в ячейку B2 итоговой таблицы, появится результат вычислений по данной формуле.

Копирование формул

Следующий шаг – создание аналогичных формул для других ячеек таблицы. Чтобы избежать повторения описанных выше операций, необходимо скопировать формулу во все остальные такие ячейки. Это оставшиеся ячейки диапазона B2:D4 книги sale-all.xls.

Последовательность действий:

-  скопируйте в буфер обмена ячейку B2, содержащую формулу;

-  щелкните по ячейке, куда должна быть скопирована формула, например, B3;

-  возьмите содержимое буфера, формула будет скопирована.

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

=[sale-1.xls]Лист1!B3+[sale-2.xls]Лист1!B3.

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

Сохранение рабочей области

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

Последовательность действий:

-  откройте меню Файл и выполните команду Сохранить рабочую область…, появится диалоговое окно;

-  укажите имя файла области (999-all) и папку, куда он должен быть помещен, нажмите кнопку OK;

-  ответьте Да на вопросы о сохранении изменений в других рабочих книгах области (sale-1 и sale-2) и рабочая область будет сохранена.

Задания и порядок выполнения

Задание № 1. Создание таблицы и диаграммы в Microsoft Excel.

1.  Введите данные в таблицу 1.

2.  С помощью кнопки Автосумма на панели инструментов произведите расчет: всего.

3.  Создайте диаграмму (рис. 2) по данным таблицы 1.

4.  Результат покажите преподавателю.

Таблица 1.

Задание № 2. Осуществление подбора параметра

1.  Заполните пустые ячейки таблицы 2 с помощью введения расчетных формул.

2.  Результат заполнения покажите преподавателю.

3.  Установите в ячейке $H$4 значение для получения квартальной  прибыли в объеме не менее $200000, при этом необходимо изменить значение ячейки $C$4.

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

Таблица 2

Задание № 3. Связывание рабочих книг

1.  Создайте еще две таблицы путем копирования в них содержимого таблицы 2. При этом получится три рабочие книги. В третьей книге (итоговой таблице) удалите всю информацию в ячейках B2:D4.

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