Различные надстройки, мастера, шаблоны, например Мастер суммирования, подстановок, шаблонов, Мастер обновления связей, надстройка Пакет анализа и др

8.    Различные надстройки, мастера, шаблоны, например Мастер суммирования, подстановок, шаблонов, Мастер обновления связей, надстройка Пакет анализа и др.

Средства Excel для работы с данными списка. Электронная таблица, все строки которой содержат однородную информацию, рассматривается как список или база данных. При этом:

¨            каждая строка списка рассматривается как запись базы данных;

¨            столбцы списков считаются полями базы данных;

¨            заголовки столбцов считаются именами полей базы данных;

¨            все строки таблицы должны содержать однородную информацию – во всех строках в одинаковых столбцах должны находиться однотипные данные;

¨            заголовки столбцов должны находиться в первом столбце списка;

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

Сортировка данных. Сортировка осуществляется непосредствен в электронной таблице. Строки, столбцы и ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем порядке (от 1 до 9, от А до Я) или в убывающем порядке (от 9 до 1, от Я до А). Сортировать можно как текстовые, так и числовые данные. Если в сортируемом столбце содержатся и текстовые и числовые данные, то после сортировки числовые значения будут расположены перед текстом. Сортировка данных в электронных таблицах производятся с помощью команд меню ДАННЫЕ – Сортировка или с помощью кнопок [А^Я↑], [Я^А↓] на Стандартной панели инструментов.

В диалоговом окне Сортировка диапазона указывают требуемые параметры – тип и порядок сортировки (рис.19)

 

рис. 19

☺ Перейдем к работе со списками. Полями нашей базы данных будут:

¨       Категория – вид продаваемой продукции;

¨       Товар – название товара;

¨       Модель – Модель товара;

¨       Цена (нал) – стоимость товара при наличном расчете;

¨       Цена (б\н)стоимость товара при безналичном расчете.

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

Автозавершение.

1.   На листе Список товаров выделите диапазон С9:G9.

2.   Последовательно в каждую ячейку этого диапазона вводите данные:

Компьютер, Персональный, Gamer’s Dream, 1200, =F9+F9*$H$5.

3.   Выделите диапазон С10:F10.

4.   Введите K. Excel предложит закончить слово «Компьютер». Позвольте это программе, нажав клавишу Enter.

5.   Введите П. Excel предложит закончить ввод словом «Персональный». Нажмите клавишу Enter.

6.   В оставшиеся ячейки введите Master Class, 1450.

7.   Выделите диапазон С11:F11.

8.   Последовательно в каждую ячейку этого диапазона вводите данные: Периферия, Принтер, Epson LQ, 123

9.   Выделите диапазон С12:F12.

10.Введите П. Excel предложит закончить ввод словом «Периферия». Нажмите клавишу Enter.

11.Аналогично введите «Принтер».

12.Закончите ввод записи, заполнив оставшиеся ячейки значениями Lexmark 5700, 279.

Попробуем, что же такое выбор из списка.

13.Выделим диапазон С13:F13.

14.Нажмите сочетание клавиш Alt + Стрелка вниз. В раскрывшемся списке выделите пункт Периферия и нажмите клавишу Enter. Нажмите еще раз клавишу Enter, чтобы перейти к нижней ячейке.

15.Щелкните правой кнопкой мыши на ячейке D13. В появившемся окне меню выберите команду Выбрать из списка и в появившемся списке выберите пункт Принтер. Нажмите клавишу Enter, чтобы перейти к следующей ячейке.

16.Нажмите комбинацию клавиш Alt + Стрелка вниз. В раскрывшемся списке щелкните на пункте Epson LQ100.

17.Щелкните в строке формул и замените LQ100 на LX300. Нажмите Enter и перейдите к следующей ячейке.

18.Введите 137.

Чтобы потренироваться в работе со списками, продолжайте ввод данных.

19. Введите еще 10 записей, используя возможности Автозавершения и выбора из списка:

Компьютер, Сервер, WorkMem, 2556;

Компьютер, Сервер, BigBlue, 6521;

Периферия, Факс-Модем, USR 33600 int, 58;

Периферия, Факс-Модем, USR 33600 ext, 68;

Периферия, Клавиатура, Genius, 9;

Периферия, Клавиатура, ВТС 5739, 25;

Периферия, Сканер, Acer 610 PT, 195;

Комплектующие, Винчестер, 3.2Гб IDE Seagate, 128;

Комплектующие, Винчестер, 6.4Гб IDE Quantum FB_EX, 161;

Комплектующие, Винчестер, 9.1Гб UW SCSI_IBM, 499.

Cкопируйте формулу из ячейки G9,заполнив поле Цена (б\н) для всех записей. Теперь рабочий лист готов (см. рис. 20).

рис.20

 

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

Фильтрация данных с помощью автофильтра:

1.   установить курсор внутри таблицы;

2.   ввести команду меню ДАННЫЕ – Фильтр – Автофильтр;

3.   щелчком мыши по кнопке со стрелкой [▼] раскрыть список столбца, по которому будет производится выборка;

