YourLib.net
Твоя библиотека
Главная arrow Информатика (А.В. Терехов, А.В. Чернышев, В.Н. Чернышев) arrow 6.2. Табличный процессор Microsoft Excel
6.2. Табличный процессор Microsoft Excel

6.2. Табличный процессор Microsoft Excel

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

6.2.1. ЗАПУСК ПРОГРАММЫ. ОСНОВНЫЕ КОМПОНЕНТА ОКНА EXCEL

   Excel можно запустить с рабочего стола Windows или через главное меню (например, Пуск > Программы > MicrosoftOffice > Excel). Рассмотрим основные элементы экрана Excel (рис. 6.2.1).

Рис. 6.2.1. Основные элементы экрана Excel 

Рис. 6.2.1. Основные элементы экрана Excel

   Главное меню представляет собой набор каскадных меню, посредством которых можно обратиться ко всем основным операциям Excel. Панель заголовка служит для перемещения окна Excel, отображения имен программы и рабочей книги (файла). Панели инструментов - это ряды кнопок (пиктограмм) и окон выбора, дающих быстрый доступ к опциям главного меню и позволяющих быстро ввести данные. Кнопки Свернуть, Развернуть, Закрыть - стандартные для Windows. Строка формул показывает адрес текущей ячейки и ее содержимое.
   Рабочий лист представляет собой то, что мы называем электронной таблицей.
   Статусная строка отображает сведения о выбранной команде или выполняемой операции. В правой части строки показано, какие функциональные клавиши включены, например, Caps Lock, Num Lock и др.

6.2.2. РАБОЧАЯ КНИГА И РАБОЧИЙ ЛИСТ

   Пользователю, работающему с Excel, приходится иметь дело с файлами рабочих книг, имеющих расширение .xls. Рабочая книга может включать рабочие листы, представляющие собой электронные таблицы, листы диаграмм, содержащие графики и гистограммы, листы модулей с текстами программ на языке Visual Basic и листы диалога с кнопками и другими элементами управления.
   Переключаться между листами рабочей книги можно при помощи ярлычков, кнопок прокрутки листов, а также клавиш Ctrl + PageUp - на лист вперед и Ctrl + PageDn - на лист назад. Рабочее поле представляет собой таблицу, в которую вводятся данные. Столбцы таблицы обозначаются латинскими буквами A, B, C, ..., Z, AA, AB, ..., AZ, BA, ..., IV - всего 256; строки нумеруются арабскими цифрами от 1 до 65 536 (в Excel_2002).
   Имя ячейки складывается из заголовка столбца и номера строки, на пересечении которых она находится, например, A1, Z324, AA12, IV16384. Полное имя ячейки также включает в себя имена файла рабочей книги и листа, на которых она расположена. Так, ячейка A1 на листе Лист1 в рабочей книге Книга1 имеет полное имя [Книга 1]Лист1!А
   В пределах одной книги ее название в имени ячейки можно не указывать. Аналогично, в пределах одного листа его имя в имени ячейки допускается не указывать.
   Перемещение по листу производится при помощи полос горизонтальной и вертикальной прокрутки или клавиш на клавиатуре: ←,↑,→,↓, Page Up, Page Dn.

6.2.3. РАБОТА С КНИГАМИ И ЛИСТАМИ

   Создание книги. При загрузке Excel автоматически создает новую книгу с именем Книга 1. Создать самому новую книгу можно через главное меню Файл - Создать..., с помощью кнопки Создать книгу в панели инструментов Стандартная или сочетания клавиш Ctrl + N.
   Запись книги в файл осуществляется через команды главного меню Файл > Сохранить и Файл > Сохранить как... Первая выполняет сохранение книги в тот же файл (расширение файла .xls), из которого она была загружена, вторая вызывает стандартный диалог Сохранение документа, в котором выбирается каталог и имя файла для записываемой на диск рабочей книги. Альтернативами команде Файл > Сохранить является использование кнопки Сохранить в панели инструментов и нажатие на клавиатуре комбинации клавиш Ctrl + S.
   Загрузка книги с диска производится или командой меню Файл > Открыть..., вызывающей стандартный диалог Открытие документа, или щелчком мыши по кнопке Открыть на панели инструментов, или комбинацией клавиш Ctrl + О.
   Закрытие книги выполняется через меню Файл > Закрыть. Если закрываемая книга содержит несохраненные изменения, то Excel предложит сохранить данный файл.
   Вставка листа. Добавление листа в рабочую книгу производится командой меню Вставка > Лист с присвоением новому листу имени, например, Лист 17.
   Переименовать лист можно опцией меню Формат > Лист > Переименовать... .
   Удаление листа выполняется командой меню Правка > Удалить лист. В результате будет удален текущий рабочий лист.
   Перемещение и копирование листа позволяет изменить порядок листов в книге (Правка > Переместить > Скопировать...).

