Вычисления в Excel. Формулы и функции

Табличный редактор Excel

Основные понятия

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

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

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

Основные приемущества MS Excel:

1. Подобно прочим электронным таблицам, на экране Excel появляется сетка из строк и столбцов — электронная книга учета, в которую вводятся данные. Каждая клеточка сетки (пересечение строки и столбца) называется ячейкой. Обычно в ячейки заносятся числа, но туда можно поместить и текст с описанием, например, заголовки строк и столбцов.

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

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

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

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

Основу структуры листа Excel составляют столбцы, которые располагаются сверху вниз, и, строки, проходящие слева направо. Столбцы обозначаются буквами, а строки - цифрами. Заголовки столбцов - это буквы A, B, C..., а заголовки строк - это цифры 1, 2, 3 и т.д. На пересечении каждого столбца и каждой строки располагается небольшой прямоугольник, называемый ячейка. Каждая ячейка имеет свой адрес, который определяется соответствующими столбцом и строкой. Например, на пересечении столбца С со строкой 3 располагается ячейка С3.

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

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

Рабочие книги - это файлы MS Excel, которые могут содержать один или несколько рабочих листов.

Основные элементы окна Excel (рис. 9.1):

1. Поле названия главного окна.
2. Строка меню.
3. Кнопки управления
4. Панели инструментов.
5. Поле имени.
6. Строка формулы.
7. Строка состояния
8. Поле названия окна рабочей книги (дочернее окно Excel).
9. Активный лист рабочей книги.
10. Кнопка выделения всего листа.
11. Активная (текущая) ячейка.
12. Имя столбца.
13. Имя строки.

Рис. 9.1 Элементы рабочего листа

Ячейка - это основной элемент электронной таблицы только в ней может содержаться какая-либо информация. В ячейке таблицы может находиться:

текст;
число;
дата;
формула.

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

Структура ячейки Excel:

1-й уровень содержит видимое на экране изображение (т.е. отформатированный текст) или результат вычисления формулы).
2-й уровень содержит форматы ячейки (формат чисел, шрифты, выключатель (включатель) признак показывать или нет ячейку, вид рамки, защита ячейки).
3-й уровень содержит формулу, которая может состоять из текста, числа или встроенных функций.
4-й уровень содержит имя ячейки, это имя может использоваться в формулах других ячеек, при этом обеспечивается абсолютная адресация данной ячейки.
5-й уровень содержит примечания данной ячейки (произвольный текст). Если ячейка содержит примечание, то в правом верхнем углу появляется красный квадратик (точка)

Рис.9.2. Уровни ячейки

Блок ячеек - группа ячейками.

Строка ввода - полоса под панелями инструментов Microsoft Excel, используется для ввода или редактирования значений или формул в ячейках или диаграммах. Здесь выводится постоянное значение или формула активной ячейки. Для ввода данных выделите ячейку, введите данные и щелкните по кнопке с зеленой “галочкой” или нажмите ENTER. Данные появляются в строке формул по мере их набора.

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

Данные в Excel выводятся на экран в определенном формате. По умолчанию информация выводиться в формате Общий. Можно изменить формат представления информации в выделенных ячейках (Формат | Ячейки).

Вычисления в Excel. Формулы и функции

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

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

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

Использование имен обеспечивает следующие преимущества:

Формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки.
Например, формула “=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6”.
При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте — в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.
После того как имя определено, оно может использоваться в любом месте рабочей книги. Доступ ко всем именам из любого рабочего листа можно получить с помощью окна имени в левой части строки формул.
Вы можете также определить специальные имена, диапазон действия которых ограничивается текущим рабочим листом. Это означает, что эти имена можно использовать лишь на том рабочем листе, на котором они определены. Такие имена не отображаются в окне имени строки формул или окне диалога “Присвоить имя”, если активен другой рабочий лист книги.
Excel автоматическое создает имена на основе заголовков строк и столбцов рабочего листа. Подробной информация о создании таких имен содержится в главе “Базы данных”.
После того, как имя определено, вы можете:
Заменить все соответствующие ссылки этим именем во всех местах рабочего листа.
Быстро перейти на поименованную ссылку, заменить ссылки, вставить ссылку в формулу с помощью окна имени в строке формул.

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

При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы “$”. Изменяться только те атрибуты адреса ячейки, перед которыми не стоит символ “$”. Если перед всеми атрибутами адреса ячейки поставить символ “$”, то при копировании формулы ссылка не изменится.

