Пиши Дома Нужные Работы

Обратная связь

Работа с таблицей EXCEL как с базой данных: автофильтр, расширенный фильтр, сортировка данных, создание сводных таблиц

Цель работы.Изучить:

· автофильтрацию;

· расширенный фильтр;

· сортировку данных;

· создание сводной таблицы.

 

Работа со списком как с базой данных

 

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

В Microsoft Excel в качестве базы данных можно использовать список.

Список – таблица, данные в строках которой имеют однородную структуру, т.е. в каждом столбце списка содержатся данные одного типа (число, текст, дата и т.д.)

Пример списка – базы данных – приведен в таблице 2.5.1 (в столбце стоимость задана формулой: = количество * цена).

 

Таблица 2.5.1

Пример списка

Расход материалов со склада № 3
Материал количество ед. измер. Цена (у.е.) стоимость (у.е) получил дата
Доска м^3 Иванов А.П. 16.10.10
Гвозди кг Сидоров С.К. 12.11.10
Цемент кг Иванов А.П. 14.10.10
Кирпич шт. Петров С.К. 20.11.10
Доска м^3 Иванов А.П. 16.11.10
Кирпич шт. Иванов А.П. 20.01.10
Цемент кг Иванов А.П. 14.01.10
Гвозди кг Петров С.К. 14.09.10
Гвозди кг Петров С.К. 14.10.10
Кирпич шт. Петров С.К. 21.09.10

 

На листе не следует помещать более одного списка.

Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец.

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

Важные данные, не относящиеся к списку, не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.



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

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

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

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

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

Не следует помещать пустую строку между заголовками и первой строкой данных.

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

Для работы с базами данных Excel содержит следующий набор специальных средств:

· организация ввода, просмотра и поиска данных с помощью форм;

· сортировка данных;

· фильтрация данных;

· подведение итогов и создание сводных таблиц;

· организация ввода, просмотра и поиска данных с помощью форм.

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

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

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

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

Автофильтр

 

Для работы с автофильтром требуется выполнить следующие действия:

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

– затем выполнить команды меню Данные / Фильтр;

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

– пункт Условие позволяет применить отличные от равенства операторы сравнения;

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

? – любой символ в той же позиции, что и знак вопроса, например: д?м задает поиск "дым" и "дом";

* – любую последовательность символов в той же позиции, что и звездочка, например: *ино задает поиск "Люблино" и "Выхино".

Строчные и прописные буквы при фильтрации данных не различаются;

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

 

 

Рис. 2.5.1. Выбор условий для фильтрации

 

Задания для выполнения

 

Задача1.

в базе данных, заданной таблицей 2.5.1, выберите строки, относящиеся к октябрю 2010 года.

Для этого в столбце дата нужно задать соответствующее условие (рис. 2.5.2).

 

Рис. 2.5.2. Задание условия фильтрации (октябрь)

 

Задача2.

Выберите строки с одним наибольшим количеством кирпича по табл. 2.5.1.

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

Задача3.

1. Создайте таблицу 2.5.2.

2. Произведите автоматическую фильтрацию данных созданной таблицы по средней оценке за семестр.

3. Произведите автоматическую фильтрацию данных созданной таблицы по дисциплинам математика и сопромат.

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

 

Таблица 2.5.2

Исходные данные для выполнения задания

№ п/п ФИО студента Наименование дисциплины
математика физика сопромат теоретическая механика начертательная геометрия Средняя оценка студента за семестр
Иванов А.С. 3,8
Петренко Р.И. 4,0
Кузнецова Т.А. 4,0
Новчихин С.М. 3,8
Петрова А.Р. 3,6
Евсяков П.П. 3,4
Зиняков Н.П. 3,4
Пронин В.В. 3,6
Былова С.Ю. 3,6
Средняя оценка по дисциплине 3,9 3,7 3,9 3,3 3,7  

Задача4.

1. Создайте таблицу 2.5.3.

2. Произведите автоматическую фильтрацию данных созданной таблицы по спутникам.

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

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

 

Таблица 2.5.3

Планеты солнечной системы

