Табличный процессор

Дата: 2025-05-01; Просмотры: 1

Оценка:

0.00 из 5.00 — оценок

Скачиваний: 0

Скачать

Часть 2

Табличный процессор

MS Excel

ЗАНЯТИЕ 1

ЗАПУСК ПРОГРАММЫ. ЭКРАН ТАБЛИЧНОГО ПРОЦЕССОРА MICROSOFT EXCEL. ОСНОВНЫЕ ПОНЯТИЯ MS EXCEL.

ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ

Способы запуска MS Excel :

- через Главное меню Windows: Пуск – Программы – Microsoft Excel;

- через Главное меню Windows: Пуск – Создать документ Office – Вкладка Общие – Новая книга – OK;

- через Проводник или Мой компьютер: открыть папку, в которой должен находиться новый файл MS Excel и выбрать в меню Файл – Создать – Лист Microsoft Excel;

- дважды щелкнуть левой клавишей мыши по любому ранее созданному файлу MS Excel.

Окно табличного процессора MS Excel :

Значок Адрес Строка Строка Панели Кнопки

программы активной ячейки заголовка меню инструментов управления окном

Рабочая книга MS Excel – совокупность рабочих листов (от 1 до 255), сохраняемых на диске в одном файле, имеющем расширение *.xls.

Рабочий лист MS Excel – основной документ, используемый в MS Excel для хранения и обработки данных. Состоит из 65536 строк и 256 столбцов.

Ячейка – область рабочего листа, находящаяся на пересечении столбца и строки.

Текущая ячейка – ячейка, в которой находится курсор.

Диапазон ячеек – группа последовательных ячеек.

Способы выделения фрагментов таблицы:

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

Способы окончания ввода данных в ячейку:

- нажатие клавиши Enter;

- щелчок мышью по другой ячейке;

- переход к другой ячейке с помощью клавиш управления курсором;

- щелчок мышью по кнопке (Ввод) в Строке формул;

- щелчок мышью на квадратике в нижнем правом углу ячейки (Маркере заполнения).

Способы изменения ширины столбца (высоты строки):

- с помощью команды Формат – Столбец – Ширина… (Формат – Строка – Высота) указать точную ширину столбца (высоту строки);

- с помощью команды Формат – Столбец – Стандартная ширина задать стандартную ширину выделенным столбцам;

- с помощью команды Формат – Столбец – Автоподбор ширины (Формат – Строка – Автоподбор высоты) осуществляется автоподбор в соответствии с введенными данными;

- дважды щелкнуть по правому разделителю в строке заголовка столбца для автоподбора его ширины;

- дважды щелкнуть по нижнему разделителю в столбце заголовка строки для автоподбора ее высоты;

- поместить указатель мыши на правую границу столбца в строке с заголовками столбцов (указатель примет вид ) и, удерживая нажатой левую клавишу мыши, изменить ширину столбца;

- поместить указатель мыши на нижнюю границу строки в столбце с заголовками строк (указатель примет вид ) и, удерживая нажатой левую клавишу мыши, изменить высоту строки.

Ширина столбца измеряется в символах (от 0 до 255), высота строки – в пунктах (от 0 до 409).

Способы удаления выделенных строк, столбцов, ячеек:

- пункт Удалить… меню Правка;

- пункт Удалить… контекстного меню.

Способы очистки содержимого выделенных строк, столбцов, ячеек:

- клавиша Delete (Del);

- команда Правка – Очистить – Содержимое;

- пункт Очистить содержимое контекстного меню.

Способы добавления строк, столбцов, ячеек:

- команда Ячейки (Строки, Столбцы) меню Вставка;

- команда Добавить ячейки… контекстного меню.

Редактирование содержимого ячейки:

- активизируйте ячейку;

- нажмите клавишу F 2 или щелкните мышью в Строке формул;

- внесите в данные необходимые изменения;

- закончите ввод.

Способы форматирования ячеек:

- кнопки Панелей инструментов Стандартная и Форматирование;

- пункт Ячейки… меню Формат;

- команда Формат ячеек… контекстного меню.

