Выберите команду Создать в меню Файл

1.    Выберите команду Создать в меню Файл.

2.    Чтобы создать новую пустую книгу, выберите вкладку Общие и дважды щелкните значок «Книга».

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

Создание формы

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

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

Открытие книги

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

Книги можно открывать и в сети интранет, если на предприятии имеется этот вид сети Web. Книги можно открывать на узлах HTTP в World Wide Web и узлах FTP в Интернете. Для получения доступа к Интернету и World Wide Web необходимо иметь подключение к Интернету либо через прямое соединение, либо через удаленный доступ поставщика услуг Интернета.

Использование свойств при поиске файлов

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

Определение местоположения файлов

При помощи диалогового окна Открыть (меню Файл) можно осуществлять поиск файлов на жестком или сетевом диске. Поиск может осуществляться по имени файла, типу файла, дате последнего изменения, а также по тексту, содержащемуся в файле или по свойствам файла. Свойствами называются данные о файле, используемые для его описания, такие как название, имя автора, предметная область, ключевые слова и другие сведения.

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

Для поиска файла по свойствам или по дополнительным условиям поиска нажмите кнопку Дополнительно. Например может осуществляться поиск файлов, содержащих текст "бюджет" или имеющих ключевое слово "Бюджет96". Условия поиска файлов могут быть сохранены.

Для ускорения поиска файлов в диалоговом окне Открыть в комплект Office включена программа Find Fast, которая запускается при двойном щелчке значка Поиск Файлов на панели управления Windows. Для ускорения поиска по содержимому и свойствам файла Find Fast использует индексацию. Для получения сведений об использовании Find Fast, обратитесь к меню Справка в диалоговом окне Поиск Файлов.

Настройка основных параметров книги или листа с помощью шаблона

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

Для изменения элементов форматирования или содержимого открывающихся при запуске Microsoft Excel новых книг следует создать стандартный шаблон книги. Книги, создаваемые с помощью нажатия кнопки Создать или с помощью выбора шаблона книги в диалоговом окне Создать (меню Файл) также основываются на стандартном шаблоне. Для определенных задач и проектов разрабатываются дополнительные, пользовательские шаблоны. После того, как шаблон создан, он может открываться, как любая книга и в него могут быть внесены любые необходимые изменения.

Чтобы изменять стандартные листы, которые вставляются с помощью команды Лист (меню Вставка), следует создать стандартный шаблон листа. Если в книгах используется несколько листов, то имеется возможность создания дополнительных пользовательских шаблонов листов.

Изменение формата или содержимого существующего шаблона

1.    Нажмите кнопку Открыть , а затем откройте шаблон, который необходимо изменить.

Шаблоны обычно хранятся в папке «Шаблоны», которая находится в папке Office или Microsoft Excel, в папке XLStart или в другой дополнительной папке автозагрузки.

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

3.    Нажмите кнопку Сохранить.

Эти изменения влияют только на новые книги или листы, созданные после смены шаблона. На существующие книги или таблицы, основанные на предыдущей версии шаблона, никакого влияния не оказывается.

Закрытие книги

·     Выберите команду Закрыть в меню Файл.

Чтобы закрыть все открытые книги без выхода из программы, нажмите клавишу SHIFT и, не отпуская ее, выберите команду Закрыть все в меню Файл.

Копирование файла

Для получения сведений о копировании файла в среде Windows NT Workstation 3.51 см. документацию по Windows NT Workstation.

1.    Выберите команду Открыть .

2.    В поле Папка выберите диск или папку, содержащую копируемый файл.

3.    Выделите один или несколько файлов.

Если сразу файл найти не удается, попробуйте воспользоваться средствами поиска файлов (указать дополнительные условия поиска, включить поиск по подкаталогам и т.п.).

4.    Щелкните правой кнопкой мыши файл, который необходимо скопировать, а затем в контекстном меню выбрать команду Копировать.

5.    В поле Папка выберите диск или папку, в которую необходимо скопировать файл.

6.    Выберите из списка папку, в которую нужно скопировать файл.

7.    Убедитесь, что не выбрано никаких файлов, а затем в контекстном меню выберите команду Вставить.

Если в списке выбран какой-нибудь файл, то команда Вставить недоступна.

Удаление файла

1.    Нажмите кнопку Открыть .

2.    В поле Папка выберите диск или папку с файлом, который нужно удалить.

3.    Выделите один или несколько файлов.

Если сразу файл найти не удается, попробуйте воспользоваться средствами поиска файлов (указать дополнительные условия поиска, включить поиск по подкаталогам и т.п.).

4.    Щелкните правой кнопкой мыши уадяемый файл и затем в контекстном меню выберите команду Удалить.

Перемещение файла

Для получения сведений о перемещении файла в среде Windows NT Workstation 3.51 см. документацию по Windows NT Workstation.

1.    Выберите команду Открыть .

2.    В поле Папка выберите диск или папку, содержащую файл, который необходимо переместить.

3.    Выделите один или несколько файлов.

Если сразу файл найти не удается, попробуйте воспользоваться средствами поиска файлов (указать дополнительные условия поиска, включить поиск по подкаталогам и т.п.).

4.    Щелкните правой кнопкой мыши файл, который необходимо переместить, а затем в контекстном меню выберите команду Вырезать.