4.   указать требуемые значения или выбрать строку ”условие” и задать критерии выборки в диалоговом окне Пользовательский автофильтр.

 

рис. 21 использование автофильтра для отбора записей по признаку «М» -(мужчина)

 

рис. 22 рабочий лист после фильтрации списка по признаку «мужчина»

 

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

¨                критериев сравнения;

¨                вычисляемых критериев.

Фильтрация данного типа выполняется с помощью команды меню ДАННЫЕ – Фильтр – Расширенный фильтр. Особенностью этого режима является, то что до выполнения самой команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора. После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕ – Фильтр – Расширенный фильтр и в диалоговом окне Расширенный фильтр указывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазон условий.

Для восстановления всех строк исходной таблицы и отмены режима фильтрации следует ввести команду меню ДАННЫЕ – Фильтр – Отобразить всё.

Структура электронной таблицы. Excel позволяет организовать данные электронной таблицы в виде структуры. С помощью структуры можно скрывать (убирать с экрана) менее существенные детали и отображать только тот объем информации, который необходим в текущий момент.

В Excel можно создавать до 8 уровней структуры. Для каждого рабочего листа можно создать только одну структуру.

Структура таблицы создается автоматически после выполнения таких команд меню ДАННЫЕ, как Итоги, Консолидация, Сводная таблица.

Структурировать таблицу можно и самостоятельно с помощью команд меню ДАННЫЕ – Группа и структура.

 Автоматическое структурирование таблицы выполняется с помощью команды меню ДАННЫЕ – Группа и структура – Создание структуры. Перед вводом этой команды необходимо выделить диапазон ячеек – область, для которой требуется создать структуру. После выполнения этой команды будут отображены в развернутом виде все существующие структуры.

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

В Excel имеется несколько способов консолидации данных

¨          консолидация данных по расположению – используется для однотипных данных, упорядоченных одинаковым образом;

¨          консолидация данных по категориям – используется для однотипных данных, организованных в различных областях - источниках по–разному;

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

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

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

Анализ и обобщение данных с помощью сводных таблиц и сводных диаграмм.

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

Так же, сводные таблицы и сводные диаграммы можно создавать:

¨          на основе таблиц, полученных в результате консолидации данных;

¨          на основе других сводных таблиц;

¨          на основе внешних источников данных.

Необходимым условием для создания сводной таблицы или сводной диаграммы является наличие одной или нескольких таблиц, содержащих заголовки столбцов. Заголовки столбцов служат для создания в них полей данных. Создание и модификация сводных таблиц и сводных диаграмм выполняются с помощью Мастера сводных таблиц и диаграмм, окно которого появляется на экране после ввода команды меню ДАННЫЕ – Сводная таблица или щелчка по кнопке Мастер сводных таблиц на панели инструментов Сводные таблицы.

Средства условного анализа (“что, если”) используются для исследования различных вариантов решения задач, поиска и выбора оптимального решения.

К средствам условного анализа относятся:

¨          подбор параметра;

¨          поиск решения;

¨          сценарии;

¨          таблицы подстановки.

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

При выполнении этой операции следует иметь в виду, что:

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

¨          ячейка, которая будет изменяться при подборе, должна, наоборот, содержать значение, а не формулу.

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

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

Excel позволяет создавать таблицы подстановки различных типов:

¨          таблицы подстановки с одной переменной (с одной или несколькими формулами);

¨          таблицы подстановки с двумя переменными.

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

☺ Упражнение на сортировку и фильтрацию.

20.На листе Список товаров выделите какую – нибудь ячейку списка, например С8.

21.Выполните команду Данные- Фильтр – Автофильтр. У ячеек с названиями полей появились кнопки, раскрывающие списки значений.

22.Выберите все записи, в которых поле Категория принимает значение «Периферия». Для этого нажмите кнопку поля Категория и в появившемся списке выберите пункт Периферия.

23.В оставшейся части выберите всю информацию о принтерах. Для этого нажмите кнопку поля Товар и в появившемся списке выберите пункт Принтер.

24.Выполните более сложную фильтрацию. Выберите в списке такие принтеры, цена которых не превышает 200 у.е. Для этого нажмите кнопку поля Цена (нал) и выберите пункт Условие.

25.В появившемся окне диалога Пользовательский автофильтр в списке первого поля выберите пункт меньше или равно, а во втором поле со списком введите 200.

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

27.Самостоятельно выберите из списка такие компьютеры, цена которых меньше 200 у.е.

28.Включите фильтрацию, выполнив команду Данные – Фильтр – Автофильтр.

29.На листе список товаров выделите какую-нибудь ячейку списка, например С8.

30.Выполните команду Данные – Сортировка. Появится окно диалога Сортировка диапазона.

31.Отсортируем список по полю Категория. Для этого нужно выбрать пункт Категория в списке Сортировать по окна диалога Сортировка диапазона.

33. Нажмите кнопку ОК. Теперь список отсортирован по выбранному полю в алфавитном порядке.