Задания:

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

2. Сделайте текущим Лист2, щелкнув по его ярлычку внизу окна MS Excel.

3. Выделите ячейку C 6.

4. Вернитесь в ячейку A 1.

5. Вставьте два новых рабочих листа, используя команду Лист меню Вставка.

6. Сделайте активным Лист5.

7. Удалите Лист5 с помощью контекстного меню.

8. Переименуйте Лист4 в Рабочий лист с помощью контекстного меню. По окончании ввода нового имени нажмите Enter.

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

10. Выделите строку 3.

11. Отмените выделения.

12. Выделите столбец D.

13. Отмените выделения.

14. Выделите столбцы A, B, C и F.

15. Выделите диапазон ячеек A 5: C 8.

16. Отмените выделения.

17. Выделите одновременно диапазоны ячеек A 3: B 12, D5:F1, C 3: E 20.

18. Выделите весь рабочий лист.

19. Отмените выделение.

20. Удалите Рабочий лист с помощью команды Удалить контекстного меню.

21. Сделайте активным Лист1.

22. Введите в ячейку A 1 текст: “Фамилия”.

23. Введите в ячейку B 1 текст: “Год рождения”.

24. Заполните 10 последовательных (начиная со второй) ячеек столбцов A и B данными учащихся вашей группы.

25. Измените ширину столбцов таблицы так, чтобы на экране были видны все введенные данные.

26. Скопируйте таблицу на Лист2.

27. Измените ширину столбцов.

28. Удалите из таблицы 3 и 4 строки.

29. Добавьте в таблицу 2 строки после данных о пятом учащемся.

30. Очистите содержимое 2 строки таблицы.

31. Введите во 2 строку таблицы новые данные.

32. Измените год рождения шестого учащегося.

33. Отформатируйте ячейки таблицы следующим образом: размер шрифта – 14 пт; начертание заголовка – полужирный курсив; выравнивание заголовка по горизонтали и вертикали – от центра; внешние границы таблицы и заголовка – двойные линии; внутренние границы – одинарные линии.

34. Сохраните таблицу в своей папке под именем Задание 1.

35. Закройте MS Excel.

ЗАНЯТИЕ 2

АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ ЯЧЕЕК. ИСПОЛЬЗОВАНИЕ МАРКЕРА АВТОЗАПОЛНЕНИЯ. ФОРМУЛЫ. МАСТЕР ФУНКЦИЙ

Типы данных в ячейках таблицы:

1. Текстовые данные – строка текста, длина которой не более 32000 символов. В качестве первого символа для обозначения текстовых данных иногда используется апостроф – ‘, кавычки – " или пробел. Если MS Excel не может интерпретировать данные в ячейке как число или как формулу, программа считает, что это текстовые данные.

2. Числовые данные – отдельное число, введенное в ячейку. Данные рассматриваются как число, если их формат позволяет это сделать. Как числа рассматриваются данные, определяющие даты или денежные суммы. Ячейки, содержащие числовые данные могут, использоваться в вычислениях.

3. Формула – арифметическое или логическое выражение. Содержимое ячейки рассматривается как формула, если оно начинается со знака равенства (=). Формулы могут включать:

- константы;

- ссылки на ячейки (при выполнении вычислений на место ссылки вставляется числовое значение, находящееся в ячейке, на которую указывает ссылка);

- операторы – знаки арифметических, логических и других операций;

- встроенные функции;

- скобки, закладки и др.

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

Ссылка – указатель на ячейку или группу ячеек. Ссылки делят на:

- абсолютные – указывают на ячейку безотносительно к выбранной ячейке электронной таблицы;

- относительные – указывают на положение ячейки относительно текущей.

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

Для создания абсолютной ссылки нужно поставить знак $ перед той ее частью, которая не должна изменяться. Например: $ C $1 – абсолютные строка и столбец; $ C 1 – абсолютный столбец и относительная строка; C $1 – относительный столбец и абсолютная строка; C 1 – относительные столбец и строка.

Маркер автозаполнения позволяет:

- копировать и размножать содержимое ячеек;

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

- копировать формулы;

- стирать данные в выделенных ячейках;

- удалять и вставлять ячейки.

Копирование и размножение содержимого ячеек с помощью маркера автозаполнения:

- выделить ячейку или копируемый блок;

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

Если вместо копирования с помощью маркера автозаполнения чисел или дат происходит их приращение, то перетаскивать маркер нужно при нажатой клавише Ctrl.

Создание рядов с помощью маркера автозаполнения:

- ввести в соседние по вертикали или горизонтали ячейки первые два или более членов ряда;

- выделить заполненные ячейки;

- перетащите маркер автозаполнения в нужном направлении, при этом программа MS Excel сама определит закон, по которому формируется последовательность.

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

Способы ввода функций:

- непосредственный набор функции с клавиатуры в Строку формул;

- с помощью Панели формул;

- с помощью Мастер функций.

Вызов Панели формул осуществляется щелчком по кнопке (Изменить формулу) в Строке формул.

Панель формул имеет следующий вид:

Поле Функция Отмена формулы Ввод формулы

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

Кнопка Отмена формулы осуществляет закрытие Панели формул и возврат к активной ячейке.

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

Способы вызова Мастера функций:

- пункт Функция меню Вставка;

- кнопка Вставка функции на Панели инструментов Стандартная;

- комбинация клавиш Shift+F 3.

Задания:

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

2. Переименуйте Лист1, дав ему имя Заработная плата.

3. Сохраните таблицу в файле Занятие 2.

4. Введите в ячейки таблицы следующие данные:

A1 – № п/п;

A2 – 1;

A3 – 2;

B1 – Фамилия;

B 12 – Итого;

C 1 – Оклад;

D 1 – Ставка;

E 1 – Зарплата;

F 1 – Налоги;

G 1 – К выдаче;

H 1 – Ставка подоходного налога;

I 1 – 12%.

5. Заполните столбец А, используя маркер автозаполнения так, чтобы в нем содержалась последовательность чисел от 1 до10.

6. Заполните ячейки B 2: B 11, введя в них произвольные фамилии.

7. Заполните ячейки C 2: D 11.

8. Вычислите “Зарплату” как произведение “Оклада” на “Ставку”. Для этого введите в ячейку Е2 формулу =C2*D2.

9. Используя маркер автозаполнения, скопируйте формулу из ячейки E 2 в Е3:Е11. Обратите внимание на то, как изменилась формула.

10. Вычислите “Налог”. Для этого введите в ячейку F 2 формулу =E2*$I$1.

11. Используя маркер автозаполнения скопируйте формулу из ячейки F 2 в F 3: F 11. Обратите внимание на то, как изменилась формула.

12. Вычислите “К выплате”. Для этого введите в ячейку G 2 формулу =E2-F2.

13. Используя маркер автозаполнения, скопируйте формулу из ячейки G 2 в G 3: G 11. Обратите внимание на то, как изменилась формула.

14. Введите в ячейку C 12 формулу =СУММ(C2:C11). Для ввода функции используйте Мастер функций.

15. Используя маркер автозаполнения, скопируйте формулу из ячейки C 12 в D 12: G 12. Обратите внимание на то, как изменилась формула.

16. Измените значение ячейки I 1 на 20% и проследите за произошедшими в таблице изменениями.

17. Сохраните текущую таблицу и закройте MS Excel.

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

1. Создайте следующую таблицу в файле Маркер:

При заполнении столбцов А..Е и первой строки используйте автозаполнение; в столбце С заполнение делается по первым трем значениям; в столбце F складываются числа, стоящие в столбце С в предыдущей, текущей и следующих строках.

2. Создайте в файле Страна следующую таблицу:

Для разбиения текста в ячейке на строки используйте комбинацию клавиш Alt+Enter.

Для создания верхнего индекса выделите символ, выберите из меню Формат пункт Ячейки…

Вычислите сумму в столбце “Площадь”, используя кнопку Поле Функция Панели формул.

Вычислите сумму в столбце “Население”, используя кнопку Автосумма Панели инструментов Стандартная.