5.    В поле Папка выберите диск или папку, в которую необходимо скопировать файл.

6.    Выберите из списка папку, в которую необходимо переместить файл.

7.    Убедитесь, что не выбрано никаких файлов, а затем в контекстном меню выберите команду Вставить.

Если в списке выбран какой-нибудь файл, то команда Вставить недоступна.

Переименование файла

1.    Выберите команду Открыть .

2.    В поле Папка выберите диск или папку, содержащую файл, который необходимо переименовать.

3.    Выделите один или несколько файлов.

Если сразу файл найти не удается, попробуйте воспользоваться средствами поиска файлов (указать дополнительные условия поиска, включить поиск по подкаталогам и т.п.).

4.    Щелкните правой кнопкой мыши файл, который необходимо переименовать, затем в контекстном меню выберите команду Переименовать.

5.    Введите новое имя файла и нажмите клавишу ENTER.

Работа с книгами и листами

Общие сведения о книгах и листах Microsoft Excel

Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи.

Листы служат для организации и анализа данных. Можно вводить и изменять данные одновременно на нескольких листах, а также выполнять вычисления на основе данных из нескольких листов. При создании диаграммы ее можно поместить на лист с соответствующими данными или на отдельный лист диаграммы.

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

Ввод чисел, текста, даты или времени суток

1.    Укажите ячейку, в которую необходимо ввести данные.

2.    Наберите данные и нажмите клавишу ENTER или клавишу табуляции.

При вводе даты используйте точку или дефис в качестве разделителя, например, 09.05.96 или Янв-96.

Для отображения времени суток в 12-часовом формате введите букву a или p, отделенную пробелом от значения времени, например 9:00 p. В противном случае время будет интерпретировано на основе 24-часового формата.

Ввод формулы

1.    Укажите ячейку, в которую необходимо ввести формулу.

2.    Введите = (знак равенства).

Если нажать кнопку Изменить формулу или Вставка функции , автоматически вставляется знак равенства.

3.    Введите формулу.

4.    Нажмите клавишу ENTER.

Ввод одного и того же значения в несколько ячеек одновременно

1.    Выделите ячейки, в которые необходимо ввести данные.

Выделенные ячейки могут быть как смежными, так и несмежными.

2.    Введите данные и клавиши CTRL+ENTER.

Ввод и изменение одного и того же значения на нескольких листах

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

3.    Выберите листы, на которые необходимо ввести данные.

4.    Укажите ячейку или выделите диапазон ячеек, в которые необходимо ввести данные.

5.    Введите или измените данные в первой выделенной ячейке.

6.    Нажмите клавишу ENTER или клавишу табуляции.

Данные будут автоматически введены во все выбранные лист

Быстрое заполнение повторяющихся данных в столбце

Если несколько первых символов, вводимых в ячейку, совпадают с символами записи, ранее введенной в этом столбце, то недостающая часть набора будет произведена автоматически. В Microsoft Excel автоматический ввод производится только для тех записей, которые содержат текст или текст в сочетании с числами. Записи, полностью состоящие из чисел, дат или времени, необходимо вводить самостоятельно.

·     Для подтверждения предлагаемого варианта, нажмите клавишу ENTER.

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

·     Для замены автоматически введенных символов продолжите ввод самостоятельно.

·     Для удаления автоматически введенных символов нажмите клавишу BACKSPACE.

Вариант ввода также можно выбрать из списка записей, которые уже имеются в столбце. Чтобы просмотреть список, нажмите клавиши ALT+СТРЕЛКА ВНИЗ, или укажите ячейку с помощью правой кнопки мыши, а затем выберите команду Выбор из списка в контекстном меню.

Ввод чисел с заданным количеством десятичных разрядов или конечных нулей

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

1.    Выберите команду Параметры в меню Сервис, а затем – вкладку Правка.

2.    Установите флажок Фиксированный десятичный формат при вводе, десятичных разрядов.

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

Чтобы автоматически добавлять нули (0) при вводе больших целых чисел, укажите отрицательное число, обозначающее количество нулей. Например, для автоматического ввода 283000 при наборе 283 введите -3 в поле десятичных разрядов.

При вводе чисел в указанное место вставляется десятичная точка или добавляется определенное количество нулей. Изменение параметра Фиксированный десятичный формат при вводе не оказывает влияния на введенные до этого данные.

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

Автозаполнение, основанное на смежных ячейках

С помощью перетаскивания маркера заполнения ячейки можно копировать ее в другие ячейки той же строки или того же столбца. Если ячейка содержит число, дату или период времени, который может являться частью ряда, то при копировании происходит приращение ее значения. Могут создаваться пользовательские списки автозаполнения для часто используемых значений, например, названий торговых районов компании.

Перемещение и прокрутка внутри листа

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

Чтобы перейти Сделайте следующее

На одну строку вверх или вниз Нажмите кнопку на вертикальной полосе прокрутки.

На один столбец влево или вправо Нажмите кнопку на горизонтальной полосе

прокрутки.

На один экран вверх или вниз Укажите вертикальную полосу прокрутки выше или

ниже бегунка.

На один экран влево или вправо Укажите горизонтальную полосу прокрутки слева или

 справа от бегунка.

На большое расстояние Перетащите бегунок на нужную позицию. Если лист

