Табличный процессор EXCEL. Назначение и функциональные возможности., Информатика

Содержание

Введение2

1. Назначение2

2. Область применения электронных табличных процессоров

3. Общий вид рабочего окна MS Excel

4. Основные понятия электронных таблиц MS Excel

5. Адресация Excel8

6. Работа с формулами

7. Использование «Мастера диаграмм»

8. Набор основных функций Excel15

9. Решение уравнений и задач оптимизации23

Список использованной литературы.25

ПРИЛОЖЕНИЕ. Список функций Excel26

Выдержка из текста работы

Microsoft Excel входит в состав пакета MS Office и является одним из самых популярных сегодня табличных процессоров — программ для работы с электронными таблицами.

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

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

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

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

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

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

Назначение табличных процессоров

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

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

Самые известные табличные процессоры — Microsoft Excel, Lotus 1-2-3, Corel, Works. Умение работать с электронными таблицами — решительное преимущество при устройстве на работу в частную компанию.

Информационная структура таблицы описывается строками с номерами 1, 2, 3,… и столбцами с буквами А, В, С,… (рис. 15.1). Поэтому ячейки (или клетки) таблицы обозначаются координатами А2, СЗ, D4, как в шахматах или игре «Морской бой».

В ячейки можно ввести данные, задать формат их представления (проценты, руб.), установить формулы вычисления зависимых значений. Например, в ячейки столбцов «Цена за штуку», «Количество, шт.» вводят данные, а в ячейки столбца «Общая стоимость» — формулы умножения цены за штуку на количество штук.

Ячейки с формулами показывают значения результата вычисления, причем если данные в ячейках столбцов В и С изменить, значения ячеек столбца D будут пересчитаны. Это позволяет изучать сценарии «что, если». Для табличных расчетов характерны относительно простые формулы и большие объемы исходных данных.

Версии табличного процессора Microsoft Excel 97/2000/ХР/2003 обмениваться файлами, так как создают их совместимыми по формату. В состав программы Excel входит система управления базами данных (СУБД), текстовый редактор, модуль деловой графики для построения наглядных диаграмм по табличным данным. Название Excel — изящная игра слов: Excellent cells — превосходные ячейки. А еще англ. excel [ik’sel] — превосходить, поэтому можно рекомендовать ударение эксель.

Форматирование шрифта, выравнивание, проверка орфографии, вставка объектов в редакторе Excel аналогичны приемам текстового редактора Word. Другие операции присущи только Excel и связаны с его математическими возможностями.

Формулы

Microsoft Excel обрабатывает данные при помощи формул и встроенных стандартных функций.

Формула — уравнение или выражение. В ячейке электронной таблицы формула — введенное выражение, всегда начинающееся со знаки равенства = и содержащее далее константы (числа), операторы, функции и ссылки — адреса ячеек. Введенные в формулу адреса ячеек определяют, как значение ячейки зависит отданных в других ячейках текущего листа, листов той же книги или других книг. Например, в ячейке С5 формула =A4*D7 будет умножать данные из ячейки А4 на данные из ячейки D7. В ячейке листа формула видна только в момент ввода или редактирования, а после ввода ячейка выводит результат исчислений. Когда табличный курсор выделяет ячейку с формулой, формулу видно в строке формул.

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

Константа представляет собой готовое (не вычисляемое, постоянное) значение, которое введено в ячейку: текст, целые и дробные десятичные числа (в том числе процентные и денежные форматы), даты, время. Например, число 210, дата 09.04.2004 и текст «Осужден по статье» являются константами, а выражение =(35+590)*23 и =0,13*CУMM(C9:D29) являются формулами, в которые входят числовые константы 35, 590, 23 и 0,13. Сами формулы не являются константами.

Операторы определяют действия в формуле (сложение, умножение, деление, сравнение, а также объединение). Арифметические операторы +, —, *, / вводят знаки плюс, минус, умножить, разделить, например: =(B4+25*C4)/(D5—F5). Оператор процентов % записывается вплотную к числу или адресу ячейки и соответствует делению на 100. формула =(1+5%) вычислит значение 105%, а формула =С5*(1+5%) о эквивалентна =С5*1,05 и вычисляет 105% от числа в ячейке С5. Оператор возведения в степень (крышка) находится на клавише 6англ.

Импример, формула =3Л4 означает З4 и возвратит значение 81.