Для каждой страны вычислите плотность населения и долю (в %) от всего населения Земли.

При вычислении плотности населения всего мира используйте функцию СРЗНАЧ – Среднее значение.

3. Решите задачу. У Сережи было 6 рублей. Он вложил свой капитал в прибыльный бизнес, который приносит ему 20% дохода. Кроме того, каждое утро папа дает Сереже 50 копеек. Мальчик ездит в школу на автобусе, платя по 40 копеек за каждую поездку. Составьте и оформите таблицу доходов Сережи по предложенному образцу:

4. Один стакан лимонада содержит 15 калорий, 1 кусок торта – 150 калорий, 1 драже «Тик-Так» - 2 калории. Во время праздничного обеда Буратино выпил 5 стаканов лимонада, съел 20 драже «Тик-Так» и 4 куска торта. Мальвина съела 2 драже «Тик-Так», 1 кусок торта и выпила 1 стакан лимонада. Пьеро выпил 2 стакана лимонада и съел 2 куска торта. Дуремар съел 3 куска торта и выпил 2 стакана лимонада. Построить электронную таблицу, из которой будет видно, сколько всего стаканов лимонада было выпито, кусков торта и драже «Тик-так» съедено; сколько калорий употребил каждый участник праздничного обеда; сколько калорий содержалось во всем выпитом лимонаде, всех съеденных кусках торта и драже «Тик-так».

5. Решить задачу путем построения электронной таблицы. Исходные данные для заполнения таблицы подберите самостоятельно (не менее 10 строк). При построении таблицы используйте функцию ЕСЛИ.

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

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

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

8. В сельскохозяйственном кооперативе работают 5 сезонных рабочих. Норма сбора овощей составляет N кг. Оплата труда производится по количеству собранных овощей: k рублей за 1 кг. Составить таблицу, содержащую сведения о количестве собранных овощей каждым рабочим и об оплате труда каждого рабочего. Известно, что 1-й рабочий собрал овощей в 3 раза больше нормы; 2-й – на 50 кг меньше 1-го; 3-й – в 1,5 раза больше нормы; 4-й – на 75 кг больше 3-го; 5-й – на 10 кг больше 1-го.

9. В начале года потребление овощей и мяса составляло А кг и В кг соответственно. Ежемесячно потребление овощей увеличивается в среднем в 1,1 раза, мяса – на 3%. Проследить ежемесячное изменение потребления овощей и мяса в течение полугода.

10. Вычислить размер недельной заработной платы рабочего. Ежедневно он может находиться как на обычном, так и на вредном производстве. Часы работы по дням недели указаны в двух строках для каждого человека. По итогам недели вычисляется число дней и часов, отработанных в обычных и вредных условиях. На их основе определяется оплата труда умножением «часов» на соответствующую «часовую оплату». Кроме того, рабочим начисляется «доплата» за сверхурочный труд и за вредность. «Доплата» за труд в обычных условиях производится только при наличии сверхурочного времени. Разность между фактической длиной рабочей недели и 48 часами оплачивается по тарифу сверхурочных часов (ячейка L 1). Доплата за работу на вредном производстве осуществляется аналогично, но только если отработано свыше 20-ти «вредных» часов. Кроме того, в «доплату» входит сумма на покупку молока (ячейка L 2) за каждый день, отработанный во вредных условиях. В столбце M формируется сообщение (слово «Отгул»), если отработано свыше 30-ти часов на вредном производстве. В клетке M 27 вычислить число всех отгулов за неделю.

Примечания:

1) в ячейке E 2 содержится размер почасовой оплаты за работу на обычном производстве;

2) в ячейке E 3 содержится размер почасовой оплаты за работу на вредном производстве;

3) формат ячеек E 2, E 3, L 2, J 7: L 28 – к числовым данным автоматически добавляется «р.»;

4) формат ячейки M 11 – к числовым данным добавляется «дн.»;

5) слово «Отгул» должно выводится красным цветом;