слишком большой, удерживайте при перетаскивании

клавишу SHIFT.

Выделение ячеек на листе

Выполнение большей части команд и задач в Microsoft Excel становится возможным после выделения ячеек, с которыми необходимо произвести те или иные действия.

Выделение ячеек в соответствии с содержащимися в них данными

1.    Выделите диапазон, содержащий тип ячеек, которые нужно выделить.

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

2.    В меню Правка выберите команду Перейти.

3.    Нажмите кнопку Выделить.

4.    Установите нужные параметры.

Чтобы получить справку о параметре, щелкните знак вопроса, а затем ѕ параметр.

Изменение данных на листе

Изменение содержимого ячейки

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

2.    Измените содержимое ячейки.

3.    Для сохранения изменений нажмите клавишу ENTER.

Для отмены изменений нажмите клавишу ESC.

Очистка и удаление ячеек, строк и столбцов

При удалении ячейки Microsoft Excel так перемещает соседние ячейки, чтобы они заполнили освободившееся место. При очистке ячейки удаляется ее содержимое (формулы и данные), форматы и примечания, а сама ячейка остается на листе.

Поиск и замена данных

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

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

Перемещение и копирование ячеек

При использовании перетаскивания, а также команд Вырезать, Копировать и Вставить, Microsoft Excel полностью копирует ячейку, включая формулы и возвращаемые ими значения, примечания и форматы.

Если область копирования содержит скрытые ячейки, они также будут скопированы. Существует возможность просмотреть все ячейки, которые содержатся в скрытых строках или столбцах области вставки.

Проверка орфографии и автоматическое исправление ошибок

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

Средства автозамены позволяют исправлять часто встречающиеся ошибки при вводе слов; например, можно изменить «авш» на «ваш» и «чтоыб» на «чтобы». Список таких слов можно дополнять самостоятельно.

Формат словарей и списка автозамены Microsoft Excel соответствует другим приложениям Microsoft Office.

Форматирование листов

Копирование форматов из одной ячейки или диапазона ячеек в другой

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

2.    Нажмите кнопку Формат по образцу.

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

Изменение размера, шрифта и других форматов текста

При помощи кнопок на панели инструментов Форматирование можно изменить шрифт, размер шрифта и цвет шрифта. Если некоторые данные в ячейке не видны, то чтобы вместить их, текст можно сжать. Затем при изменении ширины столбца размер текста в Microsoft Excel будет меняться автоматически. На вкладке Шрифт (в меню Формат команда Ячейки) можно вычеркнуть выделенный текст или сделать его «перечеркнутым», а также придать символам формат верхнего и нижнего индексов.

Отображение нескольких строк текста внутри ячейки

1.    Выделите ячейки, формат которых необходимо изменить.

2.    В меню Формат выберите команду Ячейки, а затем перейдите к вкладке Выравнивание.

3.    В поле Отображение установите флажок переносить по словам.

Форматирование сводной таблицы

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

1.    Выделите форматируемую часть сводной таблицы.

2.    Воспользуйтесь кнопками панели инструментов Форматирование и командами меню Формат, чтобы изменить форматирование.

3.    Выделите ячейку сводной таблицы. На панели инструментов Сводные таблицы выберите команду Параметры в меню Сводная таблица и установите необходимые параметры, чтобы отформатировать сводную таблицу.

Для получения справки по каждому элементу управления нажмите кнопку со знаком вопроса и щелкните необходимый элемент управления.

Использование рамок в ячейках

1.    Выделите ячейки, в которые необходимо добавить рамку.

2.    Для использования рамки, выбранной в прошлый раз, нажмите кнопку Границы .

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

Затенение фона ячеек цветом

1.    Выделите ячейки, которые необходимо затенить.

2.    Для использования цвета, выбранного в прошлый раз, нажмите кнопку Цвет заливки .

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

Затенение ячеек узором

1.    Выделите ячейки, которые необходимо затенить.

2.    В меню Формат выберите команду Ячейки, а затем перейдите к вкладке Вид.

3.    Для использования цвета фона с узором выберите необходимый цвет в поле Заливка ячеек.

4.    Нажмите стрелку рядом с полем Узор, а затем выберите необходимый узор и цвет.

Если цвет узора не выбран, то он будет черным.

Отображение нескольких строк текста внутри ячейки

1.    Выделите ячейки, формат которых необходимо изменить.

2.    В меню Формат выберите команду Ячейки, а затем перейдите к вкладке Выравнивание.

3.    В поле Отображение установите флажок переносить по словам.

Изменение ширины столбца и высоты строки

Microsoft Excel позволяет изменять ширину столбцов и высоту строк. Он также позволяет установить ширину столбцов для листа по умолчанию. При определении ширины столбца по умолчанию устанавливается одинаковая ширина всех столбцов кроме тех, которые были изменены ранее.

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

1.    Выделите ячейки, формат которых необходимо изменить.

2.    В меню Формат выберите команду Ячейки, а затем перейдите к вкладке Число.

3.    В поле Числовые форматы выберите формат, а затем укажите встроенный формат, отвечающий требованиям.

4.    В поле Числовые форматы выберите (все форматы).

5.    В поле Тип для создания требуемого формата измените представленные там форматы. Изменение встроенного формата не удаляет его.