6.2.4. ЯЧЕЙКИ. ВВОД ДАННЫХ И ФОРМАТИРОВАНИЕ

   В ячейки электронной таблицы вводится различная информация. Формат содержимого ячейки (группы ячеек) таблицы можно изменить, если навести на нее курсор и, нажав правую кнопку мышки, выбрать из контекстного меню команду Формат ячеек. Описание выбираемого формата приводится в этом же меню. Обычно используются следующие форматы: общий, числовой, текстовый, денежный, дата, время, экспотенциальный и др.
   Текстом считается любой набор символов. Числовая информация представляет собой комбинацию цифр с разделителем целой и дробной частей (как правило, это символ « , »). Для того, чтобы превратить число в текст, необходимо перед этим числом поставить апостроф, который не отображается на экране и является признаком текстовой информации.
   Формула представляет собой правило вычисления, записанное строго определенным образом. Отличительным признаком формулы является то, что она начинается со знаков =, + или -.
   Ввод данных в ячейку начинается с ее активизации клавишами управления курсором или щелчком левой кнопки мыши на нужной ячейке. Активная (текущая) ячейка имеет жирную контурную рамку и ее адрес отображается в левой части строки формул. После этого можно вводить информацию с клавиатуры и окончить ввод нажатием клавиши Enter. Вводимые данные отображаются как в самой ячейке, так и в строке формул.
   Удаление данных из ячейки производится клавишей Delete. Чтобы редактировать данные, активизируйте соответствующую ячейку и щелкните дважды мышью на ней, либо щелкните один раз в правой части строки формул.
   Для форматирования ячеек применяется панель инструментов Форматирование (аналогична панели Форматирование Word).
   Кнопки данной панели инструментов представляют пользователю лишь основные команды форматирования. Более полный набор средств для изменения формата ячеек доступен с помощью команды Формат > Ячейки.

6.2.5. ОПЕРАЦИИ КОПИРОВАНИЯ И ПЕРЕМЕЩЕНИЯ

   В Excel для перемещения и копирования данных используется стандартный механизм, использующий буфер обмена. Последовательность действий при перемещении (копировании) такова - сначала активизируется ячейка с копируемыми данными, затем следует команда Удалить в буфер (при перемещении) или Копировать в буфер (при копировании), после этого активизируется ячейка, в которую перемещается (копируется) информация и выполняется команда Вставить из буфера.
   Команда Удалить в буфер выполняется одноименной кнопкой в панели инструментов Стандартная, опцией меню Правка > Вырезать или сочетанием клавиш на клавиатуре Ctrl + X.
   Команде Копировать в буфер соответствует кнопка в панели инструментов, опция меню Правка > Копировать и сочетание клавиш Ctrl + C.
   Команда Вставить из буфера также имеет свою кнопку в панели инструментов Стандартная, в меню ей соответствует опция Правка > Вставить, а на клавиатуре - сочетание Ctrl + V.
   Кнопка Копировать формат предоставляет пользователю возможность скопировать формат ячейки.
   В дополнение ко всему вышесказанному, операции перемещения/копирования можно выполнять одной только мышью. Для перемещения данных подведите курсор мыши к рамке перемещаемой ячейки, затем нажмите левую кнопку и, не отпуская ее, перетащите ячейку в новое место. Для выполнения операции копирования необходимо во время перемещения удерживать в нажатом состоянии клавишу Ctrl. При этом у стрелки курсора мыши появится «плюс» - признак операции копирования.
   Для того, чтобы скопировать данные в соседние ячейки, можно воспользоваться маркером заполнения, представляющим собой маленький черный квадратик, который находится в правом нижнем углу рамки активной ячейки. Установите на него курсор мыши - он должен превратиться в черный крестик. Теперь, не отпуская левую кнопку мыши, растяните рамку на одну или несколько близлежащих ячеек.

6.2.6. БЛОКИ ЯЧЕЕК (ДИАПАЗОНЫ)

   Блок (диапазон) представляет собой объединение нескольких ячеек. Выделение блока производится при помощи мыши. Выделенные ячейки, кроме той, с которой начиналось выделение, отмечаются черным цветом. Несмежные группы ячеек выделяются мышью с нажатой клавишей Ctrl. Чтобы выделить целый столбец, необходимо щелкнуть левой кнопкой мыши по заголовку данного столбца; аналогично выделяется строка. Выделение всех ячеек на рабочем листе производится мышью с помощью прямоугольника, расположенного в левом верхнем углу листа.
   Выделенный диапазон обозначают через двоеточие B2 : D4, где B2 и D4 - имена левой верхней и правой нижней ячеек блока соответственно. Диапазон, состоящий из несмежных блоков ячеек записывается через точку с запятой, например, A1 : A4; C2 : E5.
   Операции очистки (удаления), копирования, перемещения и заполнения для блоков выполняются так же как и для отдельных ячеек.
   Рассмотрим заполнение вертикального блока последовательностью целых чисел, представляющую собой арифметическую прогрессию, т.е. 1, 2, 3, 4, 5, 6, 7, 8. Введите в первую ячейку, например, A1 начальное значение - 1, затем выделите мышью блок ячеек, в которые желаете поместить данные - A1 : A8. Выполните команду Правка > Заполнить > Прогрессия, раскроется окно диалога Прогрессия. В этом окне укажите расположение данных - по столбцам, тип прогрессии - арифметическая, шаг - 1 и нажмите кнопку ОК. В результате получим требуемый столбец цифр.