6) при вычислении количества отгулов можно использовать функцию СЧЁТЕСЛИ(диапазон; условие).

ЗАНЯТИЕ 3

ДИАГРАММЫ

В MS Excel диаграммы создаются с помощью Мастера диаграмм на основе имеющихся в таблице данных.

Способы вызова Мастера диаграмм:

- с помощью кнопки Мастер диаграмм на Панели инструментов Стандартная;

- с помощью пункта Диаграмма… меню Вставка.

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

Этапы построения диаграммы с помощью Мастера диаграмм:

1. Первое диалоговое окно Мастера – Шаг 1 из 4 – Тип диаграммы – имеет две вкладки: Стандартные и Нестандартные. На этом этапе нужно выбрать подходящий вариант строящейся диаграммы.

2. Второе диалоговое окно Мастера – Шаг 2 из 4 – Источник данных диаграммы – имеет две вкладки: Диапазон данных и Ряд.

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

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

3. Третье диалоговое окно Мастера – Шаг 3 из 4 – Параметры диаграммы – позволяет определить характер оформления диаграммы, т.е. выполнить ее форматирование. Данное окно содержит следующие вкладки:

- Заголовок – для ввода текста заголовка диаграммы и подписей осей;

- Оси – для определения отображений и маркировки осей координат;

- Линии сетки – для выбора типа линий и характера отображения сетки;

- Легенда – для отображения или скрытия легенды и определения ее места на диаграмме. Легенда – небольшое подокно на диаграмме, в котором отображаются названия рядов данных и образцы их раскраски на диаграмме (в виде ключа легенды);

- Подписи данных – для управления отображения надписей, соответствующих отдельным элементам, данным на диаграмме;

- Таблица – для добавления к диаграмме (или скрытия) таблицы данных, использованной для построения диаграммы.

4. Четвертое окно Мастера – Шаг 4 из 4 – Размещение диаграммы – служит для определения места размещения диаграммы в рабочей книге.

Для перехода к каждому последующему шагу Мастера диаграмм нужно использовать кнопку . Для возврата к предыдущему шагу Мастера нужно использовать кнопку . Для завершения построения необходимо нажать кнопку .

Задания:

1. Откройте файл Страна. xls.

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

- выделить диапазон ячеек D 2: D 12;

- вызвать Мастер диаграмм;

- на первом шаге Мастера определить тип диаграммы – Круговая и выбрать вид – Объемный вариант;

- на втором шаге Мастера перейти на вкладку Ряд и определить Подписи категорий, щелкнув по кнопке в правой части данного подокна, выделить диапазон ячеек, содержащий название стран, и восстановить размеры диалогового окна с помощью кнопки ;

- на третьем шаге Мастера на вкладке Заголовки ввести Название диаграммы – Распределение населения;

- на четвертом шаге Мастера выбрать Поместить диаграмму на отдельном листе и ввести имя листа Население.

В результате на листе Население будет находиться диаграмма следующего вида:

3. Измените тип диаграммы на гистограмму. Для этого в контекстном меню диаграммы выберите пункт Тип диаграммы…, в появившемся окне выберите Обычную гистограмму и нажмите .

В результате на листе Население будет находиться диаграмма следующего вида:

4. Перейдите на Лист2 и дайте ему имя Динамика.

5. Составьте таблицу по образцу:

6. Заполните таблицу, выполнив необходимые вычисления.

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

8. Сохраните текущий документ.

9. Выделите в таблице столбцы B, D, F и H.

10. Скройте выделенные столбцы с помощью команды Формат – Столбцы – Скрыть.

11. Скройте строку 3 с помощью команды Скрыть контекстного меню.

12. Выделите диапазон ячеек A 2: I 6.

13. Создайте диаграмму следующего вида:

14. С помощью пункта Формат оси… контекстного меню Оси значений измените формат чисел так, чтобы на экран выводились целые числа со знаком процента (0%, 10%, … , 100%).

15. Сохраните документ и закройте MS Excel.

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

1. Создайте и оформите следующую таблицу:

По данным таблицы постройте диаграмму вида:

2. Создайте и оформите следующую таблицу:

По данным таблицы постройте диаграмму вида:

ЗАНЯТИЕ 4

СОРТИРОВКА ДАННЫХ. ФИЛЬТРЫ

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

Виды сортировок и способы их выполнения:

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

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

Здесь же можно задать многокаскадную сортировку (Затем по; В последнюю очередь, по). Это означает, что если по первому сортируемому значению некоторые строки совпадают, то они между собой будут отсортированными по указанному в пункте Затем по столбцу. Третий столбец указывается, если совпадения будут по обоим первым столбцам.

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

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

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

- выделить диапазон, для которого будет создан фильтр;

- в меню Данные выбрать пункт Фильтр – Автофильтр;

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

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

Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ. Каждая часть условия может включать:

Ø значение, которое может выбираться из списка или содержать шаблонные символы подстановки * и ?;

Ø оператор сравнения.

Способы восстановления всех строк исходной таблицы:

- щелкнуть по кнопке и в раскрывшемся списке выбрать Все;

- выбрать в меню Данные пункт Фильтр – Отобразить все.

Отмена режима автофильтра: выбрать в меню Данные пункт Фильтр – Автофильтр.

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

- сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора (интервал критериев). Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Если условия вводятся в одной строке для разных столбцов, то они считаются связанными логической связкой И. Если критерии отбора записываются в разных строках, то они считаются связанными логической связкой ИЛИ. Между значениями условий отбора и таблицей должна находиться как минимум одна пустая строка;

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

- в меню Данные выбрать пункт Фильтр – Расширенный фильтр;

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

Отмена режима расширенного фильтра: выбрать в меню Данные пункт Фильтр – Отобразить все.

Задания:

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

2. В файле Склад создайте и заполните следующую таблицу:

3. В последнем столбце произведите вычисления по формуле: Итого=Цена*Количество.

4. Используя простую сортировку, отсортируйте данные таблицы по полю Цена.

5. Отсортируйте наименования организаций в алфавитном порядке, внутри каждой организации наименование товара в алфавитном порядке и внутри каждого наименования товара – по возрастанию количества проданного товара. Для этого:

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

- выполните команду Данные – Сортировка;

- в первом уровне сортировки выберите поле Организация, во втором — Товар, в третьем — Кол-во и нажмите OK.

6. Скопируйте таблицу с Листа 1 на Лист 2 и назовите новый лист Автофильтр.

7. Создайте для первого столбца автофильтр.

8. Выберите из таблицы только те строки, которые относятся к АОЗТ «Белокуриха».

9. Отключите автофильтр.

10. Создайте автофильтр для всех столбцов таблицы.

11. Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «хлеб», т.е. осуществите выборку по двум полям.

12. Восстановите все строки исходной таблицы.

13. Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16.

14. Восстановите все строки исходной таблицы.

15. Используя автофильтр, выведите на экран записи, содержащие колхоз «Восток» и дату покупки товара в промежутке после 2 марта до 13 июня.

16. Скопируйте таблицу с Листа 1 на Лист 3 и дайте имя листу Расширенный фильтр.

17. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «Хлеб», т.е. осуществите выборку по двум полям. Для этого:

- ниже таблицы, оставив пустые 2 строки, скопируйте строку заголовка таблицы;

- в следующей строке сформируйте критерий отбора записей: в столбец Организация введите АОЗТ «Белокуриха», в столбец Товар — «Хлеб»;

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

- выполните команду Данные – Фильтр – Расширенный фильтр…;

- в появившемся диалоговом окне в строке Исходный диапазон появится диапазон, соответствующий расположению анализируемой таблицы;

- в диалоговом окне установите курсор в строку Диапазон условий, перейдите на рабочий лист и выделите диапазон условий отбора;

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

18. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16. При этом в диапазоне условий отбора в поле Цена укажите условие: <=16.

19. Сохраните файл Склад и закройте его.

20. Откройте файл Страна. xls.

21. Создайте новую рабочую книгу.

22. Добавьте в новую рабочую книгу еще три рабочих листа и сохраните книгу под именем Занятие 4.