Быстрое форматирование ячеек из списков с помощью стилей и встроенных форматов таблицы

Для форматирования целого списка или другого диапазона ячеек, содержащего отдельные элементы — например, название строки или столбца, общие итоги или подробные данные — применяется автоформат. При этом для различных элементов таблицы используются отдельные форматы.

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

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

Создание нового стиля

1.    Выберите ячейку отформатированную с помощью комбинации форматов, которые необходимо включить в новый стиль.

2.    В меню Формат выберите команду Стиль.

3.    Введите имя для стиля в соответствующие поле.

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

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

Создание экранной или печатной формы. Создание формы

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

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

Форматирование листа с целью использования в качестве формы

Чтобы создать привлекательную и легкую для заполнения форму, достаточно простых способов форматирования. Например, для установки рамки для формы можно изменить сетку листа путем изменения ширины столбцов и высоты строк, а также объединяя ячейки. Объемное затенение и рамки позволяют выделить ячейки, в которые пользователи должны вводить данные. Как образец для создания собственной формы можно использовать стандартную профессиональную форму.

Линии сетки в печатных и электронных формах

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

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

·     Печатные формы

В случае, если пользователи будут заполнять форму на бумаге, особенно важен вопрос оформления и формата формы, с тем, чтобы ее было легко читать и заполнять. Например, если пользователи должны написать несколько строк данных, можно использовать светло-серые рамки ячеек для добавления строк к форме. Также можно добавить имеющие названия флажки и переключатели с помощью панели инструментов Формы. Элементы управления лучше устанавливать снятыми по умолчанию, так чтобы на бумаге появлялись пустые поля и кружочки, которые будет удобно отмечать. Для получения дополнительных сведений об инструментах форматирования форм нажмите кнопку .

·     Электронные формы

В случае, если пользователи будут заполнять форму на компьютере, можно использовать следующие вещи.

·     Так как форме присущи все функциональные свойства листа, введенные в нее данные можно использовать в формулах. Например, после того, как пользователи введут номера элементов и количества в форму заказа, с помощью формул можно вычислить итоги, налог и издержки при отгрузке.

·     Для электронных форм также могут использоваться дополнительные виды элементов управления на панели инструментов Формы. Например, пользователь имеет возможность выбрать пункты из элемента управления раскрывающегося списка (поля со списком), связанного с ячейками, содержащими список. Для получения дополнительных сведений об элементах управления, которые можно добавить на лист, нажмите кнопку .

Для форм и диалоговых окон, предназначенных для специальных программ Visual Basic для приложений или использования на Web, используются элементы ActiveX, расположенные на панели инструментов Элементы управления.

·     Для автоматизации обработки введенных данных и дальнейшей настройки формы в зависимости от введенных данных используются макросы. Например, при установке флажка «Женатый» макрос, связанный с элементом управления флажка может выдать поле «Имя супруга».

·     Для ограничения ввода в определенные ячейки листа или изменяемые поля элемента управления используется команда Проверка значения параметра в меню Данные, указывающая доступные ячейки. Для получения дополнительных сведений нажмите кнопку

·     Для включения в форму инструкций по ее заполнению можно добавить примечания к ячейкам. Чтобы предотвратить изменение формы пользователями, следует защитить лист. Перед этим нужно выбрать ячейки, в которые будут поступать данные, либо вводимые напрямую, либо связанные с элементом управления. Выберите команду Ячейки в меню Формат, а затем — вкладку Защита и снимите флажок Защищаемая ячейка. Чтобы предотвратить перемещение или изменение размера элемента управления, щелкните его правой кнопкой мыши и выберите команду форматирования элемента управления в контекстном меню. Выберите вкладку Защита и убедитесь, что установлен флажок Защищаемая ячейка. Затем установите указатель на пункт Защита в меню Сервис и выберите команду Защитить лист. .

·     Можно скрыть в книге ячейки или листы, содержащие данные, которые не должны видеть или изменять пользователи. Например, формулы или списки параметров, связанные с элементом управления списка.

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

Добавление кнопок, флажков и других элементов управления

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

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

Элементы управления, расположенные на панели инструментов Элементы управления, называются элементами ActiveX. Элементы ActiveX аналогичны элементам управления Visual Basic, их точно так же можно добавить к форме, созданной с помощью редактора Visual Basic. При добавлении элемента ActiveX на лист создается макрос, который сохраняется непосредственно вместе с элементом управления, а не только запускается при его выборе.

При настройке элементов ActiveX можно изменять некоторые их свойства. У элементов ActiveX существует также множество различных макрокоманд или событий, которые могут появиться при их использовании, например, отображение вида указателя когда он находится на элементе управления. Элементы ActiveX используются для создания форм и окон диалога для пользовательских программ Visual Basic. Панель инструментов Элементы управления содержит такие элементы управления, которых нет на панели инструментов Формы, например, выключатель и рамки объекта. Кроме того, доступны специальные элементы управления, созданные другими приложениями. Дополнительные сведения о свойствах элементов ActiveX приведены в справке по Visual Basic.

Создание формул и проверка книг

Вычисление значений в формулах

