Красноярск 2009

НОУ СИБИРСКИЙ ИНСТИТУТ БИЗНЕСА, УПРАВЛЕНИЯ И ПСИХОЛОГИИ

Г.З. Погорелов

Т.М. Овчинникова

И.Н. Коюпченко

РАБОТА СО СПИСКАМИ И ТАБЛИЦАМИ MS EXCEL 2007

Учебное пособие

Рекомендовано Сибирским региональным учебно-методическим центром высшего профессионального образования для межвузовского использования в качестве учебного пособия по курсам «Информатика», «Информационные системы» и «Информационные технологии» для студентов, обучающихся по специальностям: 080109.65 " Бухгалтерский учет, анализ и аудит", 080507.65 " Менеджмент организации", 080102.65 " Мировая экономика", 030501.65 и 030500.62 " Юриспруденция", 030301.65 и 030300.62 Психология"

Красноярск 2009

УДК 004.78+002.6]: 338.43(075.8)

ББК 32.973.202: 65.2/.4Я73

Погорелов Г.З.: Работа со списками и таблицами MS Excel 2007. Учеб. пособие. / Г.З. Погорелов, Т.М. Овчинникова, И.Н. Коюпченко. Сиб. ин-т. бизнеса управления и психологии – Красноярск, 2009. 170 с.

Данное учебное пособие предназначено для самостоятельного освоения различных разделов дисциплин «Информатика», «Информационные системы» и «Информационные технологии» студентами всех форм обучения специальностей 080109.65 «Бухгалтерский учет, анализ и аудит», 080502.65 «Экономика и управление на предприятии (в отраслях)», 080507.65 «Менеджмент организации», 080111.65 «Маркетинг», 080105.65 «Финансы и кредит».

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ................................................................................................... 5

1. ПОНЯТИЯ О ТАБЛИЦАХ (СПИСКАХ) В MS EXCEL 2007.. 6

Вопросы к главе:............................................................................... 7

2. РАБОТА В ТАБЛИЦАХ MS EXCEL 2007.................................... 8

2.1. СОЗДАНИЕ ТАБЛИЦЫ MS EXCEL........................................... 8

2.2. РАБОТА С ТАБЛИЦОЙ MS EXCEL......................................... 10

Вопросы к главе:............................................................................. 12

3. РАБОТА С БОЛЬШИМИ СПИСКАМИ В MS EXCEL 2007. 13

3.1. ЗАКРЕПЛЕНИЕ ЗАГОЛОВКОВ СТРОК И СТОЛБЦОВ... 13

3.2. ГРУППИРОВАНИЕ, РАЗГРУППИРОВАНИЕ И СКРЫТИЕ ЛИСТОВ 14

3.3. РАБОТА С ОКНАМИ................................................................... 16

3.4. ЗАЩИТА ЛИСТОВ....................................................................... 18

Вопросы к главе:............................................................................. 20

4. СОРТИРОВКА СПИСКОВ............................................................... 20

Вопросы к главе.............................................................................. 25

5. ФИЛЬТРАЦИЯ СПИСКОВ.............................................................. 26

5.1. ФИЛЬТР ПО СПИСКУ ЗНАЧЕНИЙ......................................... 26

5.2. ФИЛЬТР ПО УСЛОВИЮ............................................................. 29

5.3. ФИЛЬТРАЦИЯ ДАННЫХ С ПОМОЩЬЮ РАСШИРЕННОГО ФИЛЬТРА 31

5.4. ФИЛЬТРАЦИЯ НА ОСНОВЕ ДАННЫХ ТИПА «ДАТА»... 48

Вопросы к главе.............................................................................. 50

6. ВЫЧИСЛЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ.................. 52

Вопросы к главе.............................................................................. 55

7. АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ И СВОДНЫХ ДИАГРАММ 56

7.1. ОСНОВЫ РАБОТЫ СО СВОДНЫМИ ТАБЛИЦАМИ И СВОДНЫМИ ДИАГРАММАМИ 56

7.2. СОЗДАНИЕ И РЕДАКТИРОВАНИЕ СВОДНОЙ ТАБЛИЦЫ 57

