Использование ссылок на ячейки в разных рабочих листах
Ссылка на ячейки разных рабочих листов одной и той же рабочей книги осуществляется с указанием имени листа, отделенного восклицательным знаком от относительного адреса ячейки. Например, относительный адрес ячейки А3, находящейся на Листе2 имеет вид: Лист2!А3.
Удобнее создавать ссылки на ячейки разных листов, используя выделение ячеек. Например, для того чтобы в ячейку В2 Листа1 внести ссылку на ячейку С7 Листа2, следует выполнить такие действия:
1) выделить ячейку В2 на Листе1 и нажать знак «=»;
2) перейти на Лист2, щелкнув мышью по его ярлычку;
3) щелкнуть мышью по ячейке С7 и нажать Enter. После этого в снова будет активизирован Лист1 и в ячейке В2 появится формула: =Лист2!С7.
Общая постановка задачи
Выполнить практические задания:
Задание 1. Ввод данных, их редактирование и форматирование, имена ячеек, абсолютные и относительные ссылки.
Задание 2. Сортировка и выборка данных из таблиц, фильтрация и обработка данных, построение сводных таблиц и диаграмм.
Задание 3. Построение диаграмм, гистограмм и анализ данных.
Папку с результатами выполненной работы и файл отчета необходимо заархивировать, создав один файл архива в формате ZIP или 7ZIP. Файлу архива необходимо дать имя: «Ваша фамилия_Лабораторная работа №3» (например: «Иванов_Лабораторная работа №3.zip»).
Задания для практической работы:
Задание 1.
Переименуйте первый лист в «Список товаров». Создайте на первом листе таблицу продаж товаров согласно образцу.
|
|
|
Установите в ячейках, в которых хранятся значение денежных сумм числовой формат «Денежный» с соответствующими единицами измерения. В ячейки Е4:Е13; Н4:Н13;J4:J13; Н14 и J14 соответствующие формулы. В ячейку I1 введите текущую дату (установите числовой формат «Дата»). Выполните вычисление Суммы продаж (I4:I13) учитывая, что некоторые товары были проданы со скидкой. Скидки установить согласно своему варианту работы.
Варианты заданий
| 1. | Скидка: Acer – 3%, LG – 4 %, остальные 0. |
| 2. | Скидка: NEC – 3%, LG – 5%, остальные 0. |
| 3. | Скидка: Samsung – 4%, LG – 6%, остальные 0. |
| 4. | Скидка: SONY – 7%, Samsung – 3%, остальные 0. |
| 5. | Скидка: NEC – 4 %, Acer – 3%, остальные 0. |
| 6. | Скидка: SONY– 5%, NEC – 2%, остальные 0. |
| 7. | Скидка: Samsung – 10%, Acer – 3%, остальные 0. |
| 8. | Скидка: SONY– 2%, Acer – 5%, остальные 0. |
| 9. | Скидка: LG – 4%, NEC – 4 %, остальные 0. |
| 10. | Скидка: Acer – 8%, NEC – 3%, остальные 0. |
Задание 2.
Скопируйте на второй лист исходную таблицу, приведенную ниже и переименуйте лист в «Отдел кадров».
| № пп | Таб. номер | Фамилия | Имя | Отчество | Датарождения | Отдел | Должность | Дата приема на работу | Дата увольнения | Пол | Кол-во иждивенцев | Оклад | ||
| 1 | 00001
| Иванов | Иван | Иванович | 28.10.1952 | Плановый | экономист | 10.01.1996 |
| м | 2 | 1500 | ||
| 2 | 00454 | Иваненко | Иван | Петрович | 21.01.1935 | Бухгалтерия | бухгалтер | 10.04.1998 |
| м | 1 | 3000 | ||
| 3 | 01234 | Петров | Петр | Петрович | 26.08.1970 | Плановый | секретарь | 21.07.1998 |
| м | 2 | 2250 | ||
| 4 | 12312 | Петренко | Петр | Иванович | 14.11.1970 | Маркетинга | менеджер | 10.10.1998 |
| м | 1 | 5250 | ||
| 5 | 12345 | Сидоров | Сидор | Сидорович | 02.02.1971 | Снабжения | менеджер | 10.01.1999 | 10.10.2001 | м | 0 | 3750 | ||
| 6 | 23456 | Седов | Кузьма | Фомич | 23.04.1971 | Плановый | экономист | 12.04.1999 |
| м | 5 | 1500 | ||
| 7 | 34567 | Фомин | Фома | Фомич | 12.07.1985 | Плановый | экономист | 26.07.1995 |
| м | 1 | 1650 | ||
| 8 | 45454 | Фоменко | Сидор | Кузьмич | 30.09.1971 | Бухгалтерия | бухгалтер | 10.11.1999 |
| м | 1 | 4500 | ||
| 9 | 45564 | Кукина | Юлия | Петровна | 19.12.1971 | Бухгалтерия | секретарь | 10.01.2000 | 21.12.2001 | ж | 1 | 2250 | ||
| 10 | 45678 | Макова | Алина | Игоревна | 08.03.1972 | Снабжения | менеджер | 10.04.2000 |
| ж | 1 | 3750 | ||
| 11 | 56565 | Сушкина | Алла | Вадимовна | 17.12.1956 | Плановый | экономист | 10.07.2000 | 12.12.2000 | ж | 1 | 2055 | ||
| 12 | 56786 | Кротова | Инна | Павловна | 21.01.1980 | Снабжения | секретарь | 21.10.1997 |
| ж | 1 | 2250 | ||
| 13 | 56789 | Бойцов
| Семен | Семенович | 26.08.1970 | Бухгалтерия | начальник | 10.01.2001 |
| м | 1 | 4500 | ||
| 14 | 67890 | Гайдай | Иван | Михайлович | 14.11.1970 | Бухгалтерия | бухгалтер | 30.04.2001 |
| м | 1 | 3000 | ||
| 15 | 78787 | Краснов | Павел | Павлович | 02.02.1971 | Плановый | начальник | 10.07.2001 |
| м | 5 | 6000 | ||
| 16 | 78901 | Рябов | Олег | Евгеньевич | 23.04.1971 | Снабжения | начальник | 13.10.2001 |
| м | 1 | 4500 | ||
| 17 | 89012 | Белова | Софья | Петровна | 12.07.1971 | Плановый | экономист | 10.01.2002 |
| ж | 2 | 2550 | ||
| 18 | 90123 | Чернова | Зоя | Богдановна | 30.09.1971 | Маркетинга | начальник | 10.04.2000 |
| ж | 2 | 7500 | ||
| 19 | 98989 | Родионов | Андрей | Вадимович | 19.12.1971 | Маркетинга | секретарь | 10.07.2002 | 30.01.2001 | м | 0 | 2250 | ||
| 20 | 99999 | Хрустов | Юрий | Юрьевич | 08.03.1972 | Маркетинга | менеджер | 10.10.2002 |
| м | 0 | 3750 |
На основании исходных данных, приведенных в таблице, выполнить следующее:
1. Сортировку таблицы по названиям по названиям отделов, расположив их в следующей последовательности по вариантам:
| 1 | "Плановый", "Бухгалтерия", "Маркетинг", "Снабжения" |
| 2 | "Плановый", Маркетинг", "Снабжения", "Бухгалтерия" |
| 3 | "Бухгалтерия", "Плановый", "Снабжения", "Маркетинг" |
| 4 | "Снабжения", "Маркетинг", "Плановый", "Бухгалтерия" |
| 5 | "Маркетинг", "Снабжения", "Плановый", "Бухгалтерия" |
| 6 | "Снабжения", "Бухгалтерия", "Плановый", "Маркетинг" |
| 7 | "Бухгалтерия", "Маркетинг", "Снабжения", "Плановый" |
| 8 | "Бухгалтерия", "Снабжения", "Плановый", "Маркетинг" |
| 9 | "Маркетинг", "Бухгалтерия", "Снабжения", "Плановый" |
| 10 | "Плановый", "Маркетинг", "Бухгалтерия", "Снабжения" |
2. Внутри отдела отсортировать по должности.
|
|
|
3. На листе 3 построить сводную таблицу аналогичную, приведенной в образце (см. рис. Образец 1). Лист 3 переименовать в «Сводные данные».
Рис. 1. Образец 1
В сводной таблице сосчитать количество сотрудников в каждом отделе в соответствии с полом. Поле «Отдел» поместить в фильтр отчета.
Построить сводную диаграмму, в которой отразить величину среднего оклада сотрудников каждого отдела. Вид сводной диаграммы будет аналогичным, приведенным на рис. «Образец .2».
Задание 3.
Переименуйте 4 лист в «Статистика преступлений». Создайте на втором листе таблицу статистики компьютерных преступлений.
Выполните заливку ячеек таблицы в соответствии со своим вариантом.
Варианты заданий
Постройте по данным таблицы диаграмму динамики компьютерных преступлений. Диаграмму поместите на листе «Статистика преступлений». Параметры диаграмм установите согласно своему варианту работы.
Варианты заданий
| № | Тип диаграммы | Положение легенды |
| 1. | Гистограмма | Внизу |
| 2. | Линейчатая | Вверху |
| 3. | График | Справа |
| 4. | Цилиндрическая | Слева |
| 5. | Коническая | Внизу |
| 6. | Гистограмма | Вверху |
| 7. | Линейчатая | Справа |
| 8. | График | Слева |
| 9. | Цилиндрическая | Внизу |
| 10. | Коническая | Вверху |
Согласно своему варианту постройте по данным таблицы гистограмму, отражающую ….
Варианты заданий
| 1. | Количество компьютерных преступлений всех видов в 2010 и 2016 годах |
| 2. | Количество компьютерных преступлений всех видов в 2011 и 2016 годах |
| 3. | Количество компьютерных преступлений всех видов в 2014 и 2016 годах |
| 4. | Количество компьютерных преступлений всех видов в 2010 и 2014 годах |
| 5. | Количество компьютерных преступлений всех видов в 2010 и 2013 годах |
| 6. | Динамику количества хищений путем использования компьютерной техники и количества случаев модификации компьютерной информации в период с 2010 г. по 2016 г. |
| 7. | Динамику количества случаев несанкционированного доступа к компьютерной информации и количества случаев компьютерного саботажа в период с 2010 г. по 2016г. |
| 8. | Динамику количества случаев неправомерного завладения компьютерной информацией и количества случаев компьютерного модификации компьютерной информации в период с 2010 г. по 2016г. |
| 9. | Динамику количества хищений путем использования компьютерной техники и количества случаев разработки вредоносных программ в период с 2010 г. по 2016 г. |
| 10. | Динамику количества случаев несанкционированного доступа к компьютерной информации и и количества случаев разработки вредоносных программ в период с 2010г. по 2016 г. |
Методические пояснения к заданиям
Задание 1.
1. Для переименования листа необходимо выполнить двойной щелчок на его ярлыке и ввести имя.
2. Для оформления заголовка таблицы следует выполнить следующие действия: Выделить диапазон ячеек А1:G1. Выполните команду Формат/Ячеек. На вкладке Выравнивание выберите Объединить ячейки. Затем введите в полученную ячейку «Список товаров» и установите нужные параметры шрифта. Аналогичным образом объедините ячейки для Даты и Курса доллара. Введите в ячейки нужные данные;
3. Измените числовой формат ячейки I1.Для этого выделите ячейку и выберите команду Формат/Ячеек. На вкладке Число выберите Дата (тип любой). Далее введите текущую дату в соответствии с выбранным типом;
4. Измените формат ячейки С1,выбрав Денежный. Число десятичных знаков – 2, Обозначение – р. Затем введите текущий курс доллара;
5. Далее введите в ячейки третьей строки нужный текст. Для изменения ориентации текста в ячейке нужно выполнить Формат/Ячеек. На вкладке Выравнивание установите Ориентацию 900.
6. Для заполнения ячеек поля № целесообразно использовать Маркер заполнения. Введите в А4 – 1, в А5– 2. Затем выделите эти ячейки, установите курсор в правый нижний угол выделенного диапазона и протащите мышь.
7. Заполните ячейки полей Наименование товара, Кол-во прихода, Кол-во продаж и Скидка (согласно своему варианту). Измените числовой формат диапазона ячеек D4:D13 (Денежный. Число десятичных знаков – 0, Обозначение – $ Английский (США).
8. Для вычисления Цены товаров в рублях нужно в ячейки Е4:Е13 соответствующие формулы. Сначала введите в ячейку Е4 формулу =D4*$С$2. Зафиксируйте ввод (Enter). Затем с помощью маркера заполнения скопируйте это формулу в ячейки Е5:Е13. Обратите внимание, что в формуле ссылка на ячейку, где хранится значение курса доллара – абсолютная. Т.к. она не должна меняться при копировании формулы.
9. Для вычисления Суммы продаж введите в ячейку Н4 формулу: =G4*Е4*(1–F4) и скопируйте её (с помощью маркера заполнения)в другие ячейки поля Сумма продаж. Самостоятельно вычислите Количество остатка и Сумму остатка (вычисляя Сумму остатка, скидка не учитывать). Для вычисления общей суммы продаж нужно в ячейку Н14 ввести формулу с функцией суммирования =СУММ(H4:H13). Для ввода функции суммирования можно использовать мастер функций или кнопку . Аналогично подсчитайте Сумму остатка всех товаров.
10. Для обрамление таблицы её следует выделить и выполнить команду Формат/Ячейки, выбрать закладку Граница, в группе линии выбрать тип линии. И выбрать внешние и внутренние границы.
Задание 2.
Отчет сводной таблицы используется для обобщения, анализа, изучения и представления итоговых данных, а отчет сводной диаграммы для наглядного отображения итоговых данных в отчете сводной таблицы и упрощения поиска сравнений, закономерностей и тенденций. Эти отчеты позволяют принимать более обоснованные решения относительно важных данных организации.
1. На вкладке Вставка в группе Таблицы нажмите кнопку Сводная таблица.
2. В диалоговом окне Создание сводной таблицы выберите вариант Выбрать таблицу или диапазон и проверьте правильность диапазона ячеек в поле Таблица или диапазон.
3. Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки A1, щелкните элемент На новый лист.
4. Нажмите кнопку ОК. Microsoft Excel добавит пустой отчет сводной таблицы в указанное место и откроет список полей сводной таблицы, с помощью которого можно добавить поля, создать макет и настроить отчет.
5. В списке полей сводной таблицы выполните одно или несколько из указанных ниже действий.
1. Чтобы поместить поле в область по умолчанию раздела макета, установите флажок напротив имени данного поля в разделе полей.
2. Чтобы поместить поле в определенную область раздела макета, щелкните правой кнопкой мыши имя соответствующего поля в разделе полей и выберите команду Добавить в фильтр отчета, Добавить в названия столбцов, Добавить в названия строк или Добавить в значения.
3. Чтобы перетащить поле в нужную область, щелкните имя поля в разделе полей и, удерживая кнопку нажатой, перетащите его в нужную область раздела макета/
Задание 3.
Выделите ячейки А3:Н9 таблицы с данными и перейдите на вкладку Вставка диаграмм. Выберите в соответствии с вариантом тип диаграммы . На вкладке Макет выберите область Легенды в соответствии с вариантом задания.
Далее для построения гистограммы, отражающей количество компьютерных преступлений или динамику количества хищений в разных годах воспользуйтесь предыдущим советом построения диаграмм, а затем перейдите на вкладку Ряд и в поле Ряд оставите только 2 ряда данных за нужные вам годы.
Контрольные вопросы к защите:
1. В каких случаях необходимо использовать абсолютный адрес? В чем отличие абсолютного и относительного адреса ячейки?
2. Что такое мастер функций? Какие бывают категории функций?
3. Чем отличается в Excel сортировка от фильтрации? Для чего нужны сводные таблицы?
4. Какие типы диаграмм существуют в Excel-2010? Что такое легенда диаграммы? Как выделить выборочный диапазон значений для построения диаграммы?
Дата добавления: 2019-09-13; просмотров: 949; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!