Формула является основным средством для анализа данных. С помощью формул можно складывать, умножать и сравнивать данные, а также объединять значения. Формулы могут ссылаться на ячейки текущего листа, листов той же книги или других книг. В следующем примере складывается значение ячейки B4 с числом 25. Полученный результат делится на сумму ячеек D5, E5 и F5.

·     Синтаксис формулы

Синтаксисом формул называется порядок, в котором вычисляются значения. Синтаксисом формулы задается последовательность вычислений. Формула должна начинаться со знака равенства (=), за которым следует набор вычисляемых величин. В следующем примере представлена формула, вычисляющая разность между числами 5 и 1. Результат выполнения отобразится в ячейке, в которой указана формула.

=5-1

·     Ссылки на ячейку

В формуле может быть указана ссылка на ячейку. Если необходимо, чтобы в ячейке содержалось значение другой ячейки, введите знак равенства, после которого укажите ссылку на эту ячейку. Ячейка, содержащая формулу, называется зависимой ячейкой ѕ ее значение зависит от значения другой ячейки. Формула может вернуть другое значение, если изменить ячейку, на которую формула ссылается. Следующая формула умножает значение ячейки B15 на число 5. Формула будет пересчитываться при изменении значения ячейки B15.

=B15*5

Формулы могут ссылаться на ячейки или на диапазоны ячеек, а также на имена или заголовки, представляющие ячейки или диапазоны ячеек.

·     Функции

В Microsoft Excel содержится большое количество стандартных формул, называемых функциями. Функции используются для простых или сложных вычислений. Наиболее распространенной является функция СУММ, суммирующая диапазоны ячеек. Несмотря на то, что пользователь может создать формулу, суммирующую значения несколько ячеек, функция СУММ обладает большими возможностями и может суммировать несколько диапазонов ячеек.

Синтаксис формулы

Синтаксис формулы определяет структуру или порядок элементов формулы. Формулы в Microsoft Excel подчиняются определенному синтаксису, в который входит знак равенства (=), вычисляемые элементы (операнды) и операторы. Операндами могут быть: константы, ссылки или диапазоны ссылок, заголовки, имена или функции.

По умолчанию, Microsoft Excel вычисляет формулу слева направо, начиная вычисление со знака равенства (=). Используя синтаксис написания формулы можно управлять процессом вычисления. Например, следующая формула возвращает число 11, так как умножение имеет больший приоритет над сложением и, поэтому, выполняется в первую очередь: сначала происходит умножение 2 на 3 (результат умножения равен 6), а затем полученное значение складывается с 5.

=5+2*3

Если для изменения синтаксиса воспользоваться скобками, то сначала произойдет сложение 5 и 2, а затем умножение полученного результата на 3. Следующая формула вернет число 21.

=(5+2)*3

Использование панели формул для ввода и изменения формул

С помощью панели формул можно легко вставить функцию в формулу. После вставки функции в панели формул отображается имя функции и ее аргументы, описание функции и аргументов, а также возвращаемое функцией и формулой значение. Чтобы отобразить панель формул, нажмите кнопку Изменить формулу в строке формул.

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

Ввод формулы для вычисления значения

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

Формула может содержать функции, которые дополняют набор операторов. Чтобы одновременно выполнить ряд вычислений, воспользуйтесь формулой массива.

Правка формулы

1.    Укажите ячейку, содержащую изменяемую формулу.

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

2.    Измените формулу в строке формул.

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

3.    Нажмите клавишу ENTER.

Если формула является формулой массива, нажмите клавиши CTRL+SHIFT+ENTER.

Ссылки на ячейку или на группу ячеек

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

По умолчанию в Microsoft Excel используются ссылки A1, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65536). Чтобы указать ссылку на ячейку, введите букву заголовка столбца, а затем номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем — ссылку на правый нижний угол диапазона.

Формулы массива и их ввод

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива воздействует на несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен иметь соответствующий номер строки и столбца. Формула массива создается так же, как и простая формула. Выделяется ячейка или группа ячеек, в которых необходимо создать формулу, вводится формула, а затем нажимаются клавиши CTRL+SHIFT+ENTER.

Если необходимо вычислить одно значение, Microsoft Excel может понадобиться выполнить несколько действий для возврата такого значения. Например, следующая формула вычисляет среднее значение только тех ячеек, принадлежащих диапазону D5:D15, которым в столбце А поставлена в соответствие строка «авиалиния Небеса». Функция ЕСЛИ находит ячейки в диапазоне A5:A15, содержащие строку «авиалиния Небеса», и возвращает значения, соответствующие этой строке в диапазоне D5:D15, функции СРЗНАЧ.

{=СРЗНАЧ(ЕСЛИ(A5:A15="авиалиния Небеса",D5:D15))}

Для вычисления нескольких значений в формуле массива, необходимо ввести массив в диапазон ячеек, имеющих соответствующее число строк или столбцов, как аргументы массива. Например, для ряда, записанного в столбцы А и В, функция ТЕНДЕНЦИЯ возвращает значения линейного тренда. Чтобы отобразить все вычисленные значения, необходимо ввести формулу в пять ячеек столбца C (C10:C15).

{=ТЕНДЕНЦИЯ(B10:B15,A10:A15)}