Если в ссылке используются символы $, то она называется абсолютной, если символов $ в ссылке нет — относительной. Адреса таких ссылок называются абсолютными и относительными, соответственно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мастер функций вызывается командой Вставка | Функции или нажатием на кнопку Мастер функций

Ошибки в формулах

Если формула обработана неправильно, Microsoft Excel отображает ошибку. Причины возникновения ошибок могут быть самыми разными:

##### - результат обработки формулы не умещается в ячейке или результатом выполнения формулы, оперирующей датами и временем, является отрицательное число.
#ЗНАЧ! - используется недопустимый тип аргумента или операнда.
#ДЕЛ/0! - в формуле предпринимается попытка деления на ноль.
#ИМЯ? - Excel не может распознать имя, используемое в формуле.
#Н/Д - неопределенные данные (чаще всего встречается, если некорректно определены аргументы функции).
#ССЫЛКА! - используется недопустимая ссылка на ячейку (например, ячейки, на которые ссылается формула, были удалены).
#ЧИСЛО! - возвращаемое числовое значение слишком велико или слишком мало, чтобы его можно было представить в Microsoft Excel (диапазон отображаемых чисел от -10307 до 10307).
#ПУСТО! - задано пересечение двух областей, которые в действительности не имеют общих ячеек.

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

Описание различных функций Excel

