Табличный процессор MICROSOFT EXCEL

Табличный процессор MICROSOFT EXCEL

Теоретическое введение

 

Табличный процессор MS Excel используется для обработки данных. Обработка включает в себя:

- проведение различных вычислений с использованием мощного аппарата функций и формул;

- исследование влияния различных факторов на данные;

- решение задач оптимизации;                                                                                                   

- получение выборки данных, удовлетворяющих определенным критериям;                                                          

- построение графиков и диаграмм;                                                                                                                  

- статистический анализ данных.

При запуске MS Excel на экране появляется рабочая книга "Книга 1", содержащая 16 рабочих листов. Каждый лист представляет собой таблицу, состоящую из строк и столбцов. В этих таблицах хранятся данные, с которыми вам предстоит работать. Вы можете вводить в таблицы любую информацию: текст, числа, даты и время, формулы, рисунки, диаграммы, графики. Вся вводимая информация может быть обработана при помощи специальных функций. При помощи графических средств MS Excel вы можете рисовать, строить графики и диаграммы- MS Excel предоставляет в ваше распоряжение средства для работы с базами данных.

Запуск MS Excel

Наиболее просто запустить MS Excel из главного меню или из панели Microsoft Office. В главном меню нажмите мышью на опции «Создать документ» Microsoft Office, а на панели Microsoft Office - пиктограмму «Создать документ». В обоих случаях на экране появляется окно диалога "Создание документа". Для запуска MS Excel дважды нажмите мышью пиктограмму "Новая книга".

Управление файлами

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

Рисунок 1Общий вид окна электронной таблицы MS Excel 97

 

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

 

Создание нового документа

Для создания нового документа из меню Файл нужно выбрать команду Создать. Excel сразу присваивает новым документам имя Книга с добавлением текущего порядкового номера.

 

Загрузка рабочего документа

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

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

 

Сохранение документа

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

 

Защита данных

Для введения пароля необходимо вызвать команду Сохранить как из меню Файл. Затем нажмите кнопку Параметры. Введите пароль в поле Пароль для открытия файла:. При этом на экране вместо букв появляются звездочки.

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

Продублируйте пароль в поле Введите пароль еще раз: и нажмите кнопку OK.

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

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

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

 

Структура документов

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

 

Управление рабочими листами

Отдельные рабочие листы одного документа расположены друг под другом. С помощью именного указателя, расположенного в нижней части экрана, можно переходить с одного листа на другой. На именном указателе находятся корешки рабочих листов, расположенные в порядке возрастания номеров: Лист1, Лист2 и т.д..

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

Щелчок по пиктограмме с правой стрелкой сдвигает на единицу вправо диапазон (окно) видимости корешков рабочих листов. Щелчок по пиктограмме с левой стрелкой сдвигает этот диапазон на единицу влево. Стрелки с вертикальными штрихами позволяют перейти соответственно к первому и к последнему листам документа.

 

Добавление рабочих листов

Команды добавления расположены в меню Вставка. Пользователь может добавить в документ элементы следующих типов:

-  рабочие листы для создания таблиц;

-  диаграммы (в качестве элемента таблицы или на отдельном листе);

-  рабочий лист для записи макрокоманды в виде программного модуля (с использованием макросов MS Excel 4.0 или международного языка макросов);

-  рабочий лист для создания диалогового окна.

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

 

Перемещение рабочих листов

Установите указатель мыши на корешке рабочего листа, который нужно переместить, и щелчком правой кнопки мыши откройте контекстное меню. С помощью команды Переместить/скопировать откройте диалоговое окно с тем же названием и укажите в нем новую позицию переставляемого листа. Закройте окно Переместить/скопировать щелчком по кнопке OK, и рабочий лист займет новую позицию. Если включить опциональную кнопку Создавать копию, то данный рабочий лист останется на прежней позиции, а новую позицию займет его копия. Название копии листа будет образовано путем добавления порядкового номера к имени копируемого листа, например, Лист1(2).

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