Кроме того, формулу массива можно использовать для вычисления одного или нескольких значений для последовательности, которая не указана на листе. В формулу массива можно включать константы так же, как это делается в простой формуле, но массив констант должен вводиться в определенном формате. Например, используя ряд, описанный в предыдущем примере, можно вычислить значения линейного тренда для любой другой точки. Так как в массив констант нельзя включать формулы или функции, для указания точки используется третий аргумент функции ТЕНДЕНЦИЯ:

{=ТЕНДЕНЦИЯ(B10:B15,A10:A15,{35246;35261})}

Пересчет формул

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

При пересчете в Microsoft Excel обновляются значения ячеек, значения которых изменяются в результате произведенных изменений. Такой подход позволяет избежать ненужных вычислений. Кроме того, Microsoft Excel всегда обновляет книги при открытии или сохранении.

В процессе пересчета в Microsoft Excel используются не те значения, которые отображаются на экране, а внутренние значения ячеек. Например, дата «22.6.96» является форматированным представлением некоторого числа. Задавая формат ячейки, можно изменять изображение на экране (например «22-6-96»), не изменяя внутреннего значения ячейки.

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

Циклические ссылки в формулах

Циклической ссылкой называется последовательность ссылок, при которой формула ссылается (через другие ссылки), сама на себя. Чтобы обработать такую формулу, необходимо вычислить значение каждой ячейки, включенной в замкнутую последовательность, используя результаты предыдущих итераций. По умолчанию, до тех пор пока не будут изменены соответствующие параметры, вычисления прекращаются после выполнения 100 итераций или после того, как изменение каждой величины не будет превышать 0,001 за одну итерацию.

Обработать формулы с циклическими ссылками в режиме обычных вычислений нельзя. Когда вводится формула с циклической ссылкой, появляется предупреждающее сообщение. Если циклическая последовательность ссылок образовалась случайно, нажмите кнопку OK. На экране появится панель инструментов Циклические ссылки и стрелки зависимостей, указывающие на каждую ячейку циклической ссылки. С помощью панели инструментов Циклические ссылки можно провести анализ циклической ссылки и изменить ссылки так, чтобы циклическая ссылка исчезла. Циклические ссылки часто используются в научных и инженерных расчетах.

При создании формул с циклическими ссылками может возникнуть необходимость изменить число итераций. Чтобы изменить число итераций, выберите команду Параметры в меню Сервис, а затем — вкладку Вычисления. Установите флажок Итерации. Далее введите максимальное число итераций и относительную погрешность вычислений.

Использование в формулах данных других листов и книг

Используя связывание или внешние ссылки, можно обмениваться данными, расположенными в разных листах и книгах. Связывание особенно полезно в тех случаях, когда отсутствует практика хранения больших моделей и систем расчетов в одной книге. Связывание не ограничено связыванием двух книг. Можно создавать иерархии связанных книг. В следующем примере функция СРЗНАЧ используется для вычисления среднего значения диапазона B1:B10 на листе «Маркетинг».

Внешние ссылки можно использовать для:

·     слияния данных нескольких книг. С помощью связывания книг отдельных пользователей или коллективов распределенные данные можно интегрировать в одну итоговую книгу. Исходные книги по-прежнему могут изменяться независимо от итоговой книги;

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

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

Поиск зависимых и влияющих ячеек

При создании на листе формул, можно получить подсказку о том, как связаны зависимые и влияющие ячейки. Для поиска таких ячеек служат команды панели инструментов Зависимости. При возврате формулой ошибочного значения, такого как #VALUE! или #DIV/0!, команды проверки (меню Сервис, команда Зависимости) позволяют найти источник ошибки.

Работа с диограммами

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

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

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

Добавление данных к диаграмме

Наиболее простым способом добавления данных к диаграмме является их копирование и вставка из рабочего листа на лист диаграммы.

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

Изменение типа диаграммы

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

1.    Выберите диаграмму, которую необходимо изменить.

2.    Для изменения типа диаграммы ряда данных выберите ряд данных.

Для изменения типа диаграммы в целом на самой диаграмме ничего не нажимайте.

3.    В меню Диаграмма выберите пункт Тип диаграммы.

4.    На вкладке Стандартные или Нестандартные выберите необходимый тип.

Для использования типов диаграмм конус, цилиндр или пирамида в объемной полосковой диаграмме или гистограмме выберите в поле Тип диаграммы в меню Стандартные типы пункт Цилиндр, Конус или Пирамида, а затем установите значок в поле Применить к.

Рекомендации по настройке данных для создания карты

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

·     Расположите данные на листе книги в виде столбцов. В одном из столбцов должны быть перечислены географические данные, например, названия стран или областей.

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

·     Если в верхней части столбцов имеются заголовки, их следует включить в выделяемую область.

Создание рисунков

Создание изображений ячеек, диаграмм и других объектов

Имеется возможность создания изображений данных листа Excel, диаграммы или других объектов с тем, чтобы впоследствии использовать эти изображения в качестве иллюстраций на листе или в документе другого приложения. Можно изменять размеры этих изображений, а также перемещать и изменять сами изображения, подобно любому другому рисованному объекту.

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

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

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

Управление списками

Использоване списка в качестве базы данных

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

·     Столбцы списков становятся полями базы данных.

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

·     Каждая строка списка преобразуется в запись данных.

Порядок сортировки, используемый по умолчанию

Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки.

