MS EXCEL. Практическое занятие № 4.

MS EXCEL. Практическое занятие № 4.

Работа с экономической информацией.

Сортировка и фильтрация списков.

Содержание

Условие задачи.. 1

Создание таблицы.. 1

Графическая интерпретация экономических данных.. 4

Технология структурирования и отбора данных с использованием табличного процессора. 6

Подведение промежуточных итогов. 7

Фильтрация записей списка с использованием автофильтра. 9

Фильтрация записей списка с использованием расширенного фильтра. 10

Структурирование данных.. 12

Самостоятельная работа. 14

Условие задачи

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

Создание таблицы

Необходимо построить таблицу "Ведомость расчета потребности в деталях" и диаграмму себестоимости выпуска по кодам деталей.

Для создания таблицы выполните следующие действия:

1. Запустите MS EXCEL.

2. Установите курсор на ячейку А1.

3. Наберите заголовок таблицы в одну строчку – «Ведомость расчета потребности в деталях».

4. Подтвердите набор заголовка нажатием клавиши Еntег.

5. Выделите мышкой диапазон ячеек А1:Е1 и нажмите кнопку Объединить и поместить в центре .

Таблица 1. Ведомость расчета потребности в деталях

Ведомость расчета потребности в деталях
Код детали	Код изделия	Потребность в деталях, шт.	Себестоимость, руб./шт.	Себестоимость выпуска, руб.
			120,00
			500,00
			450,00
			500,00
			450,00
			120,00
			500,00
			450,00
			500,00
			500,00
			120,00
			450,00
			500,00
Итого

6. Нажмите кнопку с символом Ж (полужирный шрифт). В результате вы создадите заголовок таблицы.

7. Выделите мышкой диапазон ячеек А2:Е2.

8. Выполните команду Формат – Ячейка.

9. Откройте закладку Выравнивание.

10. В окне Выравнивание в позиции по горизонталивыберите по центру.

11. В окне Выравнивание в позиции по вертикали выберите по верхнему краю.

12. Включите опцию переносить по словам (Рис. 1).

Рис. 1. Установка параметров для заголовков

13. Нажмите кнопку ОК.

14. Введите заголовки столбцов.

15. Расширьте столбцы по необходимости. Для этого установите курсор мыши на границе столбца, нажмите кнопку и, удерживая ее, растягивайте или сужайте столбец до необходимого размера.

16. Введите остальные данные по образцу.

17. Для установки необходимого числа цифр после запятой выделите значения столбца «Себестоимость, руб./шт.».

18. Выберите кнопку Увеличить разрядность или Уменьшить раз­рядность на панели инструментов Форматирование и щелкайте кнопкой мыши до достижения необходимой разрядности.

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

1. Поставьте указатель мыши на ячейку E3.

2. Введите формулу =C3*D3 (на английском языке). Обратите внимание на то, что формула начинается со знака равно, а не заканчивается им. В результате у вас получится следующий вид таблицы (Рис. 2).

Рис. 2. Исходная таблица в MS EXCEL

3. Нажмите клавишу Enter.

4. Для многократного копирования ячейки выделите E3.

5. Установите указатель мыши в правом нижнем углу ячейкиE3.

6. При появлении крестика, удерживая кнопку мыши в нажатом стоянии, перемещайте курсор до необходимой ячейки (Рис. 3).

Рис. 3. Вид курсора при копировании

Создайте обрамления таблицы. Для чего:

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

2. На панели инструментов Форматирование нажмите кнопку Границы и выберите тип Все границы (Рис. 4).

Рис. 4. Меню кнопки Границы

В результате выполненных действий у вас должна появиться следующая таблица (Рис. 5).

Рис. 5. Таблица с расчетами

Для сохранения созданной таблицы проделайте следующие шаги.

1. Выполните команду Файл – Сохранить.

2. В открывшемся окне в поле Имя файла наберите свою фамилию.

3. Нажмите кнопку ОК.

Структурирование данных

Для структурирования данных создайте новую таблицу, на основании данных Деталь-Сорт.

1. Добавьте новый лист командой Вставка – Лист.

2. Скопируйте основную таблицу Деталь-Сорт на Лист 5.

3. Переименуйте Лист 5 на Деталь-Свод.

4. Установите курсор в начальную ячейку цифровых данных (А3).

5. Выполните команду Данные - Сводная таблица.

6. В появившемся окне включите опцию В списке или базе данных Microsoft Office Ехсеl, а затем нажмите кнопку Далее.

7. В следующем окне (Рис. 18) в поле Диапазон указан диапазон таблицы.

Рис. 18. Второй шаг построения сводной таблицы

8. Если изменения диапазона не требуются, то нажмите кнопку Далее.

9. В следующем окне нажмите кнопку Готово.

10. Для построения макета сводной таблицы на панели Список полей сводной таблицы выберите раздел Код детали и перетащите его в поле, указанное стрелкой (см. рис. 19).

11. Перетащите кнопку Себестоимость выпуска (руб.) в поле Данные.

12. Щелкните два раза левой кнопкой мыши на кнопке Сумма по по­лю Себестоимость выпуска, руб.

13. В открывшемся окне в поле Операция выберите Суммаи нажмитеОК.

14. С помощью кнопки Скрыть список полейна панели инструментов Сводные таблицы отключите неиспользо­ванные поля.

Рис. 19. Построения структуры сводной таблицы

15. Закройте панель Сводные таблицы. У вас должна получиться подобная таблица:

Рис. 20. Построение структуры сводной таблицы

9. Самостоятельная работа

В коммерческой фирме эксплуатируются 15 персональных компьютеров. Их характеристики представлены в таблице 2.

Таблица 2. Параметры персональных компьютеров

Инв. №	Тип ПК	Кэш-память (Кб)	ОЗУ (Мб)	HDD (Гб)	Балансовая стоимость (руб.)
Инв. 002	Pentium 1				4800,00
Инв. 010	Pentium 3				12200,00
Инв. 012	Pentium 3				16520,00
Инв. 010	Pentium 3				12200,00
Инв. 001	Pentium 1				4600,00
Инв. 015	Pentium 4				22468,00
Инв. 013	Pentium 3				18200,00
Инв. 003	Pentium 1				5200,00
Инв. 004	Pentium 1				5450,00
Инв. 005	Pentium 2				6740,00
Инв. 006	Pentium 2				5800,00
Инв. 007	Pentium 2				7300,00
Инв. 008	Pentium 2				7600,00
Инв. 009	Pentium 2				9800,00
Инв. 011	Pentium 3				14840,00
Инв. 014	Pentium 4				21460,00

1. Выполните сортировку списка таблицы последовательно по следующим столбцам:

• Инв №;
• Тип ПК;
• ОЗУ.

2. Выполните графическую интерпретацию балансовой стоимости ПК.

3. Скопируйте исходную таблицу на новый лист. Осуществите выборку записей с использованием Автофильтра, выбрав ПК с емкостью жесткого диска 80 Гбайт (HDD).

4. Отфильтруйте все ПК, стоимость которых превышает 10000 руб.

5. Скопируйте исходную таблицу на новый лист. Осуществите выборку записей с использованием Расширенно­го фильтра всех ПК, удовлетворяющих условиям:

HDD > 20 Гб, Балансовая стоимость > 10 000 руб.

6. Скопируйте исходную таблицу на новый лист. Выполните подведение итогов при изменении типа ПК.

7. Создайте сводную таблицу, состоящую из типа ПК и итогов балансовой стоимости.

MS EXCEL. Практическое занятие № 4.