Переместить рабочий лист (листы) можно также перетаскиванием их мышью в нужное место.

 

Построение таблиц

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

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

 

Выделение ячеек

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

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

Выделить несколько несмежных ячеек или диапазонов можно мышкой с нажатой клавишей Ctrl. При нажатой клавише Shift можно выделить диапазон ячеек от первой до последней ячейки диапазона указанных мышкой.

 

Отмена операций

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

Команда отмены после выполнения превращается в команду подтверждения изменения Вернуть действие Выполнив эту команду, можно снова ввести в таблицу измененные данные и т.д. Это можно сделать также с помощью клавиатурной комбинации Ctrl+Z или щелчка по соответствующей пиктограмме.

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

 

Копирование данных

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

В контекстном меню правой кнопки мыши также есть команда копирования. Для копирования можно также использовать комбинацию клавиш Ctrl+ Insert или соответствующую пиктограмму на панели инструментов.

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

 

Удаление данных

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

Все - удаляет содержимое и отменяет формат ячейки;

Форматы - отменяет только формат ячейки;

Содержимое - удаляет только содержимое ячейки;

Примечания - удаляет только комментарии к ячейке.

Команда удаления Очистить содержимое есть также в контекстном. С помощью этой команды можно удалить только содержимое ячеек. Тот же результат достигается просто нажатием клавиши Del. Если пользователь случайно удалил нужную информацию, токомандой Отменить очистку меню Правка – пользователь отменит команду удаления.

 

Форматирование данных

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

Рисунок 2 Формат ячейки

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

 

Табличные вычисления

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

Текст формулы, которая вводится в ячейку таблицы, должен начинаться со знака равенства (=), чтобы программа Excel могла отличить формулу от текста. Знак равенства вводится с клавиатуры или нажатием на кнопку  = - Редактирование формулы  в строке формул. После знака равенства в ячейку записывается математическое выражение, содержащее аргументы, арифметические операции и функции.

В качества аргументов в формуле обычно используются числа и адреса ячеек. Для обозначения арифметических операций могут использоваться следующие символы: + (сложение); - (вычитание); Ч (умножение); / (деление); ^ (возведение в степень).

Предположим, что в ячейке А1 таблицы находится число 100, а в ячейке В1 - число 20. Чтобы разделить первое число на второе и результат поместить в ячейку С1, в ячейку С1 следует ввести формулу =А1/В1 и нажать на клавиатуре Enter или в строке формул кнопку   P  . Адреса ячеек А1 и В1 вводятся латинским буквами.

Ввод адресов ячеек можно существенно упростить, используя манипулятор мыши. После ввода знака = следует просто щелкнуть мышью по первой ячейке, затем нажать / и щелкнуть по второй ячейке, затем нажать Enter.

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

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

 

Редактирование формул

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

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

Где был выполнен щелчок, появится мерцающий текстовый курсор. С этой позиции можно вводить новые символы с клавиатуры. Щелчок по кнопке Р (Esc)слева от формулы отменяет все произведенные изменения, так что содержимое ячейки остается без изменения. Щелчок по кнопке ь (Enter)слева от формулы подтверждает изменение, и в ячейке появляется новое значение.

 

Мастер функций

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

Для быстрого поиска необходимой функции они разделены по категориям: 10 недавно использовавшихся, Полный алфавитный перечень, Финансовые, Дата и время, Математические, Статистические, Ссылки и массивы, Работа с базой данных, Текстовые, Логические и Проверка свойств. Встроенный Мастер функций помогает правильно применять функции. Он позволяет построить и вычислить большинство функций.

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

Например:

=СУММ(А5:А9) – сумма ячеекА5, А6, А7, А8, А9;

=СРЗНАЧ(G4:G6) – среднее значение ячеекG4, G5, G6.

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

=ОКРУГЛ(СРЗНАЧ(H4:H8);2).

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

 

Рисунок 3 Строка формул и диалоговое окно функции Просмотр

 

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

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

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