Внимание! Термин «возвращать» в отношении формул и функций табличный обработки данных означает «выводить в качестве значения ячейки». Набираются и вводятся данные, формулы, функции, и послу исчисления возвращается результат — число, текст.

Формула =5+2*3 возвращает число 11, но формула =(5+2)*3, с теми же числами, сначала вычислит значение 7 в скобках, потом умножит на 3 и возвратит число 21.

Операторы сравнения: = (равно), < (меньше), > (больше), <= (меньше или равно), >= (больше или равно), <> (не равно) — применяются в качестве условий и критериев логических и статистических функций.

Операторы ссылки: двоеточие между адресами первой и последнем ячейки прямоугольного диапазона (C9:D29) и точка с запятой, которая отделяет несколько параметров, например CP3HA4(B9:B29;D9:D29;200)

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

Ячейка, содержащая формулу со ссылкой на адрес, называется зависимой ячейкой, поскольку ее значение зависит от значения другой ячейки, на которую она ссылается. Независимая ячейка — ячейка с числом или ячейка с формулой из числовых констант — не содержит адресов-ссылок на другие ячейки.

Например, ячейка с формулой =2*2 независимая, а ячейка с формулой =В9*К15 зависимая и пересчитает значение заново, если изменить данные в ячейке К15, на которую формула ссылается. Пересчет значений в зависимых ячейках при изменении

Функции в Microsoft Excel

Понятие функции, категории функций, применение Мастера функций

Слово «функция» (от лат. functio — исполнение) в обычном понимании означает деятельность, обязанность, работу, роль. В математике функция — переменная величина, зависимая от изменения независимой переменной — аргумента. Функция может быть представлена соответствием у —f(x), а также формулой, таблицей, графиком, диаграммой

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

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

Имя функции можно выбрать по списку Мастера функций (см ниже).

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

В Excel ХР/2003 при вводе функции появляется всплывающая подсказка с синтаксисом ее структуры.

Для выбора функции из списка и определения ее параметров привводе удобно пользоваться Мастером функций. Окно Мастера функций открывается командой Вставка, Функция или нажатием клавиш Shift+F3. Кроме того, окно выбора и ввода функций в Excel 2000 открывает кнопка fx, а в Excel ХР/2003 распахивает примыкающая кнопка ? Другие функции (правее кнопки ?).

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

Вторая панель Мастера функций появляется после того, как имя функции выбрано. Когда имя просто введено с клавиатуры или надо исправить ранее введенную функцию, то надо в строке формул нажми, в Excel 2000 кнопку = Изменить формулу, а в Excel ХР/2003 кнопку fx Вставка функции. Появляющаяся панель формул наглядно отображает функцию, строки для ввода ее аргументов, покакает возвращаемое функцией значение.

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

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

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

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

Наиболее часто применяемые статистические функции:

=СУММ — суммирует числа в диапазоне ячеек таблицы (позволяет не вводить формулу сложения значений в адресах многих ячеек).

=МАКС — возвращает наибольшее значение из набора значении,

=МИН — возвращает наименьшее значение из набора значений.

=СРЗНАЧ — возвращает среднее значение набора значений.

Варианты применения функции СУММ показаны на примерах: =СУММ(С5;Н12;К8), =CyMM(G5:G7;K8:L15;280),

=CyMM(G5.G7), =CyMM(G5:G7).

Требуется соблюдать синтаксис функции: вводить сначала знак равенства, затем имя функции (русскими буквами), в скобках — аргумент функции (число, имена ячеек, диапазон ячеек). При наличии у функции нескольких аргументов их требуется располагать в скобках в правильном порядке, отделяя точкой с запятой. Например, функция СУММ(В5:С12;Е6:Е11;500) складывает значения двух диапазонов ячеек и еще число 500. Первый диапазон — это прямоугольный блок задаваемый адресом левой верхней ячейки В5 и правой нижней С2 (между адресами крайних ячеек диапазона стоит двоеточие). После точки с запятой указан второй диапазон — Е6:Е11.

Функцию СУММ можно вставить кнопкой Автосумма (на панели Стандартная) с греческой буквой [?] — сигма. Если поставить табличный курсор в ячейку, где надо разместить значение «Итого», и нажать кнопку Автосумма, то программа вставит функцию суммировании ни диапазону ближайших ячеек с числами, выделит рамкой диапазон Если диапазон ячеек, который выделен программой, не устраивает, можно исправить границы диапазона с клавиатуры или изменим мерцающую рамку диапазона указателем мыши. В последних версии Excel кнопку [?] можно распахнуть и выбрать варианты различных функций.