При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текст "A100", то после сортировки она будет находиться после ячейки, содержащей "A1" и перед ячейкой, содержащей "A11."

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

1.    Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

2.    Текст, в том числе содержащий числа, сортируется в следующем порядке:

0 1 2 3 4 5 6 7 8 9 ' - (пробел) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

3.    Логическое значение ЛОЖЬ предшествует значению ИСТИНА.

4.    Все ошибочные значения равны.

5.    Пустые ячейки всегда помещаются в конец списка.

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

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки. Такой порядок сортировки также используется, если требуется отсортировать список в другом, особом порядке. Например, если в одном из столбцов списка содержатся значения «Низкий», «Средний» или «Высокий», можно создать такой порядок сортировки, что строки, содержащие «Низкий», будут первыми, «Средний» — следующими и «Высокий» — последними.

Отображение строк списка с использованием фильтра

Фильтры могут быть использованы только для одного списка на листе.

1.    Укажите ячейки в фильтруемом списке.

2.    Выберите пункт Фильтр в меню Данные, а затем — команду Автофильтр.

3.    Чтобы отфильтровать строки, содержащие определенное значение, нажмите кнопку со стрелкой в столбце, в котором содержатся искомые данные.

4.    Выберите значение в списке.

5.    Повторите шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других столбцах.

Чтобы отфильтровать список по двум значениям в одном столбце или применить отличные от равенства операторы сравнения, нажмите кнопку со стрелкой, а затем выберите пункт Условие.

Удаление фильтра из списка

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

·     Чтобы удалить фильтры для всех столбцов списка, выберите пункт Фильтр в меню Данные, а затем — команду Показать все.

·     Чтобы удалить автофильтр из списка, выберите пункт Фильтр в меню Данные, а затем — команду Автофильтр.

Дополнение и изменение списка с использованием формы

Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы.

Создание макроса на языке Visual Basic для получения внешних данных

Чтобы получить доступ к внешнему источнику данных с помощью макроса, написанного на языке Visual Basic, необходимо установить параметр Объекты DAO для Visual Basic в программе установки. Чтобы получить более подробные сведения, нажмите кнопку .

Чтобы использовать макрос, созданный в Microsoft Excel 5.0 или в более ранней версии, выберите команду Надстройки в меню Сервис и установите флажок Надстройка ODBC.

Чтобы получить более подробные сведения о создании макросов на языке Visual Basic, обращайтесь к справке по Visual Basic.

Сумирование данных в таблице

Способы подведения итогов данных

Для подведения итогов и анализа выделенных данных можно воспользоваться сводной таблицей. Кроме того, подвести итоги и упорядочить данные можно с помощью отчета Microsoft Access.

Вставка промежуточных итогов в список

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

Суммирование данных с помощью промежуточных итогов и структур

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

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

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

Консолидация данных

Предусмотрено несколько способов консолидации:

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

·     По расположению, если данные исходных областей находятся в одном и том же месте и размещены в одном и том же порядке. Используйте этот способ для консолидации данных нескольких листов, созданных на основе одного шаблона.

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

·     По категориям, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Используйте этот способ для консолидации данных листов, имеющих разную структуру, но одинаковые заголовки.

·     С помощью сводной таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает большую гибкость при реорганизации категорий.

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

Перед созданием сводной таблицы, использующей данные внешнего источника данных, необходимо сначала задать эти данные.

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

Если сводная таблица создается на основе данных, находящихся в списке или базе данных Microsoft Excel, выделите ячейку списка или базы данных.

2.    Выберите команду Сводная таблица в меню Данные.

3.    Следуйте инструкциям мастера сводных таблиц.

Анализ данных с помощью сводных таблиц.

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

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

·     Чтобы выделить всю таблицу, выберите команду Выделить (меню Сводная таблица, панель инструментов Сводные таблицы), а затем — команду Таблица целиком.

·     Чтобы выделить все метки элементов в поле, нажмите кнопку поля. В представленном примере можно нажать кнопку «Дата заказа», чтобы выделить метки «Кв.2» и «Кв.3». Если необходимо выделить данные вместе с метками, проверьте, что нажата кнопка Заголовки и данные.

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

·     Чтобы выделить все одинаковые элементы поля, можно указать только один элемент. Например, выберите «Батурин» для выделения всех строк «Батурин»:

·     Чтобы снять выделение всех одинаковых элементов поля и оставить только одно, необходимо выбрать элемент поля дважды. Например, если выбрать «

Батурин» второй раз, выделится строка «Батурин» для элемента поля «Мясные».

Если в сводной таблице содержится несколько полей строк, для выделения необходимых элементов нужно выполнить данную процедуру несколько раз. Например, если в сводной таблице содержится четыре поля строки, для выделения только одного элемента необходимо выбрать внутренний элемент в поле строки четыре раза.

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

·     В сводной таблице можно выделить несколько элементов поля. Сначала выделите один элемент. Нажмите клавишу SHIFT или CTRL и, удерживая ее нажатой, выделите остальные элементы внутри одного поля. Чтобы снять выделение с элемента, нажмите клавишу CTRL и, удерживая ее нажатой, снимите выделение с этого элемента.