После нажатия кнопки ОК появится диалоговое окно выбранной функции (например, функция Просмотр,

Необходимо в полях обязательных и необязательных параметров этого окна ввести аргументы функции (числовые значения или ссылки на ячейки). Чтобы указать аргументы, можно щелкнуть кнопку , находящуюся справа от поля, и выделить мышью ячейки, содержащие аргументы функции; для выхода из этого режима следует щелкнуть кнопку Х, которая находится справа от поля или клавишу Esc. Для завершения ввода формулы нажмите ОК.

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

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

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

Редактирование функций

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

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

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

Где был выполнен щелчок, появится мерцающий текстовый курсор. С этой позиции можно вводить новые символы с клавиатуры. Щелчок по кнопке Отмена Р слева от формулы отменяет все произведенные изменения, так что содержимое ячейки остается без изменения. Щелчок по кнопке   ь   (Enter)слева от формулы подтверждает изменение, и в ячейке появляется новое значение.

Вычисление суммы

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

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

В противном случае выделите группу ячеек, которые нужно сложить. Можно выделить несколько несмежных групп ячеек, используя клавишу Ctrl. Эту клавишу нужно удерживать в нажатом состоянии до тех пор, пока все группы не будут выделены.

Адреса групп можно ввести с клавиатуры. В этом случае нужно помнить, что адреса начальной и конечной ячеек группы разделяются двоеточием, а адреса различных групп - точкой с запятой (или запятой, в зависимости от установки опций Язык и стандарты в среде Windows). Кроме этого в формулу можно ввести и числовые слагаемые. После этого щелкните по пиктограмме автосуммирования, чтобы вычислить сумму.

 

Массивы формул

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

Массивы формул удобно использовать для введения однотипных формул и обработки данных в виде таблиц. Например, для вычисления модуля от чисел, размещенных в ячейках A1, B1, C1, D1, E1, вместо ввода формул в каждую ячейку можно ввести одну формулу–массив для всех ячеек. Microsoft Excel добавляет вокруг массива формул фигурные скобки { }, по которым его можно отличить.

Для создания массива формул необходимо:

- выделить ячейки, в которых должен находиться массив формул;

- ввести формулу обычным способом, указав в качестве аргументов группу ячеек-аргументов;

- в последнем окне вместо кнопки ОК нажать комбинацию клавиш Ctrl+Shift+Enter.

Для редактирования массива формул необходимо:

- выделить ячейки, в которых находится массив;

- щелкнуть мышью внутри строки редактирования и отредактировать формулу;

- нажать комбинацию клавиш Ctrl+Shift+Enter.

 

Рисунок 4 Массив формул

 

Другой пример формулы массива для одной ячейки: ={СУММ(ЕСЛИ(А1:Е1=-12; А2:Е2;0))},

Эта формула просматривает все ячейки массива А1:Е1 и если они равны –12 суммирует соответствующие ячейки строки 2.

 

Примечания

Определение типа корректных данных не приводит к форматированию ячейки. Чтобы отформатировать числа, даты или время, содержащиеся в ячейке, выберите в меню Формат _ Ячейки… и затем — вкладку Число. В списке Числовые форматы выберите нужный формат, а также требуемые параметры.

Разделение окон

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

- установить указатель мыши на горизонтальную или вертикальную вешку разбиения (он примет вид двунаправленной стрелки) и перетянуть в нужное место. Размеры подокон можно изменять, перетягивая вешку разбиения;

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

Чтобы отменить разделение окон, следует в меню Окно выбрать команду Снять разделение или дважды щелкнуть на вешке разбиения.

 

Создание нового окна

С помощью команды Новое меню Окно можно создать дополнительное окно для активной рабочей книги, чтобы просматривать различные части рабочей книги одновременно. В этом случае в заголовке окна после имени рабочей книги через двоеточие указывается номер нового окна. Например, второе окно рабочей книги Отчет будет названо Отчет:2.

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

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

 

Фиксация подокон

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

- чтобы зафиксировать горизонтальные заголовки, выделить строку ниже заголовков;

- чтобы зафиксировать вертикальные заголовки, выделить столбец справа от заголовков;

- в меню Окно выбрать команду Закрепить области.

Для фиксации и вертикальных, и горизонтальных заголовков необходимо:

- выделить ячейку, по которой следует зафиксировать заголовки (все строки выше выделенной ячейки и все столбцы слева от выделенной ячейки будут зафиксированы);

- в меню Окно выбрать команду Закрепить области.

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

Отменяется фиксация окон и подокон командой Снять закрепление областейменюОкно.

 

Создание примечаний

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

- рабочий лист используется совместно несколькими пользователями;

- рабочий лист большой и сложный;

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

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

- выделить ячейку, к которой следует добавить примечание;

- вызывать команду Примечание из меню Вставка;

- в поле, которое появилось, ввести примечание (размер поля можно изменить, перетягивая маркеры размера);

- щелкнуть мышью за пределами поля.

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

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

 

Работа с базами данных

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

 

Рисунок 6 База данных

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

 

Сортировка данных

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

В поле списка Сортировать по выбирается поле, по которому будут отсортированы данные, и тип сортировки:

по возрастанию – цифры сортируются по возрастанию, текст – в алфавитном порядке, логические выражения – ЛОЖЬ предшествует ИСТИНА.

по убыванию – сортировка в обратном порядке.

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

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

Рисунок 7 Сортировка данных

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

 

Формы данных

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

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

Для использования форм данных таблица должна иметь имена столбцов. Имена столбцов становятся именами полей в форме данных. Поле соответствует каждому столбцу таблицы. Форма данных автоматически раскрывается так, чтобы вывести на экран сразу все поля в данной таблице, до 32 полей за один раз. С помощью полосы прокрутки можно прокручивать записи базы данных. Позиция выведенной записи указывается в верхнем правом углу. Передвигаться по полям формы можно с помощью мыши и клавиш Tab (вниз), Shift+Tab (вверх). В правой части окна расположены следующие кнопки.

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

Удалить – удаляет выведенную запись, другие записи базы данных сдвигаются. Удаленные записи не могут быть восстановлены.

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

Назад – выводит предыдущую запись в списке. Если был определен критерий с помощью кнопки Критерии, то кнопка Назад выведет предыдущую запись из тех, которые удовлетворяют заданному критерию.

Далее – выводит следующую запись базы данных.

Критерии – очищает поля перед вводом критериев сравнения с операторами сравнения для поиска необходимого подмножества записей.

Правка – служит для выхода из режима ввода критериев. Доступна только после нажатия кнопки Критерии.

Очистить – удаляет существующий критерий из окна диалога. Доступна только после нажатия кнопки Критерии.

Закрыть – закрывает форму данных.

Для добавления записи к базе данных необходимо:

- выделить ячейку в таблице, к которой следует добавить запись;

- в меню Данные выбрать команду Форма;

-щелкнуть кнопку Добавить;

- заполнить поля новой записи;

- для перемещения к следующему полю нажать клавишу Тab;

- после ввода данных нажать клавишу Enter для добавления записи;

- после добавления всех необходимых записей, щелкнуть кнопку Закрыть.

Новые записи будут добавлены в конец базы данных.

 

Автофильтр

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов. С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Цена выбрать 160 р., то будут выведены только записи, у которых в поле Цена содержится значение 160 р.

 

Рисунок 8 Пример установки автофильтра

Если в поле списка выбрать пункт Условие …, то появится окно Пользовательский автофильтр. В верхнем правом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать одно из значений. В нижнем правом списке можно выбрать другой оператор, и в поле по левую сторону – значение. Когда включен переключатель И, то будут выводиться только записи, удовлетворяющие оба условия. При включенном переключателе ИЛИ будут выводиться записи, удовлетворяющие одному из условий.

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

Чтобы вывести все данны