Теоретический материал. Табличными процессорами называют программы, предназначенные для создания

Табличными процессорами называют программы, предназначенные для создания электронных таблиц и манипулирования их данными. К наиболее известным табличным процессорам относится MS Excel. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты как без «ручных» расчётов, так и без программирования. Наиболее широкое применение электронные таблицы нашли в бухгалтерских и экономических расчётах, но и в научно-технических задачах их можно эффективно использовать. Электронные таблицы рекомендуется использовать для:

· проведения однотипных расчётов над большими наборами данных (например, расчет показателей надёжности электронных схем);

· автоматизация итоговых вычислений;

· решение задач путем подбора параметров (например, расчет редукторов);

· обработка результатов экспериментов;

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

· подготовка табличных документов и др.

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

· автозаполнение ячеек,

· организация связей между таблицами (формирование значений ячеек одной таблицы на основе данных из других таблиц);

· создание сводных (интерактивных) таблиц;

· консолидация данных (объединение данных из нескольких таблиц в одну таблицу);

· использование сценариев (получение в одной таблице нескольких вариантов результатов, найденных по именованным массивам исходных данных);

· автоматизированный поиск ошибок при выполнении вычислений по формулам;

· различные уровни защиты данных;

· представление данных в режиме структуры;

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

Основные понятия и определения

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

Лист состоит из 256 столбцов и 65536 строк. Строки пронумерованы от 1 и до 65536, столбцы названы буквами и комбинациями букв. После 26 букв латинского алфавита следуют комбинации букв от АА, АВ и т.д. Одновременно на экране видна только небольшая прямоугольная часть таблицы. Листы предназначены для создания и хранения таблиц, диаграмм и макросов. По умолчанию листы имеют имена Лист1, Лист2, Лист3. Количество листов в книге можно увеличивать и уменьшать и выполнять их переименование.

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

Адрес ячейки определяет расположение ячейки в таблице. Существует два способа записи адресов ячеек:

1. Указанием буквы столбца и номера строки таблицы, перед которыми может записываться знак $, указывающий на абсолютную адресацию, например С5, J$1, $К$3 и т. д. Этот способ используется в Excel по умолчанию.

2. Указанием номера строки и номера столбца, следующих после букв R и С, соответственно. Номера строк и столбцов могут заключаться в квадратные скобки, которые указывают на относительную адресацию. Например, R5C3 — адресячейки, стоящей на пересечении 5-й строки с 3-м столбцом (равнозначно адресу С5 при первом способе записи), R[1]C4, R3C[7], R[5]C[11 ] и т.д. Такой способ записи адресов можно задать через параметры настройки.

Диапазон – область листа, образуемая двумя и более ячейками. Диапазоны бывают смежные и несмежные. Диапазон смежных ячеек обозначается указанием адресов левой верхней и правой нижней ячеек. Разделителем является двоеточие. Например, A1:B3 задаёт диапазон из шести ячеек: A1, A2, A3, B1, B2, B3.

Формула — это математическая запись вычислений, производимых над данными таблицы. Формула начинается со знака равенства и записывается в ячейку таблицы. Результатом выполнения формулы является вычисленное значение. Это значение автоматически записывается в ячейку, в которой находится формула. Пример записи формулы: = А$1+100+ВЗ.

Ссылка — это запись адреса ячейки в составе формулы. Например, в формуле =(А5+$С$3) содержатся две ссылки: А5 и $С$3. Ссылки могут быть абсолютные, относительные и смешанные.

Функция — это математическая запись, указывающая на выполнение определенных вычислительных операций. Функция состоит из имени и одного или нескольких аргументов, заключенных в круглые скобки, например, =SUMM(A1:A4).

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

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

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

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

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

Стандартное окно программы

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

Элементы окна, характерные только для Excel

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

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

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

NUM — включен цифровой блок клавиатуры (клавишей Num Lock);

CAPS — включен верхний регистр клавиатуры (клавишей Caps Lock);

ВДЛ — включен режим выделения (клавишей F8);

ДОБ — включен режим выделения несмежных диапазоновячеек (клавишамиShift+F8);

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

В этой же части высвечивается имя функции, выбранной пользователем из контекстного меню строки состояния, и результат ее вычисления по отношению к текущему диапазону ячеек. Например, СУМ-10, МИНИМАЛЬНОЕ-1, КОЛИЧЕСТВО ЗНАЧЕНИЙ=91 и др. (всего шесть функций).

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

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

Рабочая книга

Создание рабочей книги

При запуске MS Excelна экране появляется окно с пустой рабочей книгой, озаглавленной Книга 1. Новую книгу можно создать с помощью кнопки на панели инструментов или командой Файл®Создать. В последнем случае предоставляются на выбор несколько шаблонов: Книга, Авансовый отчёт, Заказ, Счёт. MS Excel позволяет работать одновременно с несколькими книгами. Переход от одной книги к другой выполняется через меню Окно.

Структура рабочей книги

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

Выделение элементов рабочей книги

Выделенная (активная) ячейка на экране обведена жирной рамкой. Одновременно жирным шрифтом выделяются номер строки и имя столбца.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Внутри выделенного диапазона указатель мыши можно перемещать нажатием клавиш Enter (вниз), Shift+Enter (вверх), Таb (вправо), Shift+Tab (влево). Нажатие любой другой клавиши управления курсором или щелчок мышью отменяет выделение диапазона.

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

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

Работа с ячейкой электронной таблицы

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

Число может быть целым или дробным. Целая и дробная часть отделяется запятой. Например: 5; 5,6.

Дата записывается в следующих форматах:

день, месяц, год - 2.12.97 (разделяются точкой).

Месяц, год - 12.97 в ячейке будет отображено Дек.97.

Время записывается - 13:45 - (часы и минуты разделяются двоеточием).

Ввод данных:

· установить курсор в нужную ячейку и выделить ее щелчком мыши (выделенная ячейка окажется в рамке, и ее адрес появится в поле имени);

· ввести данные в ячейку;

· зафиксировать данные в ячейке одним из приведенным ниже способом:

- щелкнуть по кнопке Enter;

- нажать зеленую галочку в строке формул;

- щелкнуть мышкой по другой ячейке;

- покинуть ячейку с помощью клавиш управления курсором.

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

Очистить содержимое вводимой ячейки:

1 способ:

щелкнуть по клавише Esc.

2 способ:

щёлкнуть красный крестик в строке формул.

Редактирование содержимого ячейки:

· выделить ячейку (содержимое появится в Строке формул);

· поместить курсор в нужное место в Строку формул;

· редактировать как обычный текст (кл. Backspace и Del для удаления символов);

· завершить редактирование клавишей Enter (или щелкнув мышью другую ячейку либо зелёную галочку в строке формул).

Примечание: для отмены выполненных действий используется команда Правка®Отменить или кнопка на панели инструментов. Следует помнить, что количество отменяемых действий в MS Excelограничено.

Форматы данных

Данные, введённые в ячейку, могут быть представлены в определённом формате. Действия выполняются в окне Формат ячейки, которое выводится на экран командой Формат®Ячейки®Число или через контекстное меню.

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

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

Для задания процентного формата достаточно ввести после числа символ процентов. Например, записать 30%. Чтобы не записывать вручную символ %, следует задать для ячеек процентный формат, а потом ввести число. Если сначала записать число, а потом задать процентный формат, то в ячейке отобразится значение 3000%.

Действия с ячейками и диапазонами

Копирование интервалов

1 способ:

· выделить интервал ячеек;

· выбрать меню Правка®Копировать или нажать на кнопку Копировать на панели Стандартная;

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

· нажать Enter или выбрать меню Правка®Вставить

2 способ:

· выделить интервал ячеек;

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

· нажать клавишу Ctrl (при этом рядом со стрелкой появится символ "+" );

· не отпуская клавиши Ctrl, нажать левую клавишу мыши и перетащить объект в нужное место.

Перенос интервалов:

1 способ:

· выделить интервал ячеек;

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

· нажать левую клавишу мыши и перетащить интервал в нужное место.

2 способ:

· выделить интервал ячеек;

· выбрать меню Правка®Вырезать (граница интервала превращается в прерывистую рамку);

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

· нажать клавишу Enter, или выбрать меню Правка®Вставить, или нажать кнопку Вставить на панели Стандартная.

Очистка ячеек выполняется командой Правка®Очистить®Содержимое или клавишей Delete. При этом сами ячейки остаются на листе.

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

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

Форматирование ячеек

Для каждой ячейки можно установить:

· шрифт, в котором выдается значение ячейки (раскрыть окно с набором шрифтов и выбрать нужный);

· размер шрифта (развернуть список размеров шрифтов и выбрать нужный);

· способ форматирования содержимого ячейки (по центру, левой или правой границе);

· задание формата вывода числа (Формат®Ячейки®Число);

· обрамление ячейки (Формат®Ячейки®Рамка);

· задание цвета ячейки (Формат®Ячейки®Вид);

· высота ячейки (Формат®Строка);

· ширина ячейки (Формат®Столбец).

Работа с текстом

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

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

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

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

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

Для внесения в таблицы больших текстов следует использовать текстовые поля, количество символов в которых не ограничено. Вставка текстовых полей выполняется с помощью кнопки Надпись панели инструментов Рисование. Если при вставке текстового поля удерживать нажатой клавишу Alt, то текстовое поле будет привязано к линиям сетки. Чтобы придать текстовому полю квадратную форму, следует удерживать клавишу Shiftпри его формировании. Для задания табуляции в текстовом поле используется сочетание клавиш Ctrl+Tab.

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

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

Автозаполнение и автозамена

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

Заполнение ячеек арифметической прогрессией часто применяется для нумерации элементов. Для автозаполнения арифметической прогрессией надо:

· ввести в ячейку значение первого члена арифметической прогрессии;

· ввести в соседнюю ячейку второй член арифметической прогрессии;

· выделить обе ячейки;

· протащить маркер заполнения по ячейкам строки или столбца.

В процессе автозаполнения появляются всплывающие подсказки со значениями, которые появятся после завершения операции.

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

В MS Excelпредусмотрено автозаполнение комбинациями слов и чисел, например: Вопрос 1, Вопрос 2, Вопрос 3 и т.д. Первая комбинация вносится в ячейку, а остальные получаются методом протягивания маркера заполнения.

MS Excelпозволяет вносить в таблицы списки. Причём, кроме заготовленных списков общего применения разрешено создавать пользовательские списки. Команда Сервис®Параметры открывает окно Параметры, на вкладке Списки которого можно посмотреть существующие списки (например, Пн, Вт и т.д.; Январь, Февраль и т.д.) и создать свой. Для создания списка надо в поле Списки выбрать Новый список, затем в поле Элементы списка сформировать свой список, используя клавишу Enter для разделения элементов. После щелчка по кнопке Добавить созданный список появится в перечне доступных для использования списков. Если в таблице уже есть список, то его можно использовать как образец для формирования пользовательского списка: выделить диапазон ячеек, содержащий список, и в окне Параметры на вкладке Списки щёлкнуть кнопку Импорт.

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

Ввод формул

Запись формул начинается всегда со знака равенства (=), который набирают на клавиатуре. Формулы могут включать ссылки на ячейки (A1;B1), имена функций (Sin, Сумм), разделенные знаками арифметических и логических операций. Пробелы в формуле не допускаются. Например: =А1+В1.

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

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

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

^ - возведение в степень;

+ - сложение;

* - умножение;

/ - деление;

- - вычитание.

Операциисравненияпозволяютсравнить два значения:

= - равно;

< - меньше;

> - больше;

<= - меньше или равно;

>= больше или равно;

<>- не равно.

Результатом операции является логическое значение: ИСТИНА или ЛОЖЬ.

Операция сцепления (конкатенации) относится к текстовым. Используется для объединения нескольких текстовых строк в одну строку. Обозначается символом & (амперсанд). Например, результатом вычисления по формуле ="Петров"&"а" будет значение Петрова.

Для задания ссылок на диапазоны ячеек введены следующие операции:

: (двоеточие) используется для ссылки на все ячейки между крайними ячейками диапазона, включая эти ячейки. Например, B3:D10;

; (точка с запятой) используется для ссылки на несмежные ячейки. Например, B3;D10;F18;

, (запятая) используется для объединения нескольких ссылок в одну. Например, СУММ(B5:B15,D5:D15).

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

Ввод формулы:

· выделить ячейку для размещения в ней формулы;

· ввести знак =;

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

· ввести оператор;

· выделить другую ячейку (или интервал ячеек);

· закончить ввод Enter.

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

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

Копирование формул:

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

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

· нажать левую клавишу мыши и, не отпуская, протянуть на нужное количество ячеек;

· отпустить левую клавишу мыши.

Относительные и абсолютные ссылки

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

Относительные ссылки автоматически корректируются при их копировании, а абсолютные ссылки — нет.

Часто при создании формулы ссылки учитывают расположение относительно ячейки, содержащей формулу. Если ячейка B6 содержит формулу =A5, то искомое значение находится на одну ячейку выше и левее ячейки B6. Такая ссылка называется относительной. При копировании формулы с относительными ссылками Excel автоматически изменяет ссылки во вставляемой формуле для указания на другие ячейки в соответствии с положением формулы. Если формулу из ячейки B6 скопировать в ячейку B7, то Excel изменит формулу в ячейке B7 на =A6, ссылаясь на ячейку, которая расположена на одну строку выше и на один столбец левее ячейки B7.

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, то используют абсолютные ссылки. Например, если имеется формула =A5*C1, то при копировании в другую ячейку изменятся обе ссылки. Для создания абсолютной ссылки на ячейку C1, надо поставить знак доллара ($) перед той частью ссылки, которая не должна изменяться =A5*$C$1

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

Функции

Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Например, функция СУММ суммирует значения в диапазоне ячеек

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

Стандартные функции можно записывать вручную, либо вводить с помощью мастера функций, который запускается щелчком по кнопке Вставка®Функция. В окне Мастера функций в списке Категория выбирается категория, к которой относится функция, а в списке Функция — конкретная функция данной категории. Если трудно определить категорию, то используют Полный алфавитный перечень функций. После щелчка на кнопке ОК под строкой формул появляется Палитра формул, обладающая свойствами диалогового окна. В процессе ввода параметров функции Палитра формул изменяет вид. На ней отображаются поля, предназначенные для ввода параметров. Если название параметра указано полужирным шрифтом, параметр является обязательным и соответствующее поле должно быть заполнено. Параметры, названия которых приводятся обычным шрифтом, можно опускать. В нижней части палитры приводится краткое описание функции, а также назначение изменяемого параметра (рис. 38).

Рисунок 35

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

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