34. Отсортируем список по двум полям одновременно так, чтобы записи полей Категория и Товар шли в алфавитном порядке, а цены на соответствующие товары шли по убыванию. Для этого вызовите еще раз окно диалога Сортировка диапазона.

35. В окне диалога в списке Сортировать по выберите пункт Категория, а в списке Затем по – пункт Товар.

36. Во втором списке В последнюю очередь по выберите пункт Цена (нал), а переключатель рядом с этим списком установите в положение По убыванию.

37. Нажмите кнопку ОК. Теперь список отсортирован.

Задачи для самостоятельной работы

Попробуйте самостоятельно решить предложенные ниже задачи путем построения электронной таблицы. Исходные данные для заполнения таблицы подберите самостоятельно (не менее 10 строк).

Задача 1

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

Задача 2

Каждому пушному зверьку в возрасте от 1-го до 2-х месяцев полагается дополнительный стакан молока в день, если его вес меньше 3 кг. Количество зверьков, возраст и вес каждого известны. Выяснить сколько литров молока в месяц необходимо для зверофермы. Один стакан молока составляет 0,2 литра.

Задача 3

Если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.

Задача 4

В доме проживают 10 жильцов. Подсчитать, сколько каждый из них должен платить за электроэнергию и определить суммарную плату для всех жильцов. Известно, что 1 кВт/ч электроэнергии стоит m рублей, а некоторые жильцы имеют 50% скидку при оплате.

Задача 5

Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то - в 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.

Задача 6

В сельскохозяйственном кооперативе работают 10 сезонных рабочих. Собирают помидоры. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет k килограммов. Сбор 1 кг помидоров стоит m рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?

Задача 7

Если количество баллов, полученных при тестировании, не превышает 12, то это соответствует оценке "2"; оценке "3" соответствует количество баллов от 12 до 15; оценке "4" - от 16 до 20; оценке "5" - свыше 20 баллов. Составить ведомость тестирования, содержащую сведения: фамилия, количество баллов, оценка.

Задача 8

Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: k рублей за 1 Квт/ч и m рублей за каждый Квт/ч сверх нормы, которая составляет 50 Квт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

Задача 9

10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастера, если он набрал в сумме не менее k очков. Сколько спортсменов получило звание мастера?

Задача 10

10 учеников проходили тестирование по 5 темам какого-либо предмета. Вычислить суммарный (по всем темам) средний балл, полученный учениками. Сколько учеников имеют суммарный балл ниже среднего?

Задача 11

Билет на пригородном поезде стоит 5 монет, если расстояние до станции не больше 20 км; 13 монет, если расстояние больше 20 км, но не превышает 75 км; 20 монет, если расстояние больше 75 км. Составить таблицу, содержащую следующие сведения: пункт назначения, расстояние, стоимость билета. Выяснить сколько станций находится в радиусе 50 км от города.

Задача 12

Телефонная компания взимает плату за услуги телефонной связи по следующему тарифу: 370 мин в месяц оплачиваются как абонентская плата, которая составляет 200 монет. За каждую минуту сверх нормы необходимо платить по 2 монеты. Составить ведомость оплаты услуг телефонной связи для 10 жильцов за один месяц.

Задача 13

Покупатели магазина пользуются 10% скидками, если покупка состоит более, чем из пяти наименований товаров или стоимость покупки превышает k рублей. Составить ведомость, учитывающую скидки: покупатель, количество наименований купленных товаров, стоимость покупки, стоимость покупки с учетом скидки. Выяснить сколько покупателей сделало покупки, стоимость которых превышает k рублей.

Задача 14

Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: k1 рублей за 1 кВт/ч за первые 500 кВт/ч; k2 рублей за 1 кВт/ч, если потребление свыше 500 кВт/ч, но не превышает 1000 кВт/ч; k3 рублей за 1 кВт/ч, если потребление свыше 1000 кВт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента и суммарную плату. Сколько клиентов потребляет более 1000 кВт/ч.

Задача 15

При температуре воздуха зимой до -20°С потребление угля тепловой станцией составляет k1 тонн в день. При температуре воздуха от -30°С до -20°С дневное потребление увеличивается на 5 тонн, если температура воздуха ниже -30°С, то потребление увеличивается еще на 7 тонн. Составить таблицу потребления угля тепловой станцией за неделю. Сколько дней температура воздуха была ниже -30°С? (Постройте диаграмму).

Заключение

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

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

Надеюсь, что полученные знания ни раз пригодятся вам в учебной и профессиональной деятельности.

Список литературы

1.   В. Рычков. Самоучитель «Изучите Excel 2000 самостоятельно». С.-Петербург, 1999.-457с.

2.   Дж. Вейсскопф. Excel 2000 базовый курс. С.- Петербург, 2000.- 380с.

3.   В.Т. Безручко. Практикум по курсу «Информатика». Работа в Windows 2000 (Word, Excel). Москва «Финансы и статистика», 2003.- 633с.

4.   Б. Карпов. Microsoft Excel 2002: справочник. Питер, 2001-561с.

5.   www.microsoft.com/rus/officexp/excel

6.   www.compebook.ru