Файл: пособие по информатике(Часть2, EXCEL.doc

ВУЗ: Не указан

Категория: Не указан

Дисциплина: Не указана

Добавлен: 28.07.2024

Просмотров: 362

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

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

СОДЕРЖАНИЕ

Раздел 2 MicrosoftExcel

Автозаполнение рядов данных

Выделение данных

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

Форматирование данных

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

Расчетная ведомость ао «темп» за июнь 2008г.

Задание 2

Нахождение производной

Экзаменационная ведомость

Рабочая ведомость

Сводная ведомость

Задание 3

Варианты функций

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

Содержание отчета

Варианты заданий Задание 1

Количество осадков в миллиметрах

Количество осадков в миллиметрах

Задание 2

Задание 3

Заработная плата работников

Задание 4

Задание 5

Атмосферное давление

Задание 6

Линия тренда

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

Задание 1

Исходные данные

Задание 2

Исходные данные

Задание 3

Изменения прибыли фирмы

Задание 4

Функциональная зависимость

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

Содержание отчета

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

Сортировка списков

Фильтрация данных

Создание диапазона условий

Примеры условий отбора расширенного фильтра

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

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

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

Варианты заданий Задание 1

Задание 2

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

Задание 3

Исходные данные

Задание 4

Исходные данные

Задание 5

Исходные данные

Задание 6

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

Задания

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

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

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

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

Содержание отчета

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

Рис.2.4.4. Результаты фильтрации данных

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

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

Рис. 2.4.5 Фильтрация данных

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

Создание диапазона условий

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

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

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

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

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

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

Примеры условий отбора расширенного фильтра

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

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

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

Пример 5: Выбираются все строки с условием:

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

материал

доска

гвозди

цемент


.

  • Условие отбора накладывается на ячейки двух или более столбцов

  1. Пример 6: выбираются строки с условием:

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

Получил

дата

дата

иванов

>10/10/2000

<1/11/2000

  1. Пример 7: выбираются строки с условием:

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

цена(у.е.)

стоимость(у.е)

=МАКС($D$3:$D$20)

=МАКС($E$3:$E$20)

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

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

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

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

Пример 8: выбираются строки с условием:

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

критерий

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

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

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


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

Для подведения итогов по всей базе данных, а также по отдельным группам данных можно использовать функцию ExcelПРОМЕЖУТОЧНЫЕ ИТОГИ (рис. 2.4.6), а также создание сводных таблиц.

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

.

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

Порядок работы:

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

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

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

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

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

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

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

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

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

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

Таблица 2.4.3

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

расход материалов со склада № 3

материал

количество

ед. измер.

цена(у.е.)

стоимость(у.е)

Получил

дата

13050

Иванов А.П. Всего

гвозди

10

кг

3

30

Петров С.К.

14.09.00

гвозди

10

кг

2

20

Петров С.К.

14.10.00

гвозди

20

кг

2

40

Петров С.К.

12.09.00

гвозди Всего

40

90

кирпич Всего

5500

24000

24090

Петров С.К. Всего

гвозди Всего

30

90

кирпич Всего

2500

10000

10090

Сидоров С.К. Всего

47230

Общий итог


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

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

Пример 10: Например, данные из списка " расход материалов со склада № 3" можно представить в следующем виде:

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

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

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

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

Таблица 2.4.4


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

олучил

Петров С.К.

Сумма по полю стоимость(у.е)

дата

Материал

сен

окт

Ноя

Общий итог

Гвозди

70

20

90

Кирпич

14000

5000

5000

24000

Общий итог

14070

5020

5000

24090

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

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

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

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

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

  • Для перерасчета сводной таблицы, если изменились данные, на основе которых строилась эта сводная таблица: выделить ее, команда Обновить данные.

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