 |
|
ТП Ms Excel. Принципы работы , возможности . Макросы . Формулы.
ПРИНЦИПЫ РАБОТЫ В MICROSOFT EXCEL
1.1. Принцип организации документа. Документ MS Excel называется рабочей книгой (workbook) и состоит из листов. Листы бывают нескольких типов.
Рабочий лист (worksheet, spreadsheet), называемый также электронной таблицей или просто таблицей, – служит для хранения и бработки данных. На каждом из рабочих листов данные можно вводить, редактировать, производить с ними вычисления.
Лист диаграммы (chart sheet) служит для графического отображения данных. С помощью MS Excel можно строить диаграммы различных типов: линейчатая, столбцовая, круговая, кольцевая, лепестковая, график, поверхность и другие виды листов предназначены для автоматизации задач. Они ранят макрокоманды (макросы) – небольшие программы, состоящие из последовательностей команд Excel, а также описания диалоговых окон для организации взаимодействия c пользователем при выполнении макросов. Такие листы использовались в предыдущих версиях MS Excel. Версия Excel 2003 интегрирует в себе более мощное и удобное средство автоматизации – систему программирования на языке Visual Basic for Applications (VBA).
В каждый момент времени активным является только один из листов рабочей книги: только он доступен для работы. Рабочий лист MS Excel 2003 представляет собой одну таблицу из 256 столбцов и 65536 строк. Обычно строки нумеруются, а столбцы обозначаются латинскими буквами. Первый столбец обозначается буквой А, следующие – буквами B, C, D, и т. д. После столбца Z следует столбец AA, потом идет AB, AC, … AZ, BA, BB и так далее до IT, IU, IV. Пересечение строки и столбца образует ячейку (cell) – минимальный структурный элемент электронной таблицы. Ячейка имеет свой уникальный идентификатор, состоящий из идентификатора столбца и идентификатора строки. Например, B3, D1503, FC28.
Строка меню – основное средство управления приложением. Через меню пользователь может вызвать на выполнение любую доступную команду MS Excel. Информацию любой ячейки можно условно разделить на содержимое и формат. Содержимое ячейки – это данные, а формат – форма, в которой данные отображаются на экране или с помощью печатающего устройства. Ячейка может содержать число, дату и время, текстовую константу или формулу. Ячейка также может быть пустой, т. е. не содержать никаких данных. С помощью клавиатуры можно изменять содержимое любой из ячеек: вводить новые данные или редактировать их. Формулы – основное средство анализа данных в MS Excel, как и в других табличных процессорах (электронных таблицах Lotus 1-2-3, QuattroPro, SuperCalc). В отличие от константы, формула представляет собой выражение, которое MS Excel вычисляет автоматически. При вычислении могут использоваться данные других ячеек (для этого достаточно указывать их идентификаторы).
Важно запомнить, что ячейка не может одновременно хранить несколько единиц информации (например, несколько чисел, текстовую константу и число, несколько формул). MS Excel 2003 содержит средства форматирования, достаточные для создания на основе электронной таблицы хорошо оформленного документа. Такой документ может служить итоговым отчетом о проделанной работе.
Вид экрана. Рабочее окно MS Excel, также как и окна большинства других приложений MS Windows, включает строку заголовка, строку меню и рабочую область (см. рис. 1.2). Для удобства пользователя добавлены такие элементы как панели инструментов, строка состояния, строка формул.
В строке заголовка указано имя приложения (Microsoft Excel) и
Через тире – имя документа, с которым в настоящий момент работает
Пользователь
Строка меню – основное средство управления приложением. Через меню пользователь может вызвать на выполнение любую доступную команду MS Excel.
Панели инструментов обеспечивают быстрый доступ к основным командам. Каждой команде соответствует своя кнопка (иконка) на панели инструментов. Если переместить указатель мыши на иконку, через некоторое время около нее появится подсказка с названием команды. В MS Excel 2003 панели инструментов могут содержать не только кнопки, но и меню.
Если какой-либо команде MS Excel присвоена комбинация клавиш, то эта комбинация отображается в меню справа от команды. Если команде соответствует кнопка на панели инструментов, то изображение кнопки расположено слева. Например, в меню "Правка" находится команда копирования в буфер обмена: .
Запретить появление подсказок можно, сбросив флажок
"Всплывающие подсказки" на вкладке "Вид" команды "Параметры" из меню "Сервис"
Панели инструментов можно настраивать: добавлять и удалять кнопки, создавать новые панели инструментов, а также отображать, скрывать и перемещать по экрану существующие панели инструментов. Для настройки воспользуйтесь одноименной командой, которая вызывается через меню "Сервис" или контекстное (всплывающее) меню области панелей.
Удерживая клавишу 〈Alt〉, кнопки панелей инструментов можно
Перетаскивать с помощью мыши.
Существует три способа выбора тех панелей, которые MS Excel отображает на экране.
1) В меню "Вид" выберите "Панели инструментов". Появится подменю, состоящее из списка доступных панелей. Включенные (отображаемые на экране) панели инструментов помечены маркерами. Щелкнув мышью по названию панели, ее можно включить/выключить.
2) То же самое подменю можно вызвать, щелкнув правой кнопкой мыши по любой из панелей инструментов на экране.
3) В диалоговом окне команды "Настройка" (меню "Сервис") на вкладке "Панели инструментов" пометьте маркерами нужные панели.
Наиболее часто используемые команды MS Excel находятся на панелях инструментов "Стандартная" и "Форматирование".
Строка состояния находится в нижней части экрана, на ней ото-
Бражаются сведения о выполняемой в настоящий момент операции.
Рабочая область отображает содержимое активного листа рабочей книги. Вид листа зависит от масштаба отображения: чем больше масштаб, тем крупнее и детальнее отображается содержимое листа, но тем меньшая его часть умещается на рабочей области. Масштаб задается в процентах. Его можно изменить, вызвав команду "Масштаб" в меню "Вид", или воспользовавшись соответствующим элементом на панели инструментов "Стандартная".
К рабочей области примыкают следующие элементы управления: полосы прокрутки, заголовки строк и столбцов, ярлыки листов.
Заголовки строк и столбцов помогают ориентироваться на рабочем листе: они показывают, какая часть таблицы видна на рабочей области, и позволяют идентифицировать любую ячейку.
Ярлыки листов предназначены для "перелистывания" рабочей книги. Достаточно щелкнуть по ярлыку мышью, чтобы перейти на соответствующий лист. На ярлыках написаны названия листов, по умолчанию – "Лист1", "Лист2", и т. д. Для того, чтобы изменить название листа, следует сделать двойной щелчок по его ярлыку или вызвать команду "Переименовать" в меню "Формат" / подменю "Лист".
Строка формул расположена непосредственно над рабочей областью. Правая часть строки служит для отображения и правки содержимого активной ячейки. Чтобы начать правку в строке формул, достаточно щелкнуть по ней мышью. В левой части строки формул, так называемом поле имени, выведен идентификатор активной ячейки или размер выделенного диапазона ячеек. За отображение / скрытие строки формул отвечает одноименная команда в меню "Вид".
1.3. Ввод и изменение данных. На каждом рабочем листе одна из ячеек является активной: при вводе информации с клавиатуры именно эта ячейка подвергается изменению. Таким образом, прежде чем изменять содержимое ячейки, необходимо сделать ее активной.
На экране активную ячейку можно узнать по толстой рамке. В правом нижнем углу рамки находится маркер ячейки – небольшой черный прямоугольник, с помощью которого вызывается команда автоматического заполнения (см. пункт 1.4). На рис. 1.1 активной является ячейка B3, а на рис. 1.2 – ячейка C2. Для того, чтобы сделать ячейку активной, достаточно щелкнуть по ней мышью, когда курсор мыши имеет вид. Активную ячейку можно выбрать и с помощью клавиатуры. Для перемещения по документу указателя ячейки используются следующие клавиши:
〈Клавиша со стрелкой〉 На одну ячейку в направлении стрелки
〈Ctrl〉+〈Стрелка〉 К соответствующему краю текущей области дан-
Ных (области, содержащей данные и ограничен-
Ной пустыми ячейками)
〈Home〉 В начало строки
〈Ctrl〉+〈Home〉 В начало листа
〈Ctrl〉+〈End〉 В нижний правый угол области данных
〈Page Down〉 На один экран вниз.
〈Page Up〉 На один экран вверх
〈Alt〉+〈Page Down〉 На один экран вправо
〈Alt〉+〈Page Up〉 На один экран влево
〈Ctrl〉+〈Page Down〉 На следующий лист рабочей книги
〈Ctrl〉+〈Page Up〉 На предыдущий лист рабочей книги
1.4. Форматирование. Для изменения формата ячейки или группы ячеек достаточно сделать ячейку активной, или выделить группу ячеек; и в меню "Формат" вызвать команду "Ячейки" (о выделении группы ячеек см. пункт 1.5). Появится диалоговое окно, на вкладках которого можно изменять различные параметры отображения ячейки. На вкладке "Число" изменяется способ представления содержимого ячейки – числа, даты, времени, и т. д. Например, число 367,1285 можно представить в виде 367,1285000; 367,1 (т. е. округлить); 3,671285E+2 (т. е. 3,671285⋅102). Изменение числового формата (например, округление) не влияет на содержимое ячейки, используемое при вычислениях.
Некоторые числовые форматы могут быть присвоены с помощью кнопок панели инструментов "Форматирование".
Команды правки. В меню "Правка" MS Excel находятся команды для выполнения операций с группами ячеек: очистка, удаление, автозаполнение, работа с буфером обмена. Прежде, чем вызвать команду правки, необходимо выделить в таблице группу ячеек, с которыми будет выполняться операция. Выделенная группа ячеек всегда состоит из одной или нескольких прямоугольных областей. Выделенную область легко узнать на экране: цвета отображения ячеек инвертируются. Прямоугольную область ячеек можно выделить двумя способами: с помощью мыши или клавиатуры.
Документы, окна и листы. MS Excel поддерживает многооконный интерфейс, как и другие современные приложения Windows 95. Поэтому программе MS Excel наряду с программами MS Word, MS Access, Mathcad и др. присущи следующие характерные черты. Одновременно могут быть открыты несколько документов. Каждый документ отображается в отдельном окне внутри основного окна программы. Окно документа, с которым работает пользователь, является активным: на экране это окно располагается поверх остальных и выделяется другим цветом заголовка. Для перехода между документами служит меню "Окно", в котором находится список открытых документов. Обычно каждое окно документа развернуто на все окно программы, но такое положение легко отменить, нажав на среднюю из трех кнопок управления окном документа. Эти кнопки при развернутом окне находятся под кнопками управления окном самой программы. Для создания новой рабочей книги следует щелкнуть по кнопке "Создать" . Если воспользоваться одноименной командой в меню "Файл", то появится диалоговое окно для выбора одного из имеющихся в наличии шаблонов, т. е. типов документов.
Если в программе открыт документ, сохраненный на диске, то Системе запрещено совершать с его файлом какие-либо операции (удалять, переименовывать, и т. д.). Для снятия запрета необходимо или закрыть этот документ (меню "Файл"/"Закрыть", кнопка ), или закрыть саму программу.
Отличия при работе с документами в MS Excel от других программ связаны с тем, что документ состоит из листов. Команды "Печать" и "Параметры страницы", которые обычно выполняются для всего документа, в MS Excel выполняются для активного или выделенных листов. Ярлык выделенного листа отличается светлым фоном. Активный лист также считается выделенным. Ярлык активного листа отличается еще и тем, что название листа написано полужирным шрифтом.
Для выделения (или отмены выделения) листа следует, удерживая клавишу 〈Ctrl〉, щелкнуть мышью по его ярлыку. Если Вы удерживаете 〈Shift〉, то выделенными становятся все листы, ярлыки которых расположены по порядку начиная от активного листа и заканчивая тем, по ярлыку которого Вы щелкнули.
Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса. Макрос — это серия команд и функций, хранящихся в компьютере. Их можно выполнять всякий раз, когда необходимо выполнить определенную задачу.
Перед тем как записать макрос, необходимо спланировать шаги и команды, которые он будет выполнять. Если при записи макроса была допущена ошибка, ее исправление будет также записано. Каждый раз при записи макроса, он сохраняется в новом модуле, присоединенном к книге таблицы Excel.
Допустим, что необходимо каждый месяц создавать отчет для главного бухгалтера. Требуется выделить красным цветом и полужирным шрифтом имена клиентов с просроченными счетами. Для этого можно создать и выполнить макрос, который автоматически выполнит такое форматирование к выделенным ячейкам.
Формулы в Excel — это его основная суть, то, ради чего и была создана эта программа компанией Microsoft. Формулы позволяют произвести расчеты значений ячеек на основе данных других ячеек, причем если исходные данные поменяются, то результат вычислений в ячейке, где стоит формула пересчитается автоматически!
Создание формул в Excel
Рассмотрим работу формул на самом простом примере — сумме двух чисел. Пусть в одной ячейке Excel введено число 2, а в другой 3. Нужно, чтобы в третье ячейке появилась сумма этих чисел.
Задача суммирования
Суммой 2 и 3 является, конечно же, 5, но вносить пятерку вручную в следующую ячейку не надо, иначе теряется смысл расчетов в Excel. В ячейку с итогом необходимо ввести формулу суммы и тогда результат будет вычислен программой автоматически.
Вывод результата
В примере вычисление выглядит простым, но когда числа большие или дробные без формулы просто не обойтись.
Фомулы в Excel могут содержать арифметические операции ( сложение +, вычитание -, умножение *, деление /), координаты ячеек исходных данных (как по отдельности, так и диапазон) и функции вычисления.
Рассмотрим формулу для суммы чисел в примере выше:
=СУММ(A2;B2)
Каждая формула начинается со знака «равно». Если Вы хотите добавить в ячейку формулу, написав ее вручную, то именно этот знак следует написать первым.
Далее в примере идет функция СУММ, которая означает что необходимо произвести суммирование некоторых данных, а уже в скобках у функции, разделенные точкой с запятой, указываются некоторые аргументы, в данном случае координаты ячеек (A2 и B2), значения которых необходимо сложить и поместить результат в ту ячейку, где написана формула. Если бы Вам требовалось сложить три ячейки, то можно было бы написать три аргумента у функции СУММ, разделяя их точкой с запятой, например:
=СУММ(А4;B4;C4)
Когда требуется сложить большое количество ячеек, то указывать каждую из них в формуле займет очень много времени, поэтому вместо простого перечисления можно использовать указание диапазона ячеек:
=СУММ(B2:B7)
Диапазон ячеек в Экселе указывается с помощью координат первой и последней ячеек, разделенных знаком «двоеточие». В данном примере производится сложение значений ячеек, начиная с ячейки B2 до ячейки B7.
Сумма диапазона ячеек
Функции в формулах можно соединять и комбинировать как Вам необходимо для получение требуемого результата. Например, стоит задача сложить три числа и в зависимости от того, меньше ли результат числа 100 или больше, домножить сумму на коэффициент 1.2 или 1.3. Решить задачу поможет следующая формула:
=ЕСЛИ(СУММ(А2:С2)<100;СУММ(А2:С2)*1,2;СУММ(А2:С2)*1,3)
Вычисление с условием
Разберем решение задачи подробнее. Использовалось две функции ЕСЛИ и СУММ. Функция ЕСЛИ всегда имеет три аргумента: первый — условие, второй — действие в случае, если условие верно, третий — действие в случае, если условие неверно. Напоминаем, что аргументы разделяются знаком «точка с запятой».
=ЕСЛИ(условие; верно; неверно)
В качестве условия указано, что сумма диапазона ячеек A2:C2 меньше 100. Если при расчете, условие выполнится и сумма ячеек диапазона будет равна, например, 98, то Эксель выполнить действие указанное во втором аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,2. В случае же, если сумма превысит число 100, то выполнится уже действие в третьем аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,3.
Встроенные функции в Excel
Функций в Excel огромное количество и знать все просто невозможно. Некоторые часто используемые запомнить можно, а некоторые Вам понадобятся лишь изредка и помнить их название и тем более форму записи очень сложно.
Но в Экселе имеется стандартный способ вставки функций с их полным списком. Если Вы хотите добавить какую-то функцию в ячейку, то кликните по ячейке и выберите в главном меню вставку функции. Программа отобразит список функций и Вы сможете выбрать ту, которая необходима для решения задачи.
Чтобы вставить функцию в Excel 2007 выберите в главном меню пункт «Формулы» и кликните на значок «Вставить функцию», либо нажмите на клавиатуре комбинацию клавиш Shift+F3.
Вставка функции в Excel 2007
В Excel 2003 функция вставляется через меню «Вставка»->«Функция». Так же работает и комбинация клавиш Shift+F3.
Вставка функции в Excel 2003
В ячейке на которой стоял курсор появится знак равенства, а поверх листа отобразится окно «Мастер функций».
Выбор функции
Функция в Excel разделены по категориям. Если Вы знаете к какой категории может относится предполагаемая Вами функция, то выбирайте отбор по ней. В противном случае выберите «Полный алфавитный перечень». Программа отобразит все имеющиеся функции в списке функций.
Пролистывайте список и выделяйте мышью наименование, заинтересовавшей Вас функции. Чуть ниже списка появится ее форма записи, требуемые аргументы и краткое описание, которое разъяснит Вам предназначение функции. Когда найдете то, что нужно, кликните по кнопке «OK» для перехода к указанию аргументов.
Описание функции
В окне аргументов имеются поля с названиями «Число 1», «Число 2» и т.д. Их необходимо заполнить координатами ячеек (либо диапазонами) в которых требуется взять данные. Заполнять можно вручную, но гораздо удобнее нажать в конце поля на значок таблицы для того, чтобы указать исходную ячейку или диапазон.
Окно аргументов функции
Окно аргументов примет упрощенный вид. Теперь необходимо кликнуть мышью на первую исходную ячейку с данными, а затем снова на значок таблица в окне аргументов.
Указание исходной ячейки
Поле «Число 1» заполнится координатами выбранной ячейки. Ту же самую процедуру следует проделать для поля «Число 2» и для следующих полей, если число аргументов функции у вас более двух.
Заполнение всех аргументов
Заполнив все аргументы, Вы уже можете предварительно посмотреть результат расчета полученной формулы. Чтобы он появился в ячейке на листе, нажмите кнопку «OK». В рассмотренном примере в ячейку D2 помещено произведение чисел в ячейках B2 и C2.
Результат произведение чисел
Рассмотренный способ вставки функции является универсальным и позволяет добавлять любую функцию из общего списка стандартных функций Excel.