Статистическая функция =СЧЕТ(диапазон) подсчитывает, см ни ко чисел в диапазоне — количество чисел (числовых ячеек) в интервалах и массивах. Считает только ячейки с числами без пустых и текстовых данных.

Статистическая функция =СЧЕТЗ (диапазон) ведет счет заполненных ячеек в диапазоне с числами или текстом, но не пустых (в названии функции буква 3, но не цифра 3).

Статистическая функция =СЧЕТЕСЛИ(диапазон;критерий) считает, сколько раз в диапазоне ячеек встречается значение, удовлетворяющее критерию.

Таблица 15.4

Данные о женщинах-заключенных

	А	В	С	D	Е	F	G	Н	I
1	Фамилия	А-ва	Б-ая	В-ская	Г-ва	Д-х	Е-ир	Ж-на	3-ва
2	Кол-во детей	2		1	3		2		1
3	Фамилия	И-ская	К-на	Л-ско	Н-ая	О-ва	П-ко	Р-ч	С-ва
4	Кол-во детей	Берем-ть, 7мес	3	2	2				2

По таблице 15.4 функция = СЧЕТ(В1:11;B3:I3) в диапазоне ячеек с Числовыми данными о наличии детей вычислит, сколько женщин-за-ключенных имеют детей и поэтому подлежат амнистии. Функция =СЧЕТ(A1:I1;F3:I3) подсчитает общее число женщин, а функция СЧЕТЕСЛИ(В1:13; «>1») — сколько женщин имеют более одного ребенка (критерий — неравенство в кавычках).

Логические функции

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

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

