Технологии электронных таблиц

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

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

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

Для удаления одного листа нужно сделать его активным (щелкнуть мышкой по ярлыку) и выполнить команду Правка->Удалить лист из меню. Для удаления нескольких листов их объединяют в группу (удерживая клавишу Ctrl нажатой, последовательно щелкнуть мышкой на ярлыках удаляемых листов) и выполнить команду Правка->Удалить лист из меню.

Рабочий лист Excel состоит из именованных ячеек. Адрес ячейки определяется названием (номером) столбца и номером строки. Ссылка – способ (формат) указания адреса ячейки.

Рабочий лист таблицы Excel имеет матричную структуру: 256 столбцов c именами А, B, …,Z, AA,…,AZ, …,IV и 65 536 строк с соответствующими номерами.

Например, по умолчанию адрес ячейки отображается в следующем виде: А1, D67, К123, М65, АС3

Кроме того, Excel поддерживает стиль ссылок R1C1 (Row – строка, Column – столбец). Например, адрес ячейки А1 в данном стиле ссылок отображается как R1C1, адрес ячейки А2 – R2C1, В3 – соответственно R3C2.

Для переключения стиля ссылок выполните команду
Сервис->Параметры->Общие

Форматирование ячеек производится при помощи команды
Формат->Ячейки…->Формат ячеек

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

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

Адресация ячеек. Создание формул. Функции в MS Excel

Формулы – это выражения, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=), содержит операторы (знак или символ, задающий тип вычисления в выражении) и операнды (числа, ссылки на ячейки, функции).

Существуют математические, логические операторы, операторы сравнения и ссылок.) и константы. Для создания формулы необходимо щелкнуть по ячейке, в которую требуется ввести формулу, затем ввести знак «=» , формулу и нажать клавишу ENTER.

Формулы могут содержать ссылки на ячейки или имена, например: =A1+23. Ячейка, содержащая формулу, называется зависимой ячейкой, если ее значение зависит от значений в других ячейках. Например, ячейка B2 является зависимой, если она содержит формулу =C2.

Пример формулы	Описание
=C2	Использует значение в ячейке C2
=Лист2!B2	Использует значение в ячейке B2 на Лист2

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

Типы ссылок:

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

Например, если формулу, записанную в ячейке С2, скопировать в ячейку D2, то адреса ячеек А1 и В1 изменятся и формула будет иметь вид: =В1+С1 (изменятся адреса столбцов), при копировании формулы из ячейки С2 в ячейку С3, формула примет вид: =А2+В2 (изменятся адреса строк)

	A	B	C	D
			=A1+B1	=В1+С1
			=A2+B2

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

Примеры абсолютных ссылок:

$D$1 – абсолютный адрес ячейки

D$1 – абсолютный адрес (номер) строки в адресе ячейки

$D1 – абсолютный адрес столбца в адресе ячейки

$D$1:$D$15 – абсолютный адрес диапазона ячеек

При копировании формулы из ячейки С2, в ячейку D2, адрес ячейки А1 изменится на В1, т.к. в формуле ссылка А1 является относительной, адрес ячейки В1 останется неизменным, т. к. формуле ссылка $B$1 является абсолютной, и формула будет иметь вид: =В1+$В$1, а при копировании формулы из ячейки С2 в ячейку С3, формула примет вид: =А2+$В$1

	A	B	C	D
			=A1+$B$1	=B1+$B$1
			=A2+$B$1

Функции – заранее определенные формулы, которые выполняют вычисления по заданным переменным, называемым аргументами, и в указанном порядке.

Функции позволяют выполнять как простые, так и сложные вычисления. Например, функция =ОКРУГЛ(A10;2) округляет число в ячейке A10 с точностью до двух знаков после запятой, функция =СРЗНАЧ(А1:А147) вычисляет среднее значение для диапазона А1:А147.

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

Например,синтаксис функции Если():

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение– это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Функция Если()возвращает одно из двух значений:

– значение ИСТИНА, если заданное условие при вычислении дает верное значение;

– значение ЛОЖЬ, если другое значение.

Аргументы могут быть различных типов: число, текст, логическое значение (ИСТИНА и ЛОЖЬ), значение ошибки (например #Н/Д), ссылки на ячейку, константы.

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

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

Пример: в формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 50.

Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке #ЗНАЧ!.

В формулах можно использовать до семи уровней вложения функций. Когда функция B является аргументом функции А, функция B находится на втором уровне вложенности. Например, функции СРЗНАЧ и СУММ считаются функциями второго уровня, потому что обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня и так далее.

MS Excel имеет дополнительные функции, которые можно применять для анализа данных с использованием условий.

Например, для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, используется функция СУММЕСЛИ. Для получения формулы, возвращающей в зависимости от выполнения условия одно из двух значений, например вознаграждение по указанному объему продаж, используется функция ЕСЛИ().

Функция СУММЕСЛИ() – суммирует ячейки, заданные критерием

Синтаксис функции

СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

Диапазон – диапазон вычисляемых ячеек.

Критерий – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 100 000, "320", ">21 000", "Номер счета" и т.п.

Диапазон_суммирования – фактические ячейки для суммирования.

	А	B
	Стоимость имущества	Комиссионные
	100 000	7 000
	200 000	14 000
	300 000	21 000
	400 000	28 000
	Формула	Описание (результат)
	=СУММЕСЛИ(A2:A5;">180000";B2:B5)	Сумма комиссионных для стоимости имущества свыше 180000 (63 000)

Функция СЧЁТЕСЛИ() – подсчитывает считает количество ячеек, с учетом заданного критерия. Синтаксис функции

=СЧЁТЕСЛИ(диапазон;критерий)

Диапазон – диапазон, в котором нужно подсчитать ячейки.

Критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

Например, критерий может быть выражен следующим образом: "50-1", 5000, ">15%", "Оклад" и т.п.

	A	B
	Данные	Данные
	Оклад	5520
	Премия	20%
	Премия	15%
	Доплата	860
	Формула	Описание (результат)
	=СЧЕТЕСЛИ(A2:A5;"Премия")	Количество ячеек с «Премия» в первом столбце – 2
	=СЧЕТЕСЛИ(B2:B5;">5000")	Количество ячеек со значением выше 5000 – 1

 

Построение графиков и диаграмм. Решение уравнений.

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

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

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

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

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

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

Стандартные составляющие элементы диаграммы:

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

– область построения диаграммы – включает диаграмму вместе с осями и рядами данных;

– ряд данных – совокупность числовых значений одного столбца или одной строки;

– точка ряда – отдельное значение;

– ось значений – для отображения значения точек ряда;

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

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

– основные и вспомогательные линии, линии сетки;

– легенда – для вывода условных графических обозначений рядов.

Для построения диаграммы выполняется команда Вставка->Диаграмма.

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