ПЛАНЕТЫ СОЛНЕЧНОЙ СИСТЕМЫ
Планета Период Расстояние Диаметр Масса Спутники
Солнце
Меркурий 0,241 4,9 0,2
Венера 0,615 12,1 4,86
Земля 12,8
Марс 1,881 6,8 0,61
Юпитер 11,86 142,6 1906,98
Сатурн 29,46 120,2 570,9
Уран 84,01 87,24
Нептун 164,8 50,2 103,338
Плутон 247,7 2,8 0,1

Задача5.

1. Создайте таблицу 2.5.4.

2. Произведите автоматическую фильтрацию данных созданной таблицы по количеству купленных компьютеров.

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

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

 

Задача6.

1. Создайте таблицу 2.5.5.

2. Произведите автоматическую фильтрацию данных созданной таблицы по количеству материала за квартал.

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

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

 

Таблица 2.5.4

Исходные данные для выполнения задания

Месяц Компьютер Магазин Количество, шт. Цена, руб. Стоимость, руб.
Январь AMD Альфа
Январь Pentium1 Бета
Январь Pentium2 Гамма
Февраль AMD Альфа
Февраль Pentium1 Бета
Февраль Pentium2 Гамма
Март AMD Альфа
Март Pentium1 Бета
Март Pentium2 Гамма
Апрель AMD Альфа
Апрель Pentium1 Бета
Апрель Pentium2 Гамма
Май AMD Альфа
Май Pentium1 Бета
Май Pentium2 Гамма

 

 

Таблица 2.5.5

Исходные данные для выполнения задания

Задача7.

1. Заполните таблицу 2.5.6 произвольными данными за два месяца.

2. Найдите дни с температурой <0 в октябре.

3. Найдите дни с температурой >0 или с количеством осадков >0.

4. Определите самые холодные и самые теплые дни каждого месяца.

 

Таблица 2.5.6

Исходные данные для выполнения задания

Дата Температура воздуха (Сº) Количество осадков (мм)
01.10.2008
     

Расширенный фильтр

 

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

 

Рис. 2.5.3. Диалоговое окно «Расширенный фильтр»

 

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

Порядок действий при создании расширенного фильтра.

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

2. Введите в строки под заголовками условий требуемые критерии отбора.

3. Укажите ячейку в списке.

4. Выберите пункт меню Данные/ Фильтр/ Дополнительно.

5. В исходный диапазон автоматически ставятся координаты фильтруемого списка, если вы указали ячейку внутри списка в п.3.

6. Укажите координаты диапазона условий, содержащего строку заголовков и строки с критериями. В диапазон недопустимо включение лишних пустых строк.

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

Условия, записанные в одной строке, автоматически соединяются в одно логическое выражение союзом И (And). Условия, записанные в смежных строках, соединяются союзом ИЛИ (Or).

В рассмотренных ниже примерах в качестве таблицы с исходными данными использована табл. 2.5.1.

На ячейки одного столбца накладываются три или более условий отбора. Пример: выбираются все строки с условием:

(материал = доска) или (материал = гвозди) или (материал = цемент).

Условие отбора накладывается на ячейки двух или более столбцов. Пример: выбираются все строки с условием:

(получил = Иванов) и (Дата>10/10/2000) и (Дата<1/11/2000).

Получил дата дата
Иванов >10/10/2000 <1/11/2000

 

Возможен и такой вариант:

(Цена = максимальной цене в списке) или Стоимость = максимальной стоимости в списке).

Цена (у.е.) Стоимость (у.е)
=МАКС($D$3:$D$20)  
  =МАКС($E$3:$E$20)

В условии отбора используется возвращаемое формулой значение.

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

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

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

выбираются строки с условием – значение в столбце цена (у.е.) превышает среднее значение в ячейках $D$3:$D$20; заголовок условия – критерий:

критерий
='цена (у.е.)'>СРЗНАЧ($D$3:$D$20)

 

Аналогичный результат получится при задании условия в виде:

 
=D3>СРЗНАЧ($D$3:$D$20)

 

Задания для выполнения

Задача1.

Выполните расширенную фильтрацию данных по оценке дисциплины математика большей 3 и по оценке дисциплины теоретическая механика больше 3 по табл. 2.5.2.

