Файл: 3151.pdf

Расширенные

возможности

Microsoft Excel 2007 

Надежда Алтынова 
Центр Компьютерного Обучения 
«Специалист» при МГТУ им. Баумана 
2007 

Центр Компьютерного Обучения «Специалист»  

www.specialist.ru  

2

Алтынова Н

.

А

.

, 

Центр Компьютерного Обучения

«

Специалист

»

, 

2007

Расширенные возможности Microsoft Excel 2007. Решение практических задач. 

Методическое пособие к курсу

:

«

Расширенные возможности 

Microsoft

Excel

2007»

Все  названия  программных  продуктов  являются  зарегистрированными  торговыми 
марками  соответствующих  фирм

.

  Никакая  часть  настоящего  издания  ни  в  каких 

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

,

 будь то электронные и механические

,

 включая фотокопирование и 

запись на магнитный носитель

,

 если на это нет письменного разрешения автора

.

©

 Центр компьютерного обучения 

«

Специалист

»

 при МГТУ им

.

 Н

.

Э

.

 Баумана

,

2007

 г

.

Расширенные возможности Microsoft Excel 2007 

3

Оглавление 

ОБЩИЕ СВЕДЕНИЯ 

.................................................................................................................

6

Размеры листа .......................................................................................................................................... 6

Форматы файлов ..................................................................................................................................... 6

Ограничения Excel .................................................................................................................................. 6

ИНТЕРФЕЙС ПРОГРАММЫ 

..................................................................................................

7

Новые возможности 

.........................................................................................................

7

Ручной ввод ............................................................................................................................................... 7

Горячие клавиши .................................................................................................................................... 8

Режим просмотра Разметка страницы (Page Layout View) .............................................. 8

Строка Состояния ................................................................................................................................... 8

Предварительный просмотр вариантов (Live Preview) .................................................... 8

ВВОД ДАННЫХ 

.........................................................................................................................

9

Содержимое ячеек 

............................................................................................................

9

Формулы 

.............................................................................................................................

10

Знаки операций .................................................................................................................................... 10

Ссылки на ячейки ................................................................................................................................ 10

Использование имен в формулах ................................................................................................ 11

Заполнение ячеек списками 

.....................................................................................

12

Копирование с автозаполнением ............................................................................................... 12

Создание пользовательских списков........................................................................................ 12

ПРОВЕРКА ДАННЫХ 

...........................................................................................................

13

Проверка при подготовке таблицы к заполнению ........................................................... 13

Проверка при поиске неверных данных в таблице .......................................................... 14

ИМПОРТ ДАННЫХ 

................................................................................................................

15

Импорт из текстовых файлов ....................................................................................................... 15

Импорт из баз данных MS Access ................................................................................................ 16

Импорт с Web‐страниц ..................................................................................................................... 16

Работа с импортированными данными 

...............................................................

18

Создание запросов к базам данных 

.......................................................................

19

ФОРМАТИРОВАНИЕ 

............................................................................................................

20

Создание пользовательских форматов 

................................................................

20

Форматы для дат и времени .......................................................................................................... 20

Часы, минуты и секунды.................................................................................................................. 20

Форматы для числовых данных .................................................................................................. 21

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

........................................................................................

22

Установка условного форматирования ячеек ..................................................................... 22

Редактирование условного форматирования ..................................................................... 23

Создание правила с использованием формулы .................................................................. 23

Центр Компьютерного Обучения «Специалист»  

www.specialist.ru  

4

ВЫЧИСЛЕНИЯ 

........................................................................................................................

24

Функции 

..............................................................................................................................

24

Способы вставить функцию ........................................................................................................... 24

Математические функции 

..........................................................................................

25

Функции округления.......................................................................................................................... 25

Сложить если… ...................................................................................................................................... 26

Статистические функции

............................................................................................

26

Счет, Среднее если… ............................................................................................................................ 26

Ссылки и массивы 

..........................................................................................................

27

Финансовые функции 

...................................................................................................

29

Решение задач ........................................................................................................................................ 30

Логические функции 

....................................................................................................

31

ФОРМУЛЫ МАССИВОВ 

.......................................................................................................

32

Операции с массивами

:

................................................................................................

32

Ввод формулы массива 

................................................................................................

32

Результат формулы – массив ответов ...................................................................................... 32

Результат формулы – ответ в одной ячейке ......................................................................... 33

Изменение формулы массива 

...................................................................................

33

РАБОТА С БАЗАМИ ДАННЫХ 

...........................................................................................

34

Правила организации и ведения баз данных 

(

таблиц

)

................................

34

Преобразование диапазона ячеек в Таблицу 

....................................................

34

Преимущества использования таблиц ..................................................................................... 34

Сортировка 

........................................................................................................................

35

Обычный диапазон данных ............................................................................................................ 35

Данные оформлены в виде таблицы ......................................................................................... 36

Фильтрация 

.......................................................................................................................

37

Автофильтр (простые условия отбора) ................................................................................... 37

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