·     Чтобы отключить выделение частей сводной таблицы и пользоваться обычным способом выделения, проверьте, что кнопка Разрешить выделение (команда Выделить, меню Сводная таблица, панель инструментов Сводные таблицы) не нажата. Чтобы после обновления и изменения структуры сводной таблицы форматы ее областей сохранялись, необходимо нажать кнопку Разрешить выделение.

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

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

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

Если сводная таблица создается на основе данных, находящихся в списке или базе данных Microsoft Excel, выделите ячейку списка или базы данных.

2.    Выберите команду Сводная таблица в меню Данные.

3.    Следуйте инструкциям мастера сводных таблиц.

Изменение структуры сводной таблицы

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

Изменение структуры сводной таблицы не затрагивает исходные данные.

Использование общих и промежуточных итогов в сводной таблице

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

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

Создание сводной таблицы на основе данных, находящихся во внешнем источнике данных

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

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

В следующих ситуациях передача внешних данных с помощью мастера сводных таблиц невозможна:

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

·     если для передачи данных используется запрос с параметрами, необходимо сначала создать запрос с параметрами, а затем создавать сводную таблицу на основе диапазона, содержащего внешние данные;

·     если для передачи данных через Интернет используется Web-запрос, необходимо сначала создать запрос на Web, а затем создавать сводную таблицу на основе диапазона, содержащего внешние данные.

Анализа «что-если»

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

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

1.    Либо в отдельный столбец, либо в отдельную строку введите список значений, которые следует подставлять в ячейку ввода.

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

2.    Если значения расположены в строке, то введите формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения. В том же столбце, но ниже наберите любые другие формулы.

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

4.    Выберите команду Таблица подстановки в меню Данные.

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

5.    Если же таблица подстановки данных ориентирована по строкам, то ссылка на ячейку ввода вводится в поле Подставлять значения по строкам в.

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

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

1.    В ячейку листа введите формулу, которая ссылается на две ячейки ввода.

2.    В тот же столбец ниже формулы введите значения подстановки для первой переменной. Значения подстановки для второй переменной вводятся в строку правее формулы.

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

4.    Выберите команду Таблица подстановки в меню Данные.

5.    В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для значений подстановки в строке.

6.    В поле Подставлять значения по строкам в введите ссылку на ячейку ввода для значений подстановки в столбце.

Поиск определенного результата для ячейки с помощью подбора значения другой ячейки

1.    Выберите команду Подбор параметра в меню Сервис.

2.    В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу.

3.    Введите искомый результат в поле Значение.

4.    В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение.

Создание сценария

1.    Выберите команду Сценарии в меню Сервис.

2.    Нажмите кнопку Добавить.

3.    Введите необходимое имя в поле Название сценария.

4.    Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки.

5.    Установите необходимые флажки в наборе флажков Защита.

6.    Нажмите кнопку OK.

7.    Введите необходимые значения в диалоговом окне Значения ячеек сценария.

8.    Чтобы создать сценарий, нажмите кнопку OK.

Для создания дополнительных сценариев нажмите кнопку Добавить, а затем повторите шаги с 3 по 7. После завершения создания сценариев нажмите кнопку OK, а затем – кнопку Закрыть в диалоговом окне Диспетчер сценариев.

Постановка задачи и оптимизация модели с помощью процедуры поиска решения

1.    В меню Сервис выберите команду Поиск решения.

2.    Если команда Поиск решения отсутствует в меню Сервис, установите соответствующую надстройку.

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

4.    Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению.

Чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению.

Чтобы установить значение в целевой ячейке равным некоторому числу путем изменения значений влияющих ячеек, установите переключатель в положение значению и введите в соответствующее поле требуемое число.

5.    В поле Изменяя ячейки введите имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек.

Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.

6.    В поле Ограничения введите все ограничения, накладываемые на поиск решения.

7.    Нажмите кнопку Выполнить.

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

Чтобы восстановить исходные данные, установите переключатель в положение Восстановить исходные значения.

Статистический анализ

Статистический анализ

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

Чтобы вывести список доступных инструментов анализа, выберите команду Анализ данных в меню Сервис. Если команда Анализ данных отсутствует в меню Сервис, то необходимо запустить программу установки Microsoft Excel. После установки пакета анализа его необходимо выбрать и активизировать с помощью команды Настройки. Чтобы получить более подробные сведения о пакете анализа, нажмите кнопку .

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

Открытие совместного доступа к книге

С помощью Microsoft Excel и других программ Microsoft Office можно открывать совместный доступ к файлам, и несколько пользователей могут работать с данными одновременно. Эти программы упрощают возможность просмотра чужих файлов и предоставления другим пользователям своих.

·     Существует возможность подготовки копий книги и распределения их для просмотра и внесения пометок.

Чтобы распределить книгу для просмотра, отправьте ее с помощью электронной почты или проведите ее в общу папку Microsoft Exchange. Если необходимо иметь отдельный набор комментариев от каждого лица, то отправьте им по отдельной копии книги. Чтобы получить совокупное множество комментариев, в котором каждый человек видит изменения, внесенные предыдущими пользователями, проведите или направьте книгу в общую папку.

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

·     Если необходимо, чтобы с данными одной книги одновременно работали несколько человек, то ее следует сохранить как общую книгу,, а затем сделать доступной в сети. Общая книга помещается в общий сетевой каталог или в общую папку на компьютере.