Основы работы на ПК

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

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. Если необходимо

иметь отдельный набор комментариев от каждого лица, то отправьте им по

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

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

пользователями, проведите или направьте книгу в общую папку.

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

объединения изменений каждой копии, либо слияния всех изменений в одну

копию книги.

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

несколько человек, то ее следует сохранить как общую книгу,, а затем

сделать доступной в сети. Общая книга помещается в общий сетевой каталог

или в общую папку на компьютере.

-----------------------

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Страницы: 1, 2, 3, 4, 5, 6, 7, 8