23. Скопируйте таблицу из файла Страна. xls в файл Занятие 4 на Лист1.

24. Закройте файл Страна. xls.

25. Скопируйте Лист1 на все остальные рабочие листы.

26. Расположите на первом рабочем листе страны по алфавиту.

27. На втором рабочем листе с помощью автофильтра выберите страны с площадью более 5000 тыс. км².

28. На третьем рабочем листе с помощью автофильтра выберите страны с населением меньше 150 млн. чел.

29. На четвертом рабочем листе с помощью автофильтра выберите страны с плотностью населения от 100 до 300 чел./км².

30. На втором рабочем листе отмените режим фильтрации.

31. На четвертом рабочем листе с помощью расширенного фильтра выберите страны, начинающиеся с буквы «К» и имеющие численность населения более 1 млрд. чел. Чтобы выбрать все страны, названия которых начинаются с буквы «К», нужно при создании условий отбора в поле Страна ввести К*.

32. Сохраните файл и закройте MS Excel .

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

1. В файле Телевизоры создайте на первом рабочем листе следующую таблицу и

a. используя расширенный фильтр, отобразите на втором рабочем листе все телевизоры Sony с размером экрана 37 и Samsung – с размером экрана 51;

b. используя автофильтр, отобразите на первом рабочем листе только те строки, которые относятся к телевизорам, начинающимся на букву S или P.

ЗАНЯТИЕ 5

АНАЛИЗ И ОБОБЩЕНИЕ ДАННЫХ.

ПРОМЕЖУТОЧНЫЕ ИТОГИ В СПИСКАХ.

КОНСОЛИДАЦИЯ ДАННЫХ. СВОДНЫЕ ТАБЛИЦЫ

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

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

Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении данных.

Автоматическое подведение итогов осуществляется следующим образом:

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

- выделить какую-либо ячейку таблицы или требуемый диапазон;

- выбрать пункт Итоги… меню Данные;

- выбрать столбец, содержащий группы, по которым необходимо подвести итоги (столбец, по которому проводилась сортировка), из списка При каждом изменении в;

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

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

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

Символы структуры:

- Номера уровней. Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами. Данные первого уровня не могут быть скрыты. Максимальное количество уровней – 8;

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

- Символ «+» маркирует закрытые уровни. Щелчок по нему позволяет показать детали соответствующего уровня;

-

Символ «–» маркирует открытые уровни. Щелчок по нему позволяет скрыть детали соответствующего уровня.

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

Процедура консолидации данных:

1. Выделить ячейку, определяющую положение итоговой таблицы.

2. Выбрать пункт Консолидация меню Данные.

3. Указать в окне Консолидация в списке Функция функцию, с помощью которой будет осуществляться подведение итогов.

4. Установить курсор в строку Ссылка.

5. Выделить первую исходную область.

6. Щелкнуть по кнопке Добавить окна Консолидация и нажать клавишу Del.

7. Повторить действия 4-6 для остальных исходных областей.

8. Нажать кнопку OK.

Окно Консолидация:

Кнопка Обзор… позволяет выбирать исходные области из других рабочих листов и файлов.

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

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

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

Сводную таблицу можно создать на основе:

- таблиц MS Excel, организованных в виде списка или базы данных;

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

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

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

Создание и модификация сводных таблиц выполняется с помощью Мастера сводных таблиц и диаграмм.

Способы запуска Мастера сводных таблиц и диаграмм:

- пункт Сводная таблица… меню Данные;

- кнопка Мастер сводных таблиц на Панели инструментов Сводные таблицы.

Этапы работы с Мастером сводных таблиц и диаграмм:

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

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

- в третьем окне мастера выбрать место размещения сводной таблицы, с помощью кнопки Параметры… установить параметры вывода информации, с помощью кнопки Макет… сформировать структуру сводной таблицы и нажать кнопку Готово.

Окно макета сводной таблицы:

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

Процедура создания макета сводной таблицы:

- перетащить поля, в которых нужно произвести отбор нужных записей (фильтрацию), в область Страница. Область Страница может оставаться незаполненной;

