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

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

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

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

2. Запустить Excel, переименовать Лист 1 в КТУ, на листе КТУ описать структуру таблицы и ввести исходные данные.

3. Ввести в ячейку D4 формулу расчета КТУ =В4*С4. Скопировать формулу из ячейки D4 в диапазон D5:D7.

4. В ячейку D8 ввести формулу суммы диапазона D4:D7, для чего выделив диапазон D4:D7, щелкнуть кнопку Автосумма на панели инструментов Стандартная.

5. Ввести в ячейку С2 величину распределяемого дохода, например, 10000.

6. Ввести в ячейку Е4 формулу расчета выплаты =$C$2/$D$8*D4. (Выплата каждому работнику равна частному от всей суммы доходов коллектива и суммы КТУ всех работников, умноженному на величину КТУ данного работника.) Обратите внимание, что адреса ячеек С2 и D8 записаны в абсолютной форме, т. е. они не изменятся при копировании формулы из ячейки Е4 в Е5:Е7.

Исходные данные

	A	B	C	D	E
1	Распределение доходов в коллективе
2	Сумма доходов
3	фамилия	Разряд	Время	КТУ	Выплата
4	Иванов	12	5
5	Петров	14	6
5	Сидоров	11	8
7	Костин	10	7
8	ИТОГО

Примечание. Для создания абсолютной ссылки на ячейку С2, D8 нужно при вводе формулы щелкнуть на данной ссылке клавишей F4.

7. Задать финансовый формат для значений данных в ячейках Е4:Е7, для чего выделив этот диапазон, выбрать в меню Формат опцию ячейки, а затем на вкладке Число окна Формат ячеек выбрать вариант числового формата: Финансовый, после чего указать число десятичных знаков 2, а в поле Обозначение выбрать р. и щелкнуть кнопку ОК.

8. Изменить ширину столбца Е, чтобы в нем правильно отображались выплаты в финансовом формате.

9. Проверить действие таблицы, изменяя данные в ячейках С2, В4:С7.

10. Построить диаграмму, отображающую долю выплат каждому работнику от общей суммы доходов. Для этого выделить диапазон ячеек A4:D7 и щелкнуть кнопку Мастер диаграмм в панели инструментов Стандартная. В начале построения диаграммы выбрать тип Разрезная круговая диаграмма и щелкнуть кнопку Далее. На этапе определения источника данных диаграммы выбрать для отображения в диаграмме данные из рядов в столбцах, уточнить диапазон. На вкладке Ряд в поле Имя задать диапазон данных, отображающих название Лист1!$А$1. Щелкнув кнопку Далее, на вкладке Подписи данных в поле Подписи значений выбрать опцию доля. Щелкнув кнопку Далее, выбрать место размещения диаграммы на отдельном листе с названием Диаграмма 1 и щелкнуть Готово.

11. Изменить формат области диаграммы, для чего щелкнуть правой кнопкой мыши на диаграмме, в контекстном меню выбрать команду Формат области диаграммы. Выбрав вкладку Вид, щелкнуть кнопку Способы заливки. В окне Заливка выбрать вкладку Текстура, затем щелкнуть кнопку с текстурой (например, белый мрамор). Щелкнув кнопку ОК, закрыть окно Заливка, а затем щелкнуть ОК для закрытия окна Формат области диаграммы. Просмотреть вид полученной диаграммы на отдельном листе Диаграмма 1

12. Сохранить таблицу с диаграммой под именем КТУ и закрыть окно программы Excel.

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

Например в текстовом документе Word можно подготовить финансовый отчет, основанный на электронной таблице Excel. Если вы установите динамическую связь между Word и Excel, то в дальнейшем сможете обновлять отчет непосредственно в электронной таблице, и все изменения будут автоматически воспроизведены в текстовом документе.

Технология динамического обмена данными

(DDE) между Excel и другими приложениями Windows

1. Запустить Excel и создать таблицу отчета о продажах. Сохранить файл на диске под именем Отчет1.

2. Запустить программу Microsoft Word и создать новый документ. Включить в документ файл таблицы Excel Отчет1, установив динамическую связь между Word и Excel. Для этого выбрать в меню Вставка команду Объект. На вкладке Создание из файла включить опцию Связь с файлом и щелкнуть кнопку Обзор для поиска файла. В диалоговом окне Обзор найти папку, файл таблицы Excel Отчет1 и щелкнуть кнопку ОК. После этого в документ Word будет вставлена таблица.

Финансовый отчет о продажах
Наименование	Количество	Цена	Сумма
Принтер Epson LX-300	2	3 987,36 р.	7 974,72 р.
Сканер Mustek ScanExpress	5	1 621,00 р.	8 105,00 р.
Сетевая карта 3 Com	8	1 014,00 р.	8 112,00 р.
Картридж для струйных принтеров	12	456,00 р.	5 472,00 р.
Итого	27		29 663,72 р.

3. Перейти в окно Excel и изменить данные в таблице Отчет 1 (например, изменить данные в графах Количество и Цена).