6.2.7. ФОРМАТИРОВАНИЕ РАБОЧЕГО ЛИСТА

   Если текстовая информация не умещается в ячейке, то она выводится на соседние. Однако, когда соседние ячейки заполнены, то не поместившиеся данные отсекаются рамками ячейки. При этом информация не теряется, а просто на экран выводится ее часть.
   Для того, чтобы изменить ширину столбца (например, C), поместите курсор мыши на границу разделения заголовков столбцов (C и D) и, не отпуская левую кнопку, сожмите или растяните данный столбец. Аналогично изменяется высота строки. Более точное изменение размеров ячеек осуществляется опциями меню Формат > Столбец > Ширина и Формат > Строка > Высота. Ширина текущего столбца и высота текущей строки задаются в пунктах.
   Для удаления активной ячейки выделенного блока ячеек, строки или столбца выбирается команда меню Правка > Удалить. После этого в появившемся окне диалога Удаление ячеек указывается режим удаления (со сдвигом влево, со сдвигом вверх и т.д.).
   Вставка новых ячеек, столбцов и строк выполняется опцией меню Вставка > Ячейки и последующим выбором в окне диалога Добавление ячеек, одного из режимов вставки: Ячейки, со сдвигом вправо, Ячейки, со сдвигом вниз, Стоку и Столбец. Строку и столбец можно вставить также командой Вставить > Строку и Вставить > Столбец соответственно. Следует отметить, что добавляемые ячейки, столбцы или строки располагаются перед активной ячейкой.

6.2.8. ФОРМУЛЫ. МАСТЕР ФУНКЦИЙ

   Формулы используются в Excel для выполнения расчетов, они позволяют производить вычисления в ячейках по данным, содержащимся в других ячейках. В качестве примера использования формул можно привести такие операции, как расчет итоговых сумм, средних значений, нахождения минимального/максимального значений и т.д. Формулы записываются при помощи арифметических знаков: сложения (+), вычитания (-), умножения (*), деления (/), возведения в степень (л); чисел; имен ячеек и диапазонов; а также встроенных функций. Порядок выполнения действий определяется приоритетом операций: в первую очередь выполняются действия в круглых скобках, затем - встроенные функции, возведение в степень, умножение (деление) и в последнюю очередь - сложение (вычитание).
   Формула всегда начинается со знаков =, + или -.
   Например, пусть в ячейки A1 : A5 занесены некоторые числа. Требуется посчитать в ячейке A6 сумму этих чисел, т.е. в ячейке A6 необходимо ввести формулу, суммирующую значения из A1, A2, ..., A5 и выводящую результат в A6. Для этого занесем в A6 формулу =A1+A2+A3+A4+A5, при этом вводимая информация будет отображаться как в самой ячейке, так и в строке формул. Нажав клавишу Enter, получим в ячейке A6 искомую сумму - число 15. Если теперь сделать активной ячейку A6, то в строке формул можно увидеть не число 15, а формулу =A1+A2+A3+A4+A5. Таким образом, введя в ячейках A1 : A5 новые данные, в ячейке А6 мы получим новую сумму - Excel автоматически выполняет пересчет формул.
   Для подсчета итоговых сумм можно использовать кнопку Автосуммирование, расположенную в панели инструментов Стандартная. При нажатии этой кнопки в текущей ячейке появляется встроенная функция СУММ, после чего необходимо мышью указать диапазон суммируемых ячеек и нажать Enter. В рассмотренном выше примере можно применить автоматическое суммирование и ввести в ячейке A6 встроенную функцию =СУММ(A1:A5).
   В общем виде встроенную функцию можно записать следующим образом: Имя функции (Аргументы), где Имя функции - имя встроенной функции (например, СУММ), а Аргументы - ячейка или диапазон ячеек, на которые действует функция (например, A1 : A5 или равнозначная запись A1; A2; A3; A4; A5).
   Встроенные функции можно вводить вручную с клавиатуры или при помощи мастера функций, который вызывается нажатием одноименной кнопки в панели инструментов Стандартная или в строке формул. Мастер функций также может вызываться посредством меню Вставка > Функция.
   Рассмотрим работу мастера функций на следующем примере. Исходные данные (числа) введены в первой строке рабочего листа в ячейках A1 : G1. Требуется определить максимальное значение и вывести его в ячейке A2.
   Сначала активизируем ячейку A2 и вызываем мастер функций. Действия в мастере функций состоят из двух шагов.
   В этом окне пользователь может выбрать требуемую функцию. В левой части этого окна выводятся категории функций: математические, статистические, логические и др. В правой части отображается полный перечень функций из выбранной категории. Внизу приводится подсказка: шаблон для функции, который помогает понять правила ее записи, и описание назначения функции. Выбираем из категории Статистические функцию МАКС, которая возвращает максимальное значение из списка аргументов. Чтобы перейти на второй шаг мастера, необходимо щелкнуть мышью на кнопке Далее. На втором задаются аргументы функции. Удобнее всего это делать при помощи мыши, если при этом окно мастера функций закрывает выделяемые ячейки, то его можно передвинуть за панель заголовка. Укажем мышью аргументы функции МАКС - диапазон ячеек A1:G1.
   Завершение работы мастера функций производится нажатием кнопки Готово. В результате в ячейку A2 мы занесли формулу =МАКС(A1:G1). Для исходных данных функция возвратит число 17.