Задача2.

Выполните расширенную фильтрацию планет по спутникам, количество которых меньше 5 и по диаметрам с массой больше 1000 (табл. 2.5.3).

Задача3.

Выполните расширенную фильтрацию данных по цене большей 25000 и по количеству купленных компьютеров больше 35 (табл. 2.5.4).

Задача4.

Выполните расширенную фильтрацию данных по количеству материала за 3 квартал, большего 10, и по общему расходу, меньшему 100 (табл. 2.5.5).

Подведение итогов

 

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

 

 

Рис. 2.5.4. Промежуточные итоги

 

Порядок работы для подведения итогов.

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

2. Укажите ячейку в этом списке.

3. Выберите команду меню Данные / Промежуточные итоги.

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

5. Выберите функцию, необходимую для подведения итогов, из списка Операция. Например, сумма.

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

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

Любая строка итогов может быть удалена из списка, это не повлияет на другие данные. Чтобы удалить все строки с итогами нужно выбрать меню Данные / Итоги / Убрать все.

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

Задания для выполнения

Задача1.

Получите итоги по фамилиям получателей с суммированием стоимости, а также с суммированием количества и стоимости каждого материала для каждого получателя (табл. 2.5.7).

Таблица 2.5.7

Итоги по фамилиям получателей

расход материалов со склада № 3
материал количество ед. измер. цена (у.е.) стоимость (у.е) Получил дата
        Иванов А.П. Всего
гвозди кг Петров С.К. 14.09.00
гвозди кг Петров С.К. 14.10.00
гвозди кг Петров С.К. 12.09.00
гвозди: всего        
кирпич: всего        
        Петров С.К. Всего
гвозди: всего        
кирпич: всего        
        Сидоров С.К. Всего
        Общий итог  

 

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

Сводная таблица служит для обобщения информации из базы данных и представления её в удобном для анализа виде.

Например, данные из списка расход материалов со склада № 3 можно представить в следующем виде (табл. 2.5.1):

ü страницы по полю получил, на каждой странице собраны данные по одной фамилии;

ü даты сгруппированы по месяцам;

ü в области данных вычисляется сумма по полю стоимость;

ü добавлены промежуточные итоги по каждой строке, каждому столбцу и общий итог.

Таблица 2.5.8

Сводная таблица

Получил Петров С.К.      
         
Сумма по полю стоимость (у.е) дата     Общий итог
Материал сентябрь октябрь Ноябрь
Гвозди  
Кирпич
Общий итог
             

 

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

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

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

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

· Если требуется создать сводную таблицу в виде подшивки страниц, то перетащить кнопку, соответствующую полю, по которому будут строиться страницы, в область Страница.

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

· Для группировки даты по месяцам нужно выбрать команду Группировать из меню Данные или панели инструментов.

Задания для выполнения

Задача1.

Создайте сводную таблицу, аналогичную табл. 2.5.8.

 

Задача2.

Создайте сводную таблицу на новом листе для расчета средней цены различных типов компьютеров за рассматриваемый период (табл. 2.5.4).

 

Задача3.

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

Контрольные вопросы

1. Создание электронных таблиц в Excel.

2. Задание автофильтрации в Excel.

3. Работа с расширенным фильтром в Excel.

4. Сортировка данных в Excel.

5. Создание сводных таблиц, их назначение.

 

 

Создание макросов в EXCEL

Цель работы. Изучить:

· работу с макросами в Excel;

· создание макроса построения графиков функций.

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

В Microsoft Office встроена среда программирования Visual Basic for Application (VBA). В этой среде можно создавать собственные процедуры для любого приложения Ms. Office. Однако для создания макроса знать VBA совсем не обязательно. В Ms. Office встроена программа, позволяющая автоматически переводить в команду VBA любое действие пользователя (нажатие клавиши, ввод данных в ячейку и др. подобные), а последовательность этих действий образует макрос.

Для создания макроса необходимо в меню Excel найти Макросы / Запись макроса (рис. 2.6.1).

 

 

Рис. 2.6.1. Начало работы с макросами

 

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

 

 

Рис. 2.6.2. Задание имени макроса

 

Чтобы использовать относительные ссылки, необходимо нажать кнопку Относительная ссылка на панели инструментов. Для остановки записи макроса необходимо в меню выполнить команду Макросы / Остановить запись. Когда запись макроса закончена, из меню Макросы / Макросы можно удалить или изменить макрос.

Задания для выполнения

1. на рабочем листе создайте таблицу вида:

a b c x начальное шаг по х
0,5

 

2. Запишите процедуру построения графика в макрос. Постройте график функции y = ах2+ bx + сc заданными коэффициентами и заданным шагом для 10 точек.

3. Удалите построенный график с рабочего листа. Измените значения коэффициентов а, b, с.

4. Запустите макрос. Макрос должен построить график с заданными коэффициентами на заданном интервале с заданным шагом. График строится на том рабочем листе, для которого записан макрос.

5. Откройте макрос для изменения в редакторе Visual Basic for Application. Скопируйте получившийся макрос в текущем модуле. Измените имя макроса в копии, например: Sub НовыйМакрос()

6. внесите в текст макроса следующие изменения:

· в начало макроса добавьте строку: S = ‘ActiveSheet.Name’ эта команда запоминает в S имя активного рабочего листа;

· замените обращение к «Лист1» на переменную S, например, строки:

ActiveChart.SetSourceDataSource:=Sheets("Лист1").Range("B5:B20")"), PlotBy:=xlColumns

ActiveChart.SeriesCollection(1).XValues ="=Лист1!R5C1: R20C1"

ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

замените на строки:

ActiveChart.SetSourceData Source:=Sheets(S).Range("B5:B20"), PlotBy:=xl Columns

ActiveChart.SeriesCollection(1).XValues = "=" & S & "!R5C1:R20C1" ActiveChart.Location Where:=xlLocationAsObject, Name:=S

7. В результате изменений НовыйМакрос должен строить график на любом активном листе. Построить на трех различных листах графики функции с различными коэффициентами по варианту с помощью отредактированного макроса.

Варианты заданий для работы приведены в таблице 2.6.1.

 

Таблица 2.6.1

Варианты заданий

№ варианта коэффициент – а коэффициент – в коэффициент – с х – принадлежит промежутку
2,2 3,1 [-2, 2]
1,2 2,5 3,2 [-5, 5]
1,4 2,7 3,3 [-8, 5]
1,6 2,8 3,4 [-8, 8]
1,8 3,2 0,3 [-4, 5]
3,5 4,2 [-4, 4]
2,2 3,8 0,22 [-7, 5]
2,5 3,7 [-7, 7]
2,7 1,5 0,5 [-9, 5]
2,8 1,7 0,7 [-6, 5]
3,2 2,1 0,9 [-5, 6]
3,5 2,3 [-4, 5]
3,8 2,6 1,2 [-5, 55]
3,1 1,4 [-55, 55]
1,5 3,2 1,6 [-45, 45]
1,7 3,3 1,8 [-3, 3]
2,1 3,4 [-9, 9]
2,3 0,3 2,2 [0, 5]
2,6 4,2 2,5 [0, 9]
3,1 0,22 2,2 [-9, 0]

 

Контрольные вопросы

 

1. Порядок создания макросов в Excel.

2. Возможности изменения текста макроса.

 






ТОП 5 статей:
Экономическая сущность инвестиций - Экономическая сущность инвестиций – долгосрочные вложения экономических ресурсов сроком более 1 года для получения прибыли путем...
Тема: Федеральный закон от 26.07.2006 N 135-ФЗ - На основании изучения ФЗ № 135, дайте максимально короткое определение следующих понятий с указанием статей и пунктов закона...
Сущность, функции и виды управления в телекоммуникациях - Цели достигаются с помощью различных принципов, функций и методов социально-экономического менеджмента...
Схема построения базисных индексов - Индекс (лат. INDEX – указатель, показатель) - относительная величина, показывающая, во сколько раз уровень изучаемого явления...
Тема 11. Международное космическое право - Правовой режим космического пространства и небесных тел. Принципы деятельности государств по исследованию...



©2015- 2017 pdnr.ru Все права принадлежат авторам размещенных материалов.