Файл: Учебнометодическое пособие Работа со списками в среде Microsoft Excel 2007.pdf

Министерство образования и науки
Российской Федерации
Сочинский филиал
Российского Университета Дружбы Народов
И. А. Батенева
Учебно-методическое пособие
«Работа со списками
в среде Microsoft Excel 2007»
Сочи
2010

2
Печатается по решению кафедры «Экономика» Сочинского филиала Рос- сийского университета Дружбы народов.
Автор-составитель: к. п. н. Батенева И. А.
Рецензенты: к. э. н., доцент Петенко А. Т., к. э. н., доцент Шурухина Т. В.
В данном пособии рассматривается практическое применение MS
Excel 2007. Материал учебного пособия изложен в соответствии с читаемым в течение ряда лет соответствующего раздела курса «Информатика» для студентов СФ РУДН. В данном пособии представлена система заданий по теме «Создание табличных баз данных» Пособие предназначено для студен- тов направления 080100 «Экономика» всех форм обучения.
© Сочинский филиал Российского университета Дружбы народов
© Батенева И. А., 2010

3
Оглавление
Введение ................................................................................................................. 4 1.1. Создание списка .............................................................................................. 6 1.2. Сортировка ...................................................................................................... 7 1.3. Автофильтр...................................................................................................... 9 1.4. Расширенный фильтр ................................................................................... 11 1.5. Подведение промежуточных итогов ........................................................... 14 1.6. Функции баз данных ..................................................................................... 17 1.7. Проверка вводимых значений ..................................................................... 19
Литература ............................................................................................................ 23

4
Введение
Чтобы запустить Excel, выполните Пуск

Все программы

Microsoft
Office

Microsoft Office Excel 2007. Поскольку Excel является программой, входящей, как и Word, в состав пакета Microsoft Office, интерфейс этих при- ложений во многом схож. Главное меню также представлено в виде вкладок, на ленте которых находятся группы инструментов, предназначенных для форматирования ячеек и обработки данных.
Электронная таблица состоит из ячеек, которые образуют строки и столбцы. Файл электронной таблицы называется книгой. По умолчанию но- вый файл Excel (книга) имеет три электронные таблицы — три листа (так принято называть рабочие области в Excel). Переключаться между листами можно с помощью ярлыков в нижней части окна.
Строки образованы горизонтальными рядами ячеек и пронумерованы числами (1, 2, 3…). Максимальное количество строк в листе — 1 048 575.
Нумерацию можно видеть в заголовках строк в левой части окна Excel.
Столбцы обозначаются латинскими буквами (A, B, C, D, … Z, AA, АВ, АС,
AD … AZ, BA, BB, BC, …, BZ…. AAA, AAB, AAC и т.д.), которые находят- ся в заголовках столбцов под лентой. Максимальное количество столбцов в листе — 16 384.
Каждая ячейка таблицы имеет уникальный адрес, который легко узнать, по- смотрев на номер строки и столбца, на пересечении которых она расположе- на. Иначе положение ячейки принято называть ссылкой, что более правиль- но при использовании формул. В каждую ячейку можно ввести данные трех типов: текст, число, которое может быть представлено в разных форматах, и формулу. По формуле выполняется расчет, результат которого отображается в содержащей ее ячейке. Строка формул — это уникальный элемент интер- фейса Excel, расположенный под лентой с кнопками. Слева в строке выво- дится адрес активной ячейки (той, что выделена черной рамкой), а справа — ее содержимое, которое можно редактировать. Кроме того, строка формул справка