Удаление дубликатов в области данных 

.............................................................

39

ПОДВЕДЕНИЕ ИТОГОВ 

.......................................................................................................

40

Многоуровневые итоги 

...............................................................................................

40

Группировка и создание структуры 

......................................................................

40

КОНСОЛИДАЦИЯ 

..................................................................................................................

41

СВОДНЫЕ ТАБЛИЦЫ 

..........................................................................................................

42

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

.......................................................................................

42

Настройка параметров поля .......................................................................................................... 43

Изменение способа отображения итогов ............................................................................... 44

Повторное использование одного поля в отчете сводной таблицы ....................... 45

Вычисления в сводной таблице 

..............................................................................

45

Создание вычисляемого поля: ...................................................................................................... 45

Создание вычисляемого объекта: ............................................................................................... 46

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GetPivotData) ........................ 46

Изменение оформления отчета сводной таблицы 

.........................................

47

Группировка в сводных таблицах 

..........................................................................

47

Поля Дата/Время .................................................................................................................................. 47

Числовые поля ....................................................................................................................................... 47

Текстовые поля ..................................................................................................................................... 47

Сводные таблицы по нескольким диапазонам консолидации 

.................

48

Сводные диаграммы 

.....................................................................................................

50

Создание сводной диаграммы ...................................................................................................... 50

Работа со сводной диаграммой .................................................................................................... 50

Вкладки группы Работа со сводными диаграммами ........................................................ 51

Расширенные возможности Microsoft Excel 2007 

5

Обновление сводных таблиц и сводных диаграмм 

........................................

51

Очистка макета сводной таблицы

,

 очистка фильтров 

.................................

51

АНАЛИЗ 

«

ЧТО

–

ЕСЛИ

»

(WHAT–IF

ANALYSIS)

.............................................................

52

Сценарии 

............................................................................................................................

52

Создание нового сценария ............................................................................................................. 52

Создание отчета ................................................................................................................................... 54

Вывод подходящего сценария в ячейки листа .................................................................... 54

Подбор параметра 

..........................................................................................................

55

Поиск решения 

(Solver)

...............................................................................................

56

Подключение надстройки Поиск решения: .......................................................................... 57

Работа с командой Поиск решения: .......................................................................................... 57

Таблица данных 

..............................................................................................................

58

МАКРОСЫ 

................................................................................................................................

59

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

...........................................................................................................

59

Запись макроса (использование макрорекордера) .......................................................... 59

Создание макроса в среде VBA ..................................................................................................... 61

Параметры безопасности 

...........................................................................................

61

Настройка уровня безопасности ................................................................................................. 61

Запуск макроса 

................................................................................................................

62

Запуск макроса через диалоговое окно Макрос ................................................................. 62

Запуск макроса «горячими клавишами» ................................................................................ 62

Создание кнопок запуска макроса ............................................................................................. 63

Создание собственных функций в редакторе 

VBA

.........................................

64

ЗАЩИТА ЯЧЕЕК

,

 ЛИСТОВ И РАБОЧИХ КНИГ 

...........................................................

65

Шаг 

1.

 Защита ячеек листа от изменений 

..........................................................

65

Выборочная защита диапазонов для разных пользователей ..................................... 66

Шаг 

2.

 Защита листов книги 

.....................................................................................

66

Шаг 

3.

 Защита паролем от открытия файла 

......................................................

66

ОСОБЕННОСТИ СОВМЕСТНОГО ИСПОЛЬЗОВАНИЯ КНИГ 

..................................

67

Создание книги с общим доступом 

........................................................................

67

1 способ. Через команду Доступ к книге ................................................................................. 67

2 способ. Через команду Исправления – Выделить исправления ............................ 67

3 способ. Создание общей книги, защищенной паролем ............................................... 68

ПРИЛОЖЕНИЕ 

.......................................................................................................................

69

Список функций 

Excel

 по категориям

:

.................................................................

69

ФИНАНСОВЫЕ (FINANCIAL) ........................................................................................................... 69

ДАТА И ВРЕМЯ (DATE & TIME) ...................................................................................................... 71

МАТЕМАТИЧЕСКИЕ (MATH & TRIG) ........................................................................................... 72

СТАТИСТИЧЕСКИЕ (STATISTICAL) .............................................................................................. 75

ССЫЛКИ И МАССИВЫ (LOOKUP & REFERENCE) ................................................................... 78

РАБОТА С БАЗОЙ ДАННЫХ (DATABASE) .................................................................................. 79

ТЕКСТОВЫЕ (TEXT) ............................................................................................................................ 80

ЛОГИЧЕСКИЕ (LOGICAL) .................................................................................................................. 81

ПРОВЕРКА СВОЙСТВ И ЗНАЧЕНИЙ (INFORMATION) ........................................................ 81

ИНЖЕНЕРНЫЕ (ENGINEERING) .................................................................................................... 82

АНАЛИТИЧЕСКИЕ (CUBE) ................................................................................................................ 83