Функция	Синтаксис	Описание	Пример
СУММ	СУММ(Диапазон![; Диапазон2;... ])	Вычисляет сумму содержимого ячеек указанных диапазонов	CYMM(D2:D4) СУММ(02:04; F2:F4)
СРЗНАЧ	СРЗНАЧ(Диапазон![; Диапазон2;... ])	Вычисляет среднее арифметическое содержимого ячеек указанных диапазонов	СРЗНАЧ(В2:В11)
ОКРУГЛ	ОКРУГЛ(Число; Разрядов]	Возвращает значение, полученное путем округления числа до указанного количества разрядов.	ОКРУГЛ(Е8;2) ОКРУГЛ(СУММ(ЕЗ:Е7);2)
ОКРВВЕРХ	ОКРВВЕ РХ(Число; Точность)	Возвращает значение, полученное в результате округления числа в сторону увеличения с указанной точностью.	ОКРВВЕРХ(351;10) 360 ОКРВВЕРХ(353;100) 400 ОКРВВЕРХ(125300,-1000) 126000 ОКРВВЕРХ(23,345;0,1) 23,4 ОКРВВЕРХ(7,513;0,01) 7,52
ОКРВНИЗ	ОКРВНИЗ(Число; Точность)	Возвращает значение, полученное в результате округления числа в сторону уменьшения с указанной точностью. Функцию можно использовать для округления дробных чисел. В этом случае параметр точность указывает, до какого разряда нужно выполнить округление.	ОКРВНИЗ(351;10) 350 ОКРВНИЗ(353;100) 300 ОКРВНИЗ(125300;1000) 125000 ОКРВНИЗ(2,447;001) 2,44 ОКРВНИЗ(2,99;1) 2
ОСТ AT	ОСТАТ(Делимое; Делитель)	Вычисляет остаток от деления одного числа на другое
ЦЕЛОЕ	ЦЕЛОЕ(Выражение)	Возвращает целую часть значения выражения	ЦЕЛОЕ(D5/B1) ЦЕЛОЕ(F5/3)
МАКС	МАКС(Диапазон![; Диалазон2;... ])	Возвращает максимальное значение указанных диапазонов	МАКС(В2:В4)
МИН	МИН(Диапазон![; Диапазон2;... ])	Возвращает минимальное значение указанных диапазонов	МИН(С2:С4)
СЧЕТ	СЧЕТ (Диапазон)	Возвращает количество ячеек указанного диапазона, в которых находятся числа, в том числе и вычисленные по формуле	C4ET(D3:D10) C4ET(D3:E10)
СЧЕТЕСЛИ	СЧЕТЕСЛИ(Диапазон; Критерий)	Подсчитывает количество ячеек диапазона, удовлетворяющих заданному критерию. В качестве условия можно использовать число, строку символов или выражение вида оператор константа. Оператор — это один математических операторов сравнения: — больше; < — меньше; >= — больше или равно; <= — меньше или равно; = — равно; о — не равно. Константа — число или строка символов, критерий должен быть заключен в двойные кавычки.	СЧЕТЕСЛИ(В2:В10,• "<>0") — подсчитывает количество ячеек диапазона В2:вю, содержимое которых не равно нулю СЧЕТЕСЛИ (С2: СЮ; ">1000") — подсчитывает количество ячеек диапазона В2:сю, содержимое которых больше 1000
ЕПУСТО	ЕПУСТО(Ячейка]	Функция ЕПУСТО возвращает логическое значение ИСТИНА, если ячейка пустая	ЕПУСТО(С2)
ЕСЛИ	ЕСЛИ(Условие; Значение!; Значение2)	В зависимости от значения условия возвращает значение! или Значение2	ЕСЛИ(П5>500;0,1;0) если содержимое ячейки D5 больше 500, то значение функции равно 0,1, в противном случае (если D5 меньше или равно 500) значение функции равно нулю.
ВЫБОР	ВЫБОР(Индекс; Элемент!; Элемент2;...)	Возвращает элемент списка, номер которого указан в качестве первого параметра функции. В качестве элемента списка может выступать число, строка символов или диапазон. Максимальное количество элементов списка равно 29	ВЫБОР(02;В2:В10; С2:С10; D2:D10) ВЫБОР(ДЕНЬНЕД(СЕГОДНЯ 0,2);"ПН";"ВТ";"СР";"ЧТ";"ПТ";"СБ";"ВС")
ПОИСКПОЗ	ПОИСКПОЗ(Значение, Диапазон, Тип)	Выполняет поиск значения в диапазоне. Параметр тип задает способ сопоставления	ПОИСКПОЗ(1500;С2:С20;1)
ИНДЕКС	ИНДЕКС(ДиапазонСтрока; НомерЭлемента) ИНДЕКС(ДиапазонСголбец; НомерЭлемента) ИНДЕКС(ДиапазонОбласть; Строка; Столбец])	Возвращает элемент диапазона. Если диапазон является строкой или столбцом, то функция возвращает элемент "с указанным номером. Если диапазон является областью, то значение функции — элемент, находящийся на пересечении указанных строки и столбца	ИНДЕКС(А2:А4; 2) ИНДЕКС(А2:Е2; 2) ИНДЕКС(А2:С4; 2;2)
ГОД	ГОД (Дата)	Возвращает год указанной даты. В качестве параметра функции год обычно используют имя ячейки таблицы	ГОД(В2)
ДЕНЬ	ДЕНЬ(Дата)	Возвращает день указанной даты. В качестве параметра функции ДЕНЬ обычно используют имя ячейки таблицы	ДЕНЬ(В2) ДЕНЬ(СЕГОДНЯ())
ДЕНЬНЕД	ДЕНЬНЕД (Дата [; Тип])	Возвращает номер дня недели указанной даты. Параметр тип определяет день, с которого начинается неделя. Если параметр не указан или равен 1, то первый день недели — воскресенье. Если параметр Тип равен 2, то пер- вый день недели — понедельник	ДЕНЬНЕД(В2) ДЕНЬНЕД(В2;2) ДЕНЬНЕД(СЕГОДНЯ();2)
МЕСЯЦ	МЕСЯЦ (Дата)	Возвращает номер месяца указанной даты. В качестве параметра функции МЕСЯЦ обычно используют имя ячейки таблицы. Для получения названия месяца можно воспользоваться функцией ВЫБОР	МЕСЯЦ(В2) МЕСЯЦ(СЕГОДНЯ())
СЕГОДНЯ	СЕГОДНЯ()	Возвращает текущую дату. Содержимое ячейки, в которой находится функция СЕГОДНЯ, обновляется всякий раз при открытии таблицы	СЕГОДНЯ()
ТДАТА()	ТДАТА()	Возвращает текущие дату и время. Содержимое ячейки, в которой находится функция ТДАТА, обновляется автоматически при каждом открытии документа. Если содержимое ячейки должно быть обновлено непосредственно перед печатью таблицы, то нужно нажатием клавиши активизировать процесс пересчета формул. Для того чтобы в ячейке, в которой находится функция ТДАТА, было отображено только время (как в приведенном примере), для этой ячейки нужно задать формат ВРЕМЯ.	ТДАТА()

Cерии

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

Для построения произвольных серий выполните команду Правка | Заполнить | Прогрессия.

Типы прогрессии:

Арифметическая
Геометрическая
Даты
автозаполнение

Кроме прогрессии серии можно создавать еще несколькими способами без помощи меню, они приведены ниже.

Первый способ

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

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

Второй способ

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

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

Третий способ

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

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

Как уже говорилось выше третий способ является самым универсальным.

Диаграммы и графики

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

Рис. 9.3

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

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

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

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

Рис. 9.4

Существует три вида диаграмм:

обычная;
с накоплением;
нормированная.

Для построения диаграммы выполните команду Вставка | Диаграмма или нажмите кнопку Мастер диаграмм

Для редактирования диаграммы:

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

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

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

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

Рис. 9.5

Форматирование диаграммы выполняется путем форматирования отдельных элементов.

Основные форматы

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

Общий формат

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

Числовой формат

Числовой формат является наиболее универсальным (рис. 9.6). - В отличие от общего формата, в числовом формате можно задать количество отображаемых цифр дробной части (число десятичных знаков). При отображении дробных чисел выполняется округление в соответствии с заданным количеством цифр дробной части. Например, если установлен формат с отображением двух чисел дробной части, то число 567,897 будет представлено как 567,90. Это же число будет отображено как 568, если задать формат без отображения цифр дробной части (установить значение счетчика Число десятичных знаков равным нулю).

Рис. 9.6 Выбрать формат Числовой, можно настроить его характеристики

Денежный формат

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

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

Финансовый формат

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

Дата

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

Рис. 9.7 Способ отображения даты нужно выбрать в списке Тип

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

Проценты

При отображении содержимого ячейки в процентном формате реальное значение ячейки умножается на 100, и после числа выводится знак процента. Например, если в ячейку записать число 0,2 и установить формат Процентный, то в этой ячейке будет отображено 20,00 %.

Графика

На поверхность листа рабочей книги можно поместить иллюстрацию. Это может быть, например, логотип фирмы на прайс-листе или бланке. Иллюстрацию можно заранее подготовить при помощи различных графических редакторов, например Microsoft Paint, или нарисовать непосредственно в Excel.

Обработка данных

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

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

Сортировка

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

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

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

Рис. 9.8 Строки таблицы отсортированы по содержимому столбца Фамилия

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

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

Чтобы выполнить сортировку строк, надо выделить эти строки и щелкнуть на одной из кнопок Сортировка по возрастанию или Сортировка по убыванию.

Рис. 9.9 Кнопка для выполнения сортировки

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

Фильтры

Часто пользователя интересует не вся информация, находящаяся в базе данных, а конкретная выборка. Например, информация о расходах в январе, список студентов группы 221/2 и т.д.

Поиск нужной информации осуществляется путем отбора записей, удовлетворяющих критерию отбора. В большинстве случаев критерием отбора является равенство содержимого поля определенному значению. Например, критерием отбора записей, содержащих информацию о студентах группы 221/2, является равенство содержимого поля Группа строке 221/1. Помимо сравнения на равенство, при отборе записей можно использовать и другие операции сравнения. Например, больше, больше или равно, меньше, меньше или равно. Использование этих операций позволяет сформулировать критерий отбора менее жестко.

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

Сводная таблица

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

Сводная таблица состоит из:

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

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

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

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

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

Для построения сводных таблиц предусмотрен специальный инструмент - Мастер сводных таблиц (Данные - сводная таблица).

Сводная таблица строится за три шага:

1. На первом шаге мастер сводных таблиц выясняет где содержится источник данных для сводной таблицы.

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

3. На заключительном этапе работы мастер выясняет местоположение сводной таблицы.

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

Рис. 9.10 Меню вкладки Сводная Таблица

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

Вопросы для оценки уровня знаний

Что такое электронная таблица?
Основные приемущества Excel?
Что состовляет основу структуры листа Excel?
Что таоке рабочий лист и рабочая кннига?
Перечислите основные элементы окна Excel?
Что такое ячейка?
Структура ячейки?
Что такое формула в Excel?
Что означает символ $ в ссылках?
Как Excel реагирует на ошибки?
Что означает функция ЕСЛИ?
Для чего служат диаграммы?
Что такое ряд данных?
Назовите основные виды диаграмм используемые в Excel?
Перечислите основные форматы данных?
Чем отличается сортировка от филььтрации данных?
Что такое сводная таблица?
Из чего состоит сводная таблица?