Правила создания таблицы (списка)

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

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

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

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

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

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

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

Красноярск 2010

ББК 32.973.202:65.2/.4Я73

Рецензенты

д-р техн. наук, проф. СиБГАУ А.Н. Антамошкин кафедра прикладной информатики и математики СИБУП

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

Данное учебное пособие предназначено для самостоятельного освоения различных разделов дисциплин «Информатика», «Информационные системы» и «Информационные технологии» студентами всех форм обучения 080109.65 "Бухгалтерский учет, анализ и аудит", 080507.65 "Менеджмент организации", 080102.65 "Мировая экономика", 030501.65 и 030500.62.

ISBN 978-5-94969-044-4

Методические указания утверждены и одобрены к печати научно-методическим советом СИБУПа от 02.02.2009г. Протокол № 2

© Г.З. Погорелов © НОУ ВПО «Сибирский институт бизнеса, управления и психологи, 2009

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

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

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ................................................................................................... 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ВВЕДЕНИЕ

Учебное пособие «Работа со списками и таблицами 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 предусмотрен ряд удобных средств для организации и обработки данных в таблице (списке). Чтобы воспользоваться этими средствами вводите данные в таблицу (список) в соответствии с нижеизложенными правилами.

Правила создания таблицы (списка)