- поместить поля, которые должны быть представлены в сводной таблице в области Строка и Столбец;

В области Страница, Строка и Столбец каждое поле может помещаться только один раз.

Для того, чтобы удалить поле из области построения, его кнопку нужно перетащить за пределы области построения.

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

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

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

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

- щелкнуть мышью по сводной таблице;

- на Панели инструментов Сводные таблицы щелкнуть по кнопке Сводная таблица и выбрать в раскрывающемся списке команду Формулы – Вычисляемое поле;

- щелкнуть по кнопке Добавить, а затем – по кнопке ОК;

- расположить вычисляемое поле в требуемом месте отчета.

Обновление данных в сводной таблице:

- выделить любую ячейку сводной таблицы;

- щелкнуть по кнопке Обновить данные на Панели инструментов Сводные таблицы.

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

Удаление сводной таблицы:

- установить курсор в любую ячейку сводной таблиц;

- на Панели инструментов Сводные таблицы открыть список Сводная таблица и выбрать в нем команду Выделить – Таблица целиком при активизированной кнопке Разрешить выделение;

- в меню Правка выбрать пункт Очистить – Все.

Задания:

1. Откройте файл Склад.xls.

2. Вставьте в таблицу два новых листа – Лист4 и Лист5.

3. Скопируйте таблицу с Листа1 на Лист4 и Лист5.

4. Перейдите на Лист4.

5. Осуществите автоматическое подведение итогов в таблице по столбцу Итого для каждой организации. Для этого:

- отсортируйте данные в столбце Организации таблицы по алфавиту;

- выделите какую-нибудь ячейку внутри таблицы;

- выберите пункт Итоги… меню Данные;

- в раскрывающемся списке При каждом изменении в выберите Организация;

- из списка Операция выберите Сумма;

- в списке Добавить итоги установите флажок рядом с полем Итоги;

- установите флажки Заменить текущие итоги и Итоги под данными;

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

6. Используя символы структуры, приведите таблицу к следующему виду:

7. Перейдите на Лист5.

8. Осуществите автоматическое подведение итогов в таблице по столбцу Количество для каждого товара.

9. Сохраните и закройте текущий файл.

10. Создайте новую рабочую книгу и дайте ей имя Хлеб.

11. Создайте в рабочей книге Хлеб следующую таблицу:

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

- выделите ячейку, определяющую положение итоговой таблицы, например, А22;

- выберите пункт Консолидация… меню Данные;

- в окне Консолидация в списке Функция укажите функцию Сумма;

- установите курсор в строку Ссылка;

- выделите первую исходную область A 4: D 7;

- щелкните в окне Консолидация кнопку Добавить и нажмите клавишу Del;

- повторите предыдущие три действия для диапазонов A 9: D 13, A 15: D 18;

- установите флажок Использовать в качестве имен значения левого столбца и нажмите ОК.

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

14. Сохраните и закройте текущий документ.

15. Создайте в файле Сводная таблица следующую таблицу:

16. На основании имеющихся данных постройте сводную таблицу. Для этого:

- вызовите Мастер сводных таблиц;

- на первом шаге мастера установите флажки Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel и Вид создаваемого отчета сводная таблица;

- на втором шаге мастера укажите диапазон $A$1:$E$11;

- на третьем шаге установите флажок Поместить таблицу в новый лист, создайте макет будущей таблицы (кнопка Макет…): поместите мышью кнопку Точка в область Страница, кнопку Наименование – в область Строка, кнопку Цена – в область Столбец, кнопку Сумма – в область Данные, нажмите ОК, а затем кнопку Готово.

В результате на Листе 4 будет таблица вида:

17. Используя фильтры сводной таблицы (кнопки ), выведите на экран данные по ржаному и городскому хлебу Булочной №1.

18. Перетащите кнопку Точка в область Строка. Таблица приняла вид:

19. Сохраните текущий документ и закройте MS Excel.

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

1. Для данной таблицы осуществите автоматическое подведение итогов по столбцу Количество для каждого наименования.

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