4. Перейти в окно Word и убедиться в том, что изменения, внесенные в окне Excel в файл таблицы Отчет 1, отображаются и в динамически связанной с ним таблице, вставленной в документ Word.

5. Завершить работу приложений Excel и Word с сохранением документов.

Обработка списков в Excel.

1. Запустить Excel и просмотреть в справке информацию об использовании списков. Для этого, вызвав справку Excel на вкладке Содержание нужно выбрать тему Управление списками. изучить справочную информацию об особенностях размещения списка на листе книги, о поиске в списке.

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

3. Описать структуру таблицы и заполнить ее данными, как показано в таблице.

Исходные данные.

	А	В	С	D	Е	F
1	Список участников соревнований
2	Фамилия	Имя	Вес	Регион	Очки	Место
3	Власов	Василий	97	Москва	6	1
4	Знаков	Евгений	54	Самара	8	1
5	Знаев	Владимир	76	Ульяновск	5	1
6	Кадышев	Петр	130	Самара	3	1
7	Карелин	Александр	130	Новосибирск	5	1
8	Сайфутдинов	Руслан	54	Курган	7	1
9	Сердюков	Борис	130	Воронеж	4	2
10	Чеглаков	Алексей	97	Пермь	5	2
11	Шевченко	Михаил	76	Ростов	6	1

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

5. Найти участников соревнований из Самары. Для этого в меню Правка выбрать команду Найти, затем в окне Найти в поле Что ввести образ поиска «Самара», задать опции поиска: не учитывать регистр, просматривать по строкам, и щелкнуть кнопку Найти далее. Курсор выделяет ячейку с найденным текстом. Щелчок кнопки Найти далее переводит курсор на следующую ячейку таблицы, удовлетворяющую условиям поиска. Если данных нет, то на экране будет выведено сообщение об этом. Закрыть окно поиска.

6. Отфильтровать список спортсменов и просмотреть участников состязаний в весовой категории до 130 кг. Для этого следует выбрать команду Фильтр в меню Данные, а затем выбрать опцию Автофильтр. После этого в правой части заголовков всех граф таблицы появятся стрелки. Выбрав графу Вес, нажмите кнопку со стрелкой, следует выбрать значение в списке, например 130.

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

Можно применить фильтр к уже отфильтрованному списку. Например, если, щелкнув стрелку в графе Имя, установить фильтр Александр, то в таблице будут отображаться только записи о спортсменах, у которых Вес=130 и Имя=«Александр».

7. Удалить фильтр Имя=«Александр», для чего следует нажать кнопку со стрелкой в графе Имя, а затем выбрать из развернувшегося списка Все.

Чтобы удалить фильтры для всех столбцов списка, нужно выбрать пункт Фильтр в меню Данные, а затем —- команду Отобразить все. Для удаления автофильтра выберите пункт Фильтр в меню Данные, а затем — команду Автофильтр.

8. Для просмотра записей таблицы с использованием формы выбрать команду Форма в меню Данные. После этого на экране раскроется форма с отображением записей таблицы, как показано на рисунке.

Для получения подсказки о назначении какого-либо параметра формы нажмите кнопку «?» в правом верхнем углу окна и щелкните интересующий вас параметр.

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

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

9. Для того чтобы задать фильтр отбора данных, нужно щелкнуть кнопку Критерии, затем в поле Регион ввести значение «Самара» и нажать Enter.

После этого в форме будут отображаться только данные об участниках из Самары.

Для просмотра остальных записей, удовлетворяющих условиям отбора Регион=«Самара» используйте кнопки Далее и Назад.

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

11. Для вставки записи в таблицу укажите ячейку списка, начиная с которой следует добавлять записи (например, 5), затем щелкнуть кнопку Добавить и ввести значения полей новой записи, используя клавишу Tab для перемещения к следующему полю. Задать следующие данные новой записи: Петров, Василий, 97, Тула, 5, 2. Завершить ввод данных, нажав Enter. Обратите внимание, что количество записей в таблице, отображаемое над кнопкой Добавить, увеличилось на 1.

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

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

13. Для удаления записи об участнике из Ульяновска установить ее текущей, а затем щелкнуть кнопку Удалить. Подтвердить удаление записи, щелкнув кнопку ОК на панели сообщения Microsoft Excel.

Примечание. Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши Enter или кнопки Закрыть.

14. Нажать кнопку Закрыть для выхода из формы и просмотреть изменения, внесенные в нее в режиме формы.

15. Завершить работу Excel, сохранив таблицу под именем Список участников соревнований.

Задание

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

2. Составить таблицу – список членов бригады. В таблице должны быть заполнены поля: Ф.И.О., год и дата рождения, дата поступления на работу, оклад, вычислены с помощью формул: стаж, надбавка за стаж (от 3 до5 лет – 5%, от5 до 10 лет – 10%, свыше 10 лет – 15%), заработная плата за месяц. Найти минимальную и максимальную зарплату в виде промежуточного итога.

3. Составить таблицу - список группы учащихся с результатами экзамена по информатике. Определить средний балл успеваемости группы по информатике. Используя фильтр, вывести список учащихся, получивших на экзамене «4» и «5».