6.2.9. КОПИРОВАНИЕ ФОРМУЛ. ОТНОСИТЕЛЬНЫЕ И АБСОЛЮТНЫЕ ССЫЛКИ

   Часто приходится повторять формулы для нескольких ячеек или заполнять диапазоны ячеек сходными формулами. В этом случае для упрощения ввода можно использовать копирование формул. Например, рассмотрим статистику преступлений, приведенную на рис. 6.6.2, а. В ячейке B7 введена формула для расчета общего числа преступлений за 2002 г. =СУММ(В3:В6). Скопируем из этой ячейки формулу в соседнюю ячейку C7, где должна быть рассчитана итоговая сумма за 2003 г. При копировании Excel не делает точной копии формулы, вместо этого он полагает, что скопированная формула будет относиться к ячейкам с тем же относительным расположением, т.е. он рассматривает формулу в B7 как инструкцию сложить содержимое четырех ячеек сверху. При копировании в C7 новая формула складывает четыре ячейки над C7, превращаясь в СУММ (C3 : C6). Формула в ячейке D3 для расчета процента роста преступности за I квартал имеет вид =(B3- C3)/C3 (рис. 6.6.2, б). Ее можно скопировать на лежащие ниже ячейки, чтобы получить проценты роста для других кварталов. Для этого активизируем ячейку D3 с копируемой формулой, выполним команду Правка > Копировать, выделим диапазон ячеек D4 : D7, в которые необходимо скопировать формулу и выполним команду

Рис. 6.6.2. Копирование формул: а - исходные данные примера; б - расчет итоговых сумм и процента роста 

Рис. 6.6.2. Копирование формул: а - исходные данные примера; б - расчет итоговых сумм и процента роста

Правка> Вставить (см. раздел 6.2.5 Операции копирования и перемещения). В результате в ячейке D4 появится формула =(B4-C4)/C4, в D5 =(B5-C5)/C5 и т.д. Далее для всех ячеек диапазона D3:D7 установим процентный формат одноименной кнопкой в панели инструментов Форматирование (рис. 6.6.2).
   При копировании формулы в другую ячейку все ссылки на ячейки автоматически меняются так, чтобы они относились к ячейкам с такими же относительными позициями; они называются относительными ссылками.
   Часто требуется, чтобы формула всегда относилась к одной ячейке, независимо от места, куда ее скопируют. Тогда перед каждой частью ссылки на ячейку (т.е. буквой столбца и номером строки) ставят знак $. Например, формула, ссылающаяся на $B$12, всегда будет использовать значение из ячейки B12 независимо от того, куда она скопирована. Это называется абсолютной ссылкой.
   В качестве примера использования в формулах абсолютных ссылок на ячейки рассмотрим обработку результатов голосования (рис. 6.6.3).

Рис. 6.6.3. Абсолютные ссылки 

Рис. 6.6.3. Абсолютные ссылки

   Для подсчета общего числа голосующих введем в ячейке B7 формулу = СУММ(B3:B6), в результате получим 450 человек. Формула в ячейке C3, по которой рассчитывается процент проголосовавших «За», имеет вид = B3/$B$7. Скопировав ее на ячейки C4:C7 и применив процентный формат к диапазону C3:C7, получим в ячейке C4 процент проголосовавших «Против» - формулу = B4/$B$7, в C5 - = B5/$B$7 и т.д., т.е. в рассмотренном примере была использована абсолютная ссылка на ячейку B7.

 
< Пред.   След. >