7.3. ИЗМЕНЕНИЕ ВИДА ДАННЫХ И ОТЧЕТА СВОДНОЙ ТАБЛИЦЕ 70

7.4. СОЗДАНИЕ И РЕДАКТИРОВАНИЕ СВОДНЫХ ДИАГРАММ 74

Вопросы к главе.............................................................................. 82

8. КОНСОЛИДАЦИЯ ДАННЫХ......................................................... 84

Вопросы к главе.............................................................................. 87

9. ИСПОЛЬЗОВАНИЕ МАСТЕРА ФУНКЦИЙ EXCEL ДЛЯ РАБОТЫ СО СПИСКОМ (БАЗОЙ ДАННЫХ) 88

9.1. ФУНКЦИИ РАБОТЫ С БАЗОЙ ДАННЫХ............................. 88

9.2. ИСПОЛЬЗОВАНИЕ МАСТЕРА ФУНКЦИЯ ДЛЯ РЕШЕНИЯ ПРАКТИЧЕСКИХ ЗАДАЧ 95

Вопросы к главе:........................................................................... 101

10. РАСЧЕТ ЗАДОЛЖНОСТИ (с использованием функций базы данных) 103

11. УКАЗАНИЯ К ВЫПОЛНЕНИЮ ТИПОВОГО ВАРИАНТАКОНТРОЛЬНОЙ РАБОТЫ 113

11.1. ТИПОВОЙ ВАРИАНТ КОНТРОЛЬНОЙ РАБОТЫ.......... 113

11.2. ПРИМЕР ВЫПОЛНЕНИЯ КОНТРОЛЬНОЙ РАБОТЫ.. 115

12. ЗАДАНИЕ ДЛЯ КОНТРОЛЬНОЙ РАБОТЫ........................ 134

ВАРИАНТ 0.......................................................................................... 134

ВАРИАНТ 1.......................................................................................... 136

ВАРИАНТ 2.......................................................................................... 138

ВАРИАНТ 3.......................................................................................... 140

ВАРИАНТ 4.......................................................................................... 142

ВАРИАНТ 5.......................................................................................... 144

ВАРИАНТ 6.......................................................................................... 146

ВАРИАНТ 7.......................................................................................... 148

ВАРИАНТ 8.......................................................................................... 150

ВАРИАНТ 9.......................................................................................... 152

ГЛОССАРИЙ........................................................................................... 154

ПРЕДМЕТНЫЙ УКАЗАТЕЛЬ.......................................................... 167

БИБЛИОГРАФИЧЕСКИЙ СПИСОК............................................. 168

ВВЕДЕНИЕ

Учебное пособие «Работа со списками и таблицами MS Excel 2007» посвящено изучению возможностей MS Excel 2007 при обработке таблиц (списков). Основой при его создании послужила предыдущая книга авторов[1] [22]. Далее, если это не будет дополнительно оговорено, в тексте будет употребляться просто Excel.

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

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

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

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

В качестве операционной системы выбран Microsoft Office XP посмотреть который можете в работах [1, 6]. Предполагается, что читатель уже ознакомлен с основами работы в MS Excel 2007 на уровне начинающего пользователя [23][2]. Усовершенствовать свои знания по MS Excel 2007 можно по книгам, список которых представлен в разделе MS Office Excel XP и 2007 библиографического описания.

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

ПОНЯТИЯ О ТАБЛИЦАХ (СПИСКАХ) В MS EXCEL 2007

Возможность работы с таблицами появилась в версии MS Excel 2007[3] [29] и представляет собой дальнейшее развитие концепции списков, использовавшихся в более ранних версиях Excel [22].

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

Таблица (список) состоит из следующих компонентов:

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

- Область столбцов. Столбцы в списке являются полями записи в базе данных.

- Заголовок столбца. Заголовки столбцов списка являются именами полей в базе данных.

- Заголовок строки. Имена, присвоенные всем строкам данных.

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

- Строка итогов. Итоговая строка предназначена для вычислений на базе любого столбца данных.

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

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

В Excel предусмотрен ряд удобных средств для организации и обработки данных в таблице (списке). Чтобы воспользоваться этими средствами вводите данные в таблицу (список) в соответствии с нижеизложенными правилами.