Логическая функция =И( ; 😉 может содержать несколько проверяемых условий (логических значений через точку с запятой); возврата значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Пример: =H(G15>0;D4=13%*b5).

Логическая функция = ИЛИ( ; ; ) может содержать несколько условий (через точку с запятой); возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Пример: =ИЛИ(G15>0;D4=13%*b5).

Функция =НЕ( ) проверяет условие и меняет значение ИСТИНА на ЛОЖЬ, а ЛОЖЬ на ИСТИНА, то есть логическое значение своп о аргумента меняет на противоположное; используется в случаях, когда необходимо быть уверенным в том, что указанное условие не выполняется. Пример: =HE(G15>0).

Логическая функция ЕСЛИ( ; ; ) проверяет условие (гипотезу) и отображает одно из двух значений. Функция имеет три аргумента (or деленных точкой с запятой), но условием является только первым аргумент; синтаксис функции:

=ЕСЛИ (условие; значение если истина; значение если ложь).

Или короче:

=ЕСЛИ(условие; если да; если нет).

Если условие выполняется (истина), то отображается значение если истина (записанное после точки с запятой на второй позиции), если условие не выполняется (ложь), то отображается после второй точки с запятой значение если ложь.

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

Логическое выражение задается операторами «больше», «меньше», «равно», «больше или равно», «меньше или равно»; по результатам проверки выражения выдается логическое значение ИСТИНА или ЛОЖЬ. Если выражение дает логическое значение ИСТИНА, то функции ЕСЛИ возвращает значение своего второго аргумента («если да»). Возвращает, то есть выдает за значение ячейки. Если условие дает ЛОЖЬ, то функция возвращает значение своего третьего аргумента («если нет»)

Функция =ЕСЛИ(С12>3;Я20;45) означает: если в ячейке С12 значение больше чем 3, то вывести (возвратить) значение ячейки R20, а если нет, то возвратить число 45. Функция =ЕСЛИ(F5>0;20%*F5;10%*F5) возвращает 20% от введенного числа F5 или 10% от числа в зависимости от знака из ячейки F5.

Функция с текстом в кавычках =ЕСЛИ(Р8>0;«Сектор Приз»; « Вы банкрот») возвращает в свою ячейку текст в зависимости от знака числа из ячейки F8.

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

=ЕСЛИ(ИЛИ(А5>=20;А5<3);С5-В5;0), =ЕСЛИ(И(В4>0;С4>0);А4*В4;А4/С4).

Функции дат и времени

Программа Excel для Windows использует систему дam 1900, хранит даты как ряд последовательных номеров: по умолчанию 1 января 1900 г. Иметь номер 1, а 16 февраля 2001-го — номер 36938, так как интервал в днях между этими датами составляет 36938. Конец отсчета — 31 декабря 9999 г. Годы с 00 до 29 воспринимаются как 2000 и 2029, годы 1900 -1929, 2030 год и далее надо вводить полностью. В Excel для Mmintosh система дат 1904— отсчет дат ведется с 1904 г.

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

Функция =ДАТА(год; месяц; день) возвращает День. Месяц. Год, то есть аргументы вводятся в обратной последовательности, а выводится Mpoi[ейский европейский (русский) стандарт даты. Например, функция »ДАТА(2006;12;1) возвратит 01.12.06.

Функции =СЕГОДНЯ() и =ТДАТА() не имеют аргументов в скобках. Сверившись с системными часами компьютера, функция =ТДАТА возвращает сегодняшнюю дату, а функция =ТДАТА() возвращает и текущую дату, и текущее время.

Рассмотрим случай, когда в ячейку С5 введена функция =ТДАТА() и формат ячейки настроен так, чтобы показывать дату и время, например 1.12.06 11:15. В других ячейках можно вывести избирательные сведения с помощью функций, ссылающихся на ячейку С5.

Функция =ДЕНЬНЕД(С5) выведет день недели 6 (шестой день не- Пи — суббота).

Функция =ГОД(С5) выведет 2006.

Функция =МЕСЯЦ(С5) выведет 12 (декабрь).

Функция =ЧАС(С5) выведет 11 (часов).

Функция =МИНУТЫ(С5) выведет 15 (минут).

Формат значений времени и дат устанавливает команда Формат, Ячейки, Число, Дата (или Время) по образцам или обозначениям формата:

ДД.ММ.ГГ — означает без пробела День.Месяц.Год, разделенные точкой, без точки в конце, например 31.12.98 и 13.08.07;

ЧЧ:ММ:СС или ЧЧ:ММ (без пробела) — означает, что часы, минуты и секунды будут представлены в виде 11:35:22 или 23:40.

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

Число дней между датами вычисляется по формуле разности, с записью каждой даты в кавычках по шаблону =»ДД.ММ.ГТ» «ДД-ММ.ГГ». Например, формула =»30.06.2012″-«01.09.2007» возвратит число 1764 — дни за 5 лет обучения в вузе с учетом високосных лет. Еще пример: срок завершения работ по контракту 31.12.2006, фактп ческий срок завершения работ 14.11.2007. Опоздание в днях вычисляет формула -«14.11.2007»-«31.12.2006».

Формулы вычислений с датами удобно записывать с адресами ячеек, в которых введены даты. Например, формула =К4-С4 из адреса ячейки К4 с поздней датой вычитает адрес ячейки С4, со держащей раннюю дату. Кавычки к адресу ячейки при такой записи не нужны. Если в С40 ввести прибытие по расписанию 10:14, и D40 фактическое прибытие 17:08, то время опоздания составит =C40-D40.

Чтобы узнать дату через определенное число дней, дни можно прибавлять к дате и вычитать. Например, прибавить 60 дней =»1.01.2006″+60 или вычесть 100 дней =СЕГОДНЯ()-100.

При вычитании и сложении можно применять функцию =ДАТА(год;месяц;день), у которой обратный порядок аргументом разделенных точкой с запятой. Например:

=ДАТА(1945;05;09)-ДАТА(1941;06;22).

Аргументы функции можно представить адресами ячеек, в которых записаны числа, например: =ДАТА(С40;040;Е40).

Текстовые функции

Функция =ТЕКСТ(значение;формат) форматирует число (или значение из формулы, из другой ячейки) и преобразует его в текст. Формат в кавычках описывается так, как в окне команды формат, ячейка, число. Например, формат «0%» представит число 0,413 в виде текста 41% формат «0,0%» — в виде 41,3%; число 32, 365 в формате «0руб.» представится текстом 32,00 руб., а в формате «0,00руб.» — текстом 32,37руб.; Функция =ТЕКСТ(С5;»ДДДД») прочитает дату, записанную в ячейке С5, например 29.03.07, и возвратит день недели слоном четверг; в формате «ДДД» (три буквы) будет выведена сокращенная запись дня недели — Чт, а в формате «ДД» (две буквы) — просто само число 29.

Функция объединения. Функция =СЦЕПИТЬ(Текст1;Текст2;…)

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

Например, поданным в ячейках табл. 15.5 функция =СЦЕПИТЬ(А2; В2;»осужден на»;Е2;»лет по статье»;С2;»»; D2) Выведет объединенный текст: «Свидригайлов Альберт осужден на 9 лет по статье 35 УК РФ». В функции через точку с запятой указаны Адреса ячеек и в кавычках — сцепляющий текст вместе с пробелами.

Таблица 15.5

Данные об осужденных

	А	В	С	D	Е	F
1	Фамилия	Имя	Статья	Кодекс	Срок (лет)
2	Свидригайлов	Альберт	35	УК РФ	9
3

Если в ячейку D3 ввести время 10:00, а в ячейку ЕЗ — 20:00, то функция

=СЦЕПИТЬ(«Посетите нашу адвокатскую контору по рабочим Дням с «;»TEKCT(D3;»44:MM»);» до «;»ТЕКСТ(ЕЗ;»ЧЧ:ММ»)») выведет объединенный текст «Посетите нашу адвокатскую контору но рабочим дням с 10:00 до 20:00». Функция ТЕКСТ отображает число в формате времени «часы:минуты», если же функцию не применить, то из ячейки ЕЗ вместо 20:00 будет отображено число 0,8833.

Если на часах компьютера 23 сентября 2007 г., то функция

=ЦЕПИТЬ(«Балансовый отчет от»; ТЕКСТ(СЕГОДНЯ(),»ДД ММММ ГГГГ»)) но тратит текст Балансовый отчет от 23 сентября 2007, а функция —

СЦЕПИТЬ(«Сводка преступлений, г. Элиста»; 11ХСТ(СЕГОДНЯ(),»ДД.ММ.ГГ»)) но тратит текст Сводка преступлений, г.Элиста, 30.09.07.

Формат ДД ММММ ГГГГ возвращает месяц словом и год четырехзначным числом (23 сентября 2007), а формат ДД.ММ.ГТ возвращает 23.09.07.

табличный процессор excel редактор

Абсолютные и относительные адреса ячеек

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

При копировании формул табличный редактор учитывает это важное свойство таблиц. Копирование формулы, содержащей относи тельные ссылки, в новую ячейку автоматически перестраивает ссылки, указывая измененные адреса ячеек. Обычная адресация ссылок и формулах и функциях, которая перестраивает адреса относительно нового положения копии ячейки с формулой, называется относительной адресацией. Если в какой-то ячейке записана формула с адресами сомножителей =В2*С2, то ее копирование в ячейку того же столбца на строку ниже изменит записанные в формуле ссылки на адреса ячеек, увеличив номер строки на +1. Формула перестроится как =ВЗ*СЗ (относительно нового места).

Чтобы ссылки на адреса не изменялись при копировании формулы или функции в другую ячейку, используют абсолютную адресации> ячеек (абсолютные ссылки). Например, адрес ячейки с курсом валют пи товарном счете будет использован ячейками строк разных товаром, цена которых дана в валюте. Абсолютная адресация, которая при копировании не перестраивается, устанавливается символом $, напри мер $D$7. Возможна смешанная адресация. Например, ссылка на адрес Н$5 разрешает при копировании изменять имя столбца Н, а номер строки 5 остается одним и тем же. Символ $ с клавиатуры набирать не обязательно, надо поставить курсор на адрес в формуле и нажать клавишу F4. Ссылка на адрес D7 превратится в $D$7, а после еще одного нажатия в D$7 и т.д.

Другой вариант абсолютного адреса — дать имя ячейке или диапазону и сделать в формулах ссылку не на адреса, а на это имя командной Вставка, Имя, Вставить. Если, например, ячейке, где выполняется автосуммирование данных, присвоить имя Итого, то можно написан, формулу =Н7/Итого. Имя ячейки Итого как абсолютный адрес будет использоваться для расчета долевой части каждой позиции в строках I таблицы.

Пример создания таблицы «Потребительская корзина»

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

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

Требуется заполнить данные по табл. 15.6 и выполнить расчет. Клавиша Enter вводит набираемые данные в ячейку. В ячейках А5…Е5 можно применить команду Формат, Ячейки, Выравнивание, переносить по словам или сочетанием клавиш Alt+Enter начать новую строку внутри ячейки.

В ячейке Е6 формула перемножает числа из ячеек С6 и D6. Адреса ссылок на ячейки можно набрать с клавиатуры, а можно после знака равенства щелкнуть указателем мыши ячейку С6, потом поставить знак умножения * (серая клавиша) и щелкнуть ячейку D6.

Формулы в ячейках Е6…Е10 однородные — в них меняются только адреса ссылок, причем соответственно смещению по строкам вниз.

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

• Копирование командами меню. Выделить ячейку Е6 с написанной формулой (поставить на ячейку прямоугольный курсор). Дать команду Правка, Копировать. Потом выделить ячейки Е7:Е10 (протащить по ячейкам прямоугольный курсор). Дать команду Правка, Вставить. В каждую ячейку вставится формула со смещением адреса ссылок: в ячейку Е7 формула =C7*D7, в ячейку Е8 формула =C8*D8 и т.д.

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

• Копирование контекстными командами. Левой кнопкой мыши выделить ячейку Е6 для копирования. Затем щелкнуть эту выделенную ячейку правой кнопкой мыши и дать команду Копировать. Левой кнопкой и указателем мыши выделить ячейки Е6:ЕЮ для размещения копирования, а правой кнопкой мыши щелкнуть выделение и выбрать команду Вставить.

Итоговое суммирование в последнем столбце таблицы выполняется в ячейке Ell. После знака равенства следует написать функцию суммирования =СУММ(Е6:10), в скобках указать диапазон ячеек Е6:Е10, чтобы просуммировать данные в ячейках от Е6 до Е10.

Можно выделить ячейки С6:С10 и ячейки Е6:Е10, дать команду Формат, Ячейки, Число, Финансовый. Денежный формат ставят кнопкой Денежный на панели Форматирование. Получится форматирование

6 Хлеб Буханка 8,00 р. 10 80,00 р.

Внимание! Не существует кнопки, чтобы снять денежный формате ячейки. Надо дать команду Формат, Ячейка, Число и выбрать новый формат. Можно набирать р. вместе с цифрами в ячейке — содержимое ячейки останется числом.

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

Данные на Составили Дата расчета	Октябрь 2005 г. АЛышкин, О.Покацкая 15.10.2005
Продукты, товары, услуги	Единица измерения	Цена за единицу измерения	Кол-во в месяц	Стоимость
Молоко	литр	11.70 р.	9	105,30 р.
Мясо	кг	109 р.	2.5	272,00 р.
Сахар	кг	20 р.	0.9	20,70 р.
Хлеб	буханка	8,00 р.	10	80,00 р.
Эл.транспорт	билет	5 Р-	50	250,00 р.
	Итого	728,00 р.

Рис. 15.15. Оформление таблицы

Количество знаков в десятичной дроби после запятой меняют кнопки Увеличить (Уменьшить) разрядность.

Сетка в окне листа Excel показывается для удобства работы, принтер ее не печатает. Поэтому для построения сетки и рамок надо вылепить ячейки созданной таблицы и дать команду Формат, Ячейка, Граница, определить тип линии (одинарная, двойная, другая), толщину пинии, цвет. Можно независимо определить внешние, внутренние мни отдельные линии для выделенного блока ячеек параметрами уточнения Формат, Ячейка, Граница, Внешние (или Внутренние, или Отдельные). Настройку границ можно проделать и щелчками мыши по образцу в окне команды.

Команда Формат, Ячейка, Вид устанавливает заливку выделенных Ячеек таблицы.

Финансовые функции

Денежные потоки идут как разовые или периодические платежи.

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

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

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

Внимание! Имена некоторых финансовых функций для версий Excel 2000 и Excel ХР приведены в табл. 15.7 прописными буквами, а аргументы функций — строчными.

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

Таблица 15.7

Финансовые функции

Название функции	Имя функции и ее аргументы
	Excel 2000	Excel ХР (2003)
Будущая стоимость	=БЗ(норма; кпер;пплат;-пз;тип)	=БС(ставка; кпер;плт;-пс;тип)
Первоначальное значение (Приведенная стоимость)	=ПЗ(норма;кпер;пппэг;бс;тип)	=ПС(ставка; кпер;плт;бс;тип)
Количество Периодов	=КПЕР(норма;пплат;-пз;6с;тип)	=КПЕР(ставка;плт;-пс;бс;тип)
Ставка, %	=НОРМА(кпер;пплат;-пз;6с;тип)	=СТАВКА(кпер;плт;-пс;6с;тип)
Периодическая плата	=ПП ЛАТ(ставка; кп ер; -пз; бс;ти п)	=ПЛТ(ставка;кпер;-пс;бс;тип)

Процентная ставка за период — СТАВКА в Excel 2000 или НОРМА в Excel ХР.

Общее число периодов платежей по ренте — КПЕР.

Периодическая плата (платеж, выплата), производимая в каждый период, — ППЛАТ в Excel 2000 или ПЛТ в Excel ХР. Это значение не может меняться в течение всего периода выплат.

Первоначальное значение (приведенная стоимость) на текущий момент — ПЗ в Excel 2000, ПС в Excel ХР, значение, на текущий момент равноценное ряду будущих платежей ПЛТ или будущей стоимости БС. Функция ПЗ (или ПС) «приводит» к текущей стоимости будущие платежи с учетом процентной ставки.

Тип выплат — параметр, который не требуется задавать при разовых платежах, но когда производится периодическая выплата, то число 0 обозначает выплату в начале периода, а 1 — в конце периода. Ее ли этот параметр в задаче на периодические платежи опущен, он полагается равным 0.

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

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

Таблица 15.8

Расчет будущей стоимости разового платежа

	В	С
28	Текст	Данные
29	Ставка,%	10%
30	Количество периодов (лет)	3
31	Первоначальная стоимость, р.	10000 р.
32	Будущее значение (Excel 2000)	=БЗ(С29;С30;;-С31;)
33	Будущая стоимость (Excel ХР)	=БС(С29;С30;;-С31;)

Рассмотрим расчет будущего значения разового платежа. В банк с годовой ставкой 10% на 3 года сделан вклад 10 ООО рублей. Для того чтобы определить, сколько денег на счету будет к концу срока, требуется ввести в ячейки данные (с указанием формата: процентный, финансовый) и функцию (табл. 15.8).

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

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

Периодичность начисления процентов. Ставка процентная задается за период. Банк объявляет годовую ставку (норму доходности) — например, 3%, то есть 0,03. Бывает, что объявлена годовая ставка, а про-

Таблица 15.9

Рост вклада и начисляемых процентов по годам

Вклад и проценты	2006	2007	2008
Иклад на начало года	10000,00р.	11 000,00р.	12 100,00 р.
J0% годовых	1 000,00 р.	1 100,00 р.	1 210.00 р.
Вклад на конец года	11 000,00 р.	12 100,00 р.	13 310,00 р.

центы начисляются не раз в году, а несколько раз в год — Квгод (по полугодиям, ежеквартально, ежемесячно). Тогда нужно обязательно согласовать количество периодов и ставку одного периода. В функции БС(…) изменить запись аргументов, то есть прямо в функции в позиции аргументов записывать формулы. Вместо ставки записать: %годовой / Квгод (где / — знак деления), а вместо кпер ввести: Квгод * Lлет (где * — знак умножения, L — количество лет).

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

Возможности электронных таблиц в экономике и управлении

Обработка данных средствами электронных таблиц

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

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

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

• проведения однотипных расчетов над большими наборами данных;

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

• решения задач путем подбора значений параметров, табулирования формул;

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

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

• подготовки табличных документов;

• построения диаграмм и графиков по имеющимся данным.

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

Содержание электронной таблицы

Формулы

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

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

Ссылки на ячейки

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

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

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

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

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

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

Пусть, например, в ячейке В2 имеется ссылка на ячейку A3. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку ЕА27 ссылка будет продолжать указывать на ячейку, располагающуюся левее и ниже, в данном случае на ячейку DZ28.

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как А1, $А$1, А$1 и $А1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой — как относительный.

Копирование содержимого ячеек

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

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

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

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

Автоматизация ввода

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

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

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

Автозаполнение числами. При работе с числами используется метод автозаполнения. В правом нижнем углу рамки текущей ячейки имеется черный квадратик — маркер заполнения. При наведении на него указатель мыши (он обычно имеет вид толстого белого креста) приобретает форму тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция «размножения» содержимого ячейки в горизонтальном или вертикальном направлении.

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

Пусть, например, ячейка А1 содержит число 1. Наведите указатель мыши на маркер заполнения, нажмите правую кнопку мыши и перетащите маркер заполнения так, чтобы рамка охватила ячейки А1, В1 и С1, и отпустите кнопку мыши. Если теперь выбрать в открывшемся меню пункт Копировать ячейки, все ячейки будут содержать число 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа 1, 2 и 3.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Правка > Заполнить > Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке ОК программа Excel автоматически заполняет ячейки в соответствии с заданными правилами.

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

Для примера предположим, что значения в третьем столбце рабочего листа (столбце С) вычисляются как суммы значений в соответствующих ячейках столбцов А и В. Введем в ячейку С1 формулу =А1+В1. Теперь скопируем эту формулу методом автозаполнения во все ячейки третьего столбца таблицы. Благодаря относительной адресации формула будет правильной для всех ячеек данного столбца.

В таблице 12.1 приведены правила обновления ссылок при автозаполнении вдоль строки или вдоль столбца.

Таблица 12.1. Правила обновления ссылок при автозаполнении

Ссылка в исходной ячейке	Ссылка в следующей ячейке
	При заполнении вправо	При заполнении вниз
А1 (относительная)	В1	А2
$А1 (абсолютная по столбцу)	$А1	$А2
А$1 (абсолютная по строке)	В$1	А$1
$А$1 (абсолютная)	$А$1	$А$1

Использование стандартных функций

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

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

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

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

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

Применение электронных таблиц для расчетов

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

Итоговые вычисления

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

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

Суммирование

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

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

Функции для итоговых вычислений

Прочие функции для итоговых вычислений выбираются обычным образом, с помощью раскрывающегося списка в строке формул или с использованием мастера функций. Все эти функции относятся к категории Статистические. В их число входят функции ДИСП (вычисляет дисперсию), МАКС (максимальное число в диапазоне), СРЗНАЧ (среднее арифметическое значение чисел диапазона), СЧЕТ (подсчет ячеек с числами в диапазоне) и другие.

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

Использование надстроек

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

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

Вот основные надстройки, поставляемые вместе с программой Excel.

Пакет анализа (Analysis ToolPak). Обеспечивает дополнительные возможности анализа наборов данных. Выбор конкретного метода анализа осуществляется в диалоговом окне Data Analysis (Анализ данных), которое открывается командой Сервис > Data Analysis (Анализ данных).

Мастер суммирования {ConditionalSum Wizard). Позволяет автоматизировать создание формул для суммирования данных в столбце таблицы. При этом ячейки могут включаться в сумму только при выполнении определенных условий. Запуск мастера осуществляется с помощью команды Сервис > Conditional Sum (Частичная сумма).

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

Поиск решения (Solver Add-in). Эта надстройка используется для решения задач оптимизации. Ячейки, для которых подбираются оптимальные значения и задаются ограничения, выбираются в диалоговом окне Solver Parameters (Поиск решения), которое открывают при помощи команды Сервис > Solver (Поиск решения).

Построение диаграмм и графиков

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

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

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

Выбор типа диаграммы

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

Выбор данных

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

Оформление диаграммы

Третий этап работы мастера (после щелчка на кнопке Далее) состоит в выборе оформления диаграммы. На вкладках окна мастера задаются:

• название диаграммы, подписи осей (вкладка Заголовки);

• отображение и маркировка осей координат (вкладка Оси);

• отображение сетки линий, параллельных осям координат (вкладка Линии сетки);

• описание построенных графиков (вкладка Легенда);

• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных);

• представление данных, использованных при построении графика, в виде таблицы (вкладка Таблица данных).

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

Размещение диаграммы

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

Редактирование диаграммы

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

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

Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она расположена (Правка > Удалить лист), или выбрать диаграмму, внедренную в рабочий лист с данными, и нажать клавишу DELETE.

Заключение

Microsoft Excel — одна из самых популярных программ вычислений электронных таблиц. Главной особенностью её применения является выполнение расчётов, как в бизнесе, так и в быту. Если в своей работе мы часто используем разного рода таблицы, списки, бланки, при заполнении которых производятся вычисления по формулам, то эта программа для нас. С её помощью можно проанализировать полученные числовые данные, применяя удобные встроенные инструменты, а также представить результаты в графическом виде. В расчётах можно использовать более 400 математических, статистических, финансовых и других специализированных функций, связывать различные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры и т.д.

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

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

Литература

1. Информатика. Базовый курс. 2-е издание / Под ред. С.В. Симоновича. — СПб.: Питер, 2007. — 640 с.: ил.

2. Гаврилов М.В. Информатика и информационные технологии : учебник для студентов вузов / М.В. Гаврилов — М.: Гардарики, 2007. — 655 с.: ил.

3. Симонович С.В., Евсеев Г.А., Алексеев А.Г. Специальная информатика: Учебное пособие — М.: АСТ — ПРЕСС КНИГА, 2005. 480 с.

4. Горохова Т.В. Табличный процессор Microsoft Excel: Учеб. метод. пособие. -http://www.loiro.ru/files/users_27_excel.pdf.

5. Информатика и информационные технологии. Под ред. Романовой Ю.Д. 3-е изд., перераб. и доп. — М.: Эксло, 2008. — 592 с.

Размещено на