5 содержит кнопку вызова Мастера функций, которые используются для со- здания математических выражений.
Ввод любой формулы в Excel всегда начинается со знака равенства «=». За- тем указываются адреса ячеек (ссылки на ячейки) и математические функ- ции, которые следует применить к их содержимому. О доступных форматах чисел можно прочитать в соответствующем разделе справки.
Excel может работать как с простыми и небольшими по размерам, так и с более сложными и занимающими большой объем дискового пространства базами данных (БД). В Excel БД - это просто список, состоящий из одного или более столбцов. Полнофункциональный список или БД в MS Excel со- стоит из диапазона базы данных, диапазона критериев, диапазона для извле- чения.
Диапазон базы данных - область, где хранятся данные списка; связанные друг с другом данные записываются в отдельные строки, каждому столбцу соответствует свое поле списка с уникальным именем поля.
Диапазон критериев - область на рабочем листе, где задаются критерии по- иска информации; здесь указываются имена полей и отводится область для записи условий отбора.
Диапазон для извлечения - область, в которую копируют выбранные из списка данные.
При просмотре достаточно большого списка удобно, чтобы строка заголовков всегда оставалась видимой. Зафиксировать строку с именами по- лей можно следующим образом. Нужно опустить маркер разделения окна по вертикали (он находится непосредственно над вертикальной полосой про- крутки) под строку заголовков, затем воспользоваться командой Вид-
3акрепить области. Если необходимо отменить закрепление областей, то сначала нужно выполнить команду Вид- Снять закрепление областей.

6
1.1. Создание списка
Задание. Откройте файл Варианты.docx, найдите Рабочий лист, который со- ответствует вашему варианту, скопируйте его в свою Рабочую книгу. Затем над созданным списком необходимо выполнить следующие действия:
- два вида сортировки ,
- поиск информации с помощью автофильтра,
- поиск информации с помощью расширенного фильтра,
- подведение итогов,
- анализ списка с помощью функции,
- проверку вводимых значений.
Каждое задание необходимо выполнять на отдельном листе; листы име- новать в соответствии с выполняемым заданием (например, «Автофильтр»,
«Сортировка в особом порядке» и т.п.). Для этого потребуется копировать список на нужное количество листов.
Пользуйтесь встроенной справкой !!!
1. Сортировка (табл. 2). Это задание состоит из двух пунктов:
1) стандартная сортировка
2) сортировка в особом порядке.
Во втором столбце таблицы указаны поля, по которым нужно осуществить сортировку. В третьем столбце указано поле, для которого нужно осуще- ствить сортировку в особом порядке.
2. Автофильтр (табл. 3).
3. Расширенный фильтр (табл. 4). При формировании некоторых крите- риев отбора следует использовать вычисляемые условия.
4.
Подведение промежуточных итогов (табл. 5). Итоги во многих вари- антах нужно проводить в несколько этапов.
При этом заменять текущие ито- ги не нужно.

7
5. Функции баз данных (табл. 6).
6. Проверка вводимых значений (табл. 7). В таблице указано поле, для которого требуется задать проверку вводимых значений. В некоторых вари- антах даны рекомендации для реализации задания. В других нужно самосто- ятельно определить допустимые значения для указанного поля.
Таблица 1
Предметная область (см. файл Варианты.docx)
Вариант
Название
предметной
области
Пояснения
с
0
по
2
Кадры
Возраст рассчитывается по формуле с
3
по
5
Деканат
Возраст рассчитывается по формуле с
6
по
9
Продажи
Сумма рассчитывается по формуле
Обратите внимание! Для ячеек с датами установлен формат Краткая дата.
Для ячеек с суммами установлен формат Денежный.
1.2. Сортировка
Задание. Чтобы выполнить сортировку списка, нужно выделить соответ- ствующий диапазон ячеек, выбрать команду Данные-Сортировка. В по- явившемся окне задаются уровни и порядок сортировки. Порядок сортиров- ки может быть «По возрастанию» и «По убыванию».
Стандартная сортировка и сортировка «в особом порядке» производятся на отдельных Рабочих листах!!!
Для сортировки в особом порядке в окне Сортировка в поле Порядок необ- ходимо создать настраиваемый список, в соответствии с которым Excel отсортирует данные.

8
Порядок сортировки задать самостоятельно, но этот порядок должен отли- чатся от порядка «по убыванию» и «по возрастанию».
Ввести элементы вашего списка, учитывая что

9
Таблица 2
Стандартна сортировка и сортировка «в особом порядке»
Вариант Стандартная сортировка
В особом порядке
0
Ф, И, О, дата рождения отдел
1 отдел, Ф, И, О должность
2 должность, Ф, И, О отдел
3
Группа, Ф, И, О группа
4
Факультет, Ф, И, О, факультет
5
Предмет, группа, факультет, фамилия предмет
6
Товар, дата, сумма, менеджер вид сделки
7
Менеджер, Товар, количество, вид сделки менеджер
8
Товар, количество, вид сделки, дата клиент
9
Дата, Менеджер, Товар, количество вид сделки
1.3. Автофильтр
Задание. Автофильтр выводит на Рабочий лист данные, удовлетворяющие созданному критерию. При этом записи, не удовлетворяющие критерию, будут скрыты.
Поиск данных для отображения производится на основе задаваемых пользо- вателем критериев (требований, налагаемых на данные). Результатом филь- трации является временное скрытие записей, не удовлетворяющих критери- ям. Поиск и фильтрацию данных можно осуществить с помощью автофиль-
тра и расширенного фильтра.
Для ввода простейшего критерия достаточно ввести в соответствующем по- ле искомое значение. В критериях на основе сравнения можно использовать различные операции сравнения: =, >, <=, <, >=, <>.
Для задания близкого соответствия с использованием образца используются символы шаблона * и ?: («*» заменяет любое количество любых символов,

10
«?» - один любой символ). Например, нужно выбрать все фамилии, начина-
ющиеся на «Ив» (Иванов, Ивлев, Иванович и т. п. ). Для решения этой зада-
чи можно использовать шаблон «Ив*».
Для поиска записей, удовлетворяющих нескольким условиям, можно зада- вать множественные критерии. Для наложения нескольких условий поиска в
Excel используются логические операции и, или.
С помощью автофильтра можно создать три типа фильтров:

по значениям списка,

по формату или

по условиям.
Все они являются взаимоисключающими в пределах диапазона ячеек или столбца таблицы. Например, можно выполнить отбор по цвету ячеек или по списку чисел, но нельзя использовать оба типа одновременно; точно так же необходимо выбрать один тип из двух, если требуется выполнить отбор по значкам или на основе фильтра, заданного пользователем.
Чтобы включить автофильтр, нужно воспользоваться командой Данные-
Фильтр. Excel выведет кнопки со стрелками (кнопки автофильтра) рядом с каждым заголовков столбца, которые и позволят построить условие фильтра.
В тех столбцах, где фильтр был применен, вместо стрелки фильтра появится значок
Чтобы снять фильтрацию с поля, нужно нажать кнопку и выбрать
Снять фильтр с… Чтобы снять фильтр полностью и отобразить весь спи- сок нужно отжать кнопку Фильтр .

11
Таблица 3
Условие автофильтра
Вариант Условие
0
Получить информацию о мужчинах, родившихся в период с 1 января
1985 по 31 декабря 1990, принятых на работу после 1 января 2008.
1
Получить информацию о мужчинах, фамилия у которых начинается с
И или З, отчество Иванович, работающих в подразделении, которое в названии содержит слово цех.
2
Получить информацию о женщинах с окладом ниже 30000, фамилии которых заканчиваются на -ова и -ева, работающих либо в цех2, либо
цех3.
3
Отобразить информацию о студентах Э1 и Э2, по предмету математика с оценкой отлично или хорошо
4
Отобразить информацию о студентах юридического и филологическо- го факультета, родившихся до 01.01.1990 или после 01.06.1993.
5
Отобразить информацию о студентах группы Э1, сдававших экзамен по математике не вовремя (группа Э1 сдавала 15.01.2011).
6
Отобразить информацию о клиентах ЗАО Х и ЗАО Юг, поставивших товар пароварка или хлебопечка в период с 20.01.11 по 25.01.11 7
Отобразить информацию о менеджерах, осуществлявших продажи товара электрогриль или пароварка в период с 20.01.11 по 25.01.11.
8
Отобразить информацию о товарах, поставленных в период в период с
20.01.11 по 25.01.11 от производителя из Китая.
9
Отобразить информацию о товарах, поставленных в период с 1.01.11 по 15.01.11 партией более 50 единиц.
1.4. Расширенный фильтр
Задание. Расширенный фильтр является более гибким средством отбора за- писей из БД, чем автофильтр. Использование расширенного фильтра пред- полагает наличие диапазона критериев (условий) Удобнее всего диапазон критериев располагать рядом со списком. Диапазон критериев должен со- стоять, по крайней мере, из двух строк: в верхней строке указываются имена полей, во второй и последующих строках - условия отбора. За исключением

12 вычисляемых условий, заголовки в диапазоне критериев должны точно сов- падать с заголовками столбцов в списке.
Кроме того, расширенный фильтр может использоваться для извлечения строк из списка и вставки копий этих строк в другую часть текущего листа.
В этом случае используется диапазон для извлечения.
Для выполнения задания сначала необходимо создать диапазон условий вы- ше или правее основной таблицы. После вызова расширенного фильтра
(Данные-Дополнительно) установить флажок Скопировать результат в
другое место. В поле ввода Исходный диапазон указать всю исходную таб- лицу, в Диапазон условий ввести адрес соответствующего диапазона, в поле
Поместить результат в диапазон- указать ячейки в которых вы хотите увидеть результат выборки (как правило, ниже основной таблицы). При со- здании диапазона условий учтите, что условия, записанные в одной строке имеют логическую связку И, а условия записанные в одном столбце имеют логическую связку ИЛИ.
Например:
Требуется отобразить информацию о мужчинах, фамилии которых начина- ются с И или Т, родившихся после 01.01.70 и до 01.01.80. диапазон условий

13
Результат:
Таблица 4
Условие расширенного фильтра
Вариант Условие
0
Получить информацию о принятых рабочими в период с 01.01.2000 по
01.01.2008 в любой из цехов
1
Получить информацию о женщинах, фамилии которых заканчиваются на -ова или -ева, работающих либо в коммерческом, либо бухгалтерии с
2005 года
2
Получить информацию о работниках цехов, имеющих оклады от 30 до
40 тыс.
3
Отобразить информацию о студентах юридического и филологического факультета, сдававших экзамен на удовлетворительно или хорошо.
4
Отобразить информацию о студентах Ю1 с оценками хорошо и Ю2 с оценками отлично.
5
Отобразить информацию о студентах экономического факультета, ро- дившихся в 1992 году.
6
Отобразить информацию о возвращенных после 20 января товарах, произведенных в Германии
7
Отобразить информацию о клиентах, купившихпылесосы, фены или
утюгив период с 15 по 20 января. результат

14 8
Отобразить информацию о товарах, дороже 5 тыс., поставленныхиз
Китая или России.
9
Отобразить информацию о менеджерах, осуществлявших продажи в период с 15 по 20 января и возврат в этот же период.
1.5. Подведение промежуточных итогов
Задание. При помощи команды Промежуточные итоги в группе Струк-
тура на вкладке Данные можно автоматически подсчитать промежуточные и общие итоги в списке для столбца.
Но прежде чем подводить итоги,
нужно обязательно отсортировать список соответствующим образом
(согласно варианту).
Для подведения итогов можно использовать различные функции: Сумма,
Количество значений, Среднее, Максимум, Минимум, Произведение и др.
Команда Данные- Промежуточные итоги создаст промежуточные и об- щие итоги. При выводе промежуточных итогов Excel всегда создает струк- туру списка, с помощью символов структуры можно отобразить список с нужным уровнем детализации данных.
Например:
Необходимо подсчитать для каждого отдела предприятия сумму окладов сотрудников (рис.1.)
Рис 1. Пример