Организация и принцип действия электронных таблиц

Электронные таблицы. Основные понятия

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

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

Адрес ячейки –координаты ячейки, образуемые названием столбца по горизонтали (А, В, С...) и номерами строки по вертикали (1, 2...). Например, А2, С3,... Такой адрес называется относительным, а ссылка на него в расчетах – относительной ссылкой.

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

Относительная ссылка – форма записи адреса ячейки, при которой этот адрес изменяется при копировании формулы (записывается =А1).

Абсолютная ссылка –форма записи адреса ячейки, при которой этот адрес остается неизменным при копировании формулы (записывается =$А$1).

Смешанная ссылка – это форма записи адреса ячейки, при котором при копировании формулы меняется одна из координат (обозначается: =$А1 или =А$1).

Диапазон ячеек – некоторое количество смежных ячеек. Они могут располагаться внутри столбца, строки или в виде прямоугольного блока. Обозначение их, соответственно, А1:А4, А1:D1, А1:D4.

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

Сортировка данных –расположение данных в определенном порядке по какому-либо признаку. Текстовые данные – в алфавитном или обратном порядке, числовые – по возрастанию или убыванию.

Фильтрация данных – отбор данных, удовлетворяющих определенным критериям. В качестве критериев используют условия, в которых содержимое ячейки и требуемое значение связываются оператором сравнения («=», «<», «>»…). Для проведения фильтрации предусмотрена команда меню Данные/Фильтр/Автофильтр (Расширенный фильтр).

Диаграмма – графическое представление табличных данных. Для построения диаграмм необходимо использовать «Мастер диаграмм», вызов которого осуществляется путем активизации кнопки «Диаграмма» на панели инструментов.

Типы диаграмм – наборы диаграмм различных видов, имеющие характерные внешние признаки. В Excel предусмотрены следующие типы диаграмм: «С областями», «Линейчатая», «Гистограмма», «График» и т.д.

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

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

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

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

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

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

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

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

Линия тренда – линия, аппроксимирующая точки массива данных. Используется при построении диаграмм и графиков.

Массивы – совокупность данных, расположенных в смежных ячейках, которые записываются в следующем виде А1:С5.

Одномерный массив – диапазон смежных ячеек, расположенных в строке или столбце.

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

Формула массива – специальный вид формул. Одна формула массива позволяет производить действия над диапазоном ячеек. Выражение вида {=(A1:A4)*A6} является формулой массива.

При создании формулы массива предварительно необходимо выделить диапазон ячеек, в который предполагается поместить результат, а создание формулы массива завершается нажатием комбинации клавиш <Ctrl + Shift + Enter>.

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

Операторы сравнения – математические знаки сравнения двух величин или выражений. К ним относятся: «=», «<», «>», и т.д.

Организация и принцип действия электронных таблиц

Введение. Microsoft Excel - это универсальная программа обработки данных, представленных в табличной фор­ме. Такие программы называются электрон­ными таблицами (ЭТ).

Запуск системы Excel. Осуществляется после входа в операционную систему щелчком мыши на значке Пуск в левом нижнем углу экрана. В открывшемся главном меню Windows активизируется пункт Программы и в следующем меню - Microsoft Excel. На экране появляется рабочее окно электронных таблиц, в которое загружается чистая Рабочая книга с новым окном документа, имею­щим временное название Книга1. На листах размещают самую различную информацию: таблицы, заголовки, примечания, графику.

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

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

Большую часть окна занимает Рабочий лист, разделённый Сеткой на ячейки. Управляют работой с сеткой командой Вид-Показать или закрыть.

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

Имя каждого листа (Лист1, Лист2..) выведено на ярлычок. Ярлык активного листа выделен.

По умолчанию открыты три рабочих листа. Добавление нового листа - Главная - группа Ячейки-Вставка-Вставить лист (перед текущим). Каждому листу можно дать содер­жательное имя, для чего - щелчкок мышью на его ярлычке и ввод имени.

Разделители листа – справа от горизонтальной полосы прокрутки | инад вертикальной панелью –– позволяют разделить лист на 2 или 4 части для одновременного просмотра нескольких областей одного большого документа.

Организация таблицы. Каждая ячейка таблицы имеет свой адрес, определяемый именем столбца и номером строки, на пересечении которых она находится, например, В4, С15. Строки нумеруются числами от 1 до 65536, а столбцы обозначаются одной или двумя латинскими буквами A, B, C,..Z, AA, AB,..IV. Важнейшим элементом таблицы является курсор - выделенный прямоугольник, отмечающий местоположение активной (выделенной, маркированной) ячейки.

Перемещение в таблице выполняется как мышью обычным для окон Windows образом, так и при помощи клавиш и их сочетаний: <Ctrl+Home>, <Ctrl+End> - в начало и в конец; <Ctrl+PgDn>, <Ctrl+PgUp> - постранично вправо, влево, <PgDn>, <PgUp> - вниз/вверх на экран; при этом перемеще­ние осуществляется только в пределах заполненной части таблицы.

Можно работать и с группой (интервалом, блоком) ячеек - копировать/перемещать, удалять.. Такую группу нужно выделить. Проще всего это делает мышь: ее курсор устанавливают в начальную (верхнюю левую) ячейку выделяемой области и при нажатой клавише мыши перемещают в нижнюю правую ячейку или клавишами < Shift - стрелки >. Адрес такой обла­сти записывается как адреса крайних полей, разделенные двоеточием, например, A8: C12. Если выделяется группа несмежных полей, то описанные выше действия выполняют при нажатой клавише <Ctrl>. Эти группы полей дополняются точкой с запятой (A8: C12; D15: E20). Выделение целой строки - щелчок на ее номере, столбца - на его заголовке. Быстрый способ выделения сплошного блока (в пределах экрана) -
<Shift> и по щелчку на крайних диагональных точках.

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

Ввод десятичной дроби можно начинать с запятой - например, число 0,83 - набрать как, 83.Простая дробь вводится с обязательной нулевой частью и последующим пробелом: 3/4 Þ 0_{<пробел>}3/4. Смешанная дробь, например число 2,1875 вводится в исходном виде.

Ввод даты или времени выполняется в одном из предлагаемых форматов (Главная.- Ячейки – Формат Ячейки - Дата/Время). Даты хранятся в памяти как числа, показывающие, сколько дней прошло от "начальной" даты - 1 января 1900 г. Это позволяет выполнять простейшие операции над датами. Даты вводятся в формулы как текст, заключенный в двойные кавычки. Например, формула ="13.08.91"-"01.08.91" возвращает число 12 (в числовом формате Общий).

Данные из строки формул можно ввести сразу в несколько полей, для чего - выделить диапа­зон клеток, в строку формул ввести выражение и - <Ctrl+Enter>.

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

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

Формула это сочетание опе­рандов, знаков операций и скобок. Она определяет, какие действия и в каком порядке нужно выполнить. Операндами в формулах могут быть конс­танты
(числовые, текстовые, логические, даты) или ссылки на них, т.е. их адреса. Адрес можно набирать в строке формулы (только в латинском алфавите!) или лучше задать его щелч­ком мыши на нужной клетке в процессе набора формулы. Любая формула – простая, сложная, ”мно­гоэтажная“– записывается в одну строку; такие символы как корень квадратный, произ­ве­дение, суммирование, заменяются соответствующими стандартными функциями. С набором стандартных функций, предоставляемых Мастером функций, можно ознакомиться в его окне, которое вызывается кнопкой Вставка функции.

Ввод и копирование формул. Ввод любой формулы в ячейке должен начинаться с ввода знака равенства и – в строке формулы отображается формула, а в ячейке после завершения набора и нажатия клавиши <Enter> – результат ее вычисления. При помощи формул можно прис­ва­ивать содержимому выделенной ячейки числовое или тексто­вое значение. Например, формула =" Фамилия" & " " & "Имя" запишет в ячейку " Фамилия Имя ", выражение =5+10 вернёт 15. Но 5+10 без знака равенства воспримется системой как текст.

Если одна и та же формула используется для данных в последовательных строках или столб­цах, её набирают один раз (обычно в 1-й строке), а затем копируют при помощи маркера заполне­ния или командами Копировать, Вставить. По умолчанию при копировании в смежные ячейки адреса в формуле автоматически меня­ются, чтобы одна и та же форму­ла приме­нялась к различным данным. Например, при расчёте подоходного налога в таблице формула =С4*13%, записанная в ячейку D4, будет изменяться при копировании её в соседние строки - С4 заменится на С5, С6, С7.
Такой адрес-ссылка называется относительным (изменяющим­ся при копировании).

Если адрес-ссылка меняться не должен (если 13% поместить в какую-либо отдельную ячейку, например, в С9), то этот адрес появится в аналогичной формуле (вместо константы 13% или 0,13) =С4*С9 и должен быть защищён от изменения. Это достигается вводом знака $ перед нужной коор­динатой, т.е. формула в D4 записывается как =С4*С$9 (такой адрес называется смешанным, при его копировании закрепляется только номер строки, а имя столбца не меняется) или =С4*$С$9 (это абсолютный адрес - в нём защищены от изменения обе координаты; вставка сразу двух дополнительных символов доллара выполняется клавишей <F4> сразу после набора адреса).

Символы основных операций можно разделить на группы и представить их в следующем виде:

арифметические: + - * / % ^(cложение, вычитание, умножение, деление, процент и возв.в степень),

сравнения: = < > <= >= <> (не равно), текстовые: &- конкатенация (соединение) символов,

логические: И ИЛИ НЕ - логическое умножение, сложение, отрицание.

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

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

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

Имя рабочего листа можно использовать в качестве ссылки в формулах, т.е. аргументы,
ука­зан­ные в формуле, могут на­хо­дить­ся на разных листах. Правомерна формула = A1*Лист4! С3,
вычисляющая произведение двух чисел: из ячейки A1текущего листа и из С3 рабочего листа 4.

Excel позволяет применять формулу к массивам данных (точнее, к наборам данных одинаковой структуры и однотипной информации). В качестве аргументов можно брать целую строку, стол­бец или блок. Если структура аргументов одинакова, то результатом будет такой же массив, каждый элемент которого - результат применения формулы к каждой паре соответствующих элементов из массивов аргументов. Для реализации такой операции нужно выделить соответст­вующий интервал пустых ячеек, задать формулу, например, =А1:В5 / С1:D5 и - <Ctrl-Shift-Enter> (эта комбинация используется для группового ввода данных в ячейки таблицы вместо <Enter>).

Просмотр таблиц. Предварительный просмотр (<Ctrl-F2>) выдает на экран документ, подго­товленный к печати. Выход из этого режима - кнопка Закрыть. Команда (Вид-Масштаб) изменяет масштаб таблицы. Используя Разделители рабочего листа, можно установить их так, чтобы они проходили через одну активную клетку, в которой находится курсор (чуть левее и чуть выше). Тогда во всех окнах будет находиться один и тот же рабочий лист с независимым упра­в­ле­нием окнами, что позво­лит просматривать одновременно разные части одного документа, часто удаленные друг от друга. Можно также применять команды Вид - Окно-Разделить/Снятьразделение.

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

Сохранение табличных данных.Выполняется при помощи команды Off-Сохранить как. При этом в верхнее поле диалогового окна вводится адрес хранения файла (имя диска и имя папки), а в поле имени файла любое имя. Расши­ре­ние.xls добавится автоматически.

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

- для работы в этой же системе файл получит расширение - .xlsx или.xlcx (для таблицы или диаграммы);

- для сохранения таблицы в виде шаблона, используемого при создании других таблиц, c расширением .xltx;

- Text или CSV - кодирует файлы в кодах ASCII; в качестве разделителей - табуляторы или запятая;

- DBF - для экспорта данных в системы управления базами данных dBase и др.

Выход из системы Excel реализуется щелчком кнопке в правом верхнем углу окна или двойным щелчком на значке программы в верхнем левом углу окна Excel или клавишами <Alt-F4>.

Оформление таблицы

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

Форматы данных. Список категорий всех форматов можно вывести на экран, если выделить ячейку с данными и задать команду Главная-Ячейки-Формат-Формат ячейки. В диалоговом окне на вкладках (их 6) представлены все категории форматов для всех типов данных, используемых при работе с электронными таб­ли­цами. Вкладка Число предлагает различные варианты представления числовых данных. Формат Общий установлен по умолча­нию - число отображается так, как вводится. Формат Числовой самый применяемый: по умолчанию тысячи разделены пробелом, после запятой два знака. Денежный - как Числовой, но имеет размерность рубля - р. Этот формат не дает выравни­вания чисел по десятичной запятой. Финансовый - имеет выравнивание, в остальном похож на Денежный. Экспоненциальный формат служит для изображения очень больших или очень мален­ьких чисел, например, число 0,0000067в этом формате будет представлено в виде мантиссы и порядка как 6,7 E -06, где Е - основание системы счисления (в цифровом выражении это число 10 для любой системы счисления).

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

Линии сетки устанавливаются и убираются командой Вид-Показать/Скрыть-Сетка.

Очень удобно при редактировании применять команды контекстного меню, которое вызывается правой кнопкой мыши и предоставляет такие операции, как очистить (удалить данные из выделенной части таблицы; то же самое выполняет клавиша <Del>), удалить (удаляет данные вместе с ячейками со сдвигом остальной информации), добавить ячейки (для добавления одного столбца левее выделенного; если выделить два столбца, добавится два; так же добавляются и строки). Выделенный фрагмент таблицы можно обрамить, выделить цветом, покрыть узором, исполь­зовав команду Формат-Ячейки-Границы / Вид. Автоформат выдает для оформления таблицы множество готовых вари­антов, из которых можно выбрать подходящий при помощи окна просмотра. Двойной щелчок на имени варианта закроет окно и оформит таблицу.

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

Сортировка предварительно выделенных данных выполняется по строкам/столбцам командами из подменю кнопки Сортировка и Фильтр или из группы команд вкладки Данные-Сортировка и фильтрация. Если требуется многоуровневая сортировка по двум или трём критериям, то выбирают
Настраиваемую сортировку.

Построение диаграмм. Excel строит диаграммы различных типов - круговые, гистограммы (столбиковые), объёмные, графики - стандартные и нестандартные. Очень важно для имеющихся данных правильно выбрать тип диаграммы. Сначала выделяются данные, необходимые для построения диаграммы, затем на вкладке Вставка в группе Диаграммы выбирается тип и вариант диаг­рам­мы, расположение числовых данных - по строкам или по столбцам; вводится или нет заголовок диаграммы, легенда-поясне­ние. Созданная диаграмма отобразится в рабочем листе, после чего станет доступна вкладка Работа с диаграммами с тремя наборами инструментов:
Конструктор, Макет и Формат.

Функции и вычисления

(логические, табличные, матричные функции, прогнозирование)

Excel имеет более 500 встроенных функций. Любую из них можно вводить с клавиатуры непосредственно в строке формул окна Excel или используя Мастер функций (Вставка-Функция или
значок f_x). Функция в Excel - это имя функции и аргументы в круглых скобках, которые разде­ляются точкой с запятой. Аргументами в функциях могут быть константы и адреса переменных (ссылки).

Функция СУММ (А1; В5; С10) выдаст сумму чисел, заданных в аргу­ментах-ссылках; МИН(С2: Е4) на­йдёт минимальное среди чисел указан­ного в аргументе диапазона ячеек. В качестве аргументов могут применяться другие (вложенные) функ­ции; например, СУММ (МИН (C2:E4)); A1; B5; C10).

Все функции разделены на группы. Самую большую группу составляют вычислительные функции Они также разделены на подгруппы. К ним относятся математи­че­ские, тригонометрические, статисти­ческие и др. Функции текстовой группы выполняют преоб­ра­зо­вания чисел в ASCII-коды (Американский стандартный код обмена информацией) и обратно, определяют длину данных, выделяют из строк подстроки, объединяют подстроки, удаляют пробелы. Информационные функции определяют состояние яче­ек, выдают типы ошибок. Финан­совые обрабатывают платежи, инвестиции, процентные ставки. Подробная информация обо всех функциях есть в Справке диалоговых окон Мастера функций. Там же можно получить информацию по син­таксису выбранной функции и прототипам ее аргу­мен­тов. Мастер функций приме­няют также при наборе сложных формул с большим числом аргументов;онупро­щает на­бор функций, так как разбивает эту операцию на отдель­ные шаги, выдает подсказки, отобра­жает ре­зультат каждого шага на экране и берет на себя часть работы, добавляя в формулу поля для ввода аргументов, скобки, точки с запятой. Его окно можно перемещать.

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

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

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

	C	D	E	F

	C	D	E	F

Логическая функция ЕСЛИ имеет 3 аргумента и позволяет по условию выбирать раз­ные решения. Её синтаксис: ЕСЛИ (лог_выраж; знач.1; знач2).
Семантику (смысл, действие) данной функции можно пояснить следующим образом:

если лог.выраж. (1-й аргумент) - истина,
то результат - 2-й аргумент, иначе - 3-й..

Любое логическое выражение (“высказывание”)
может иметь одно из двух значений: TRUE (истина) или FALSE (ложь).

В качестве логических выражений используются:
- логические отношения - два арифметич. выражения, соединенные символом операции отношения, например, x > k-1; это простое логическое выражение;

- логические одночлены - два или более логич.отношений, соединенные логической опера­цией "И":
x > 10 И x < 15("х лежит между 10 и 15" ); эту операцию называют также логическим умножением; в Еxcel эти выражения записывают по-другому в префиксной форме - символ логической операции находится перед аргумента­ми): И (x > 10; x < 15);

- логические многочлены - два или более логических одночлена, соединенные операцией "ИЛИ"
(логическое сложение): ИЛИ (x=2; y=2; z=2) - т.е. "хотя бы одна из трех переменных - x, y, z - равна 2".Пример использования функции ЕСЛИ в одном из вариантов расчёта подоход­ного налога:
=ЕСЛИ (C4>100000; 20%*C4; 12%*С4); здесь функция ЕСЛИ выдаёт два возмож­ных ре­зультата в зависимости от оклада. Если в качестве 3-го аргумента функции ЕСЛИ использо­вать эту же - вложенную - функ­цию, то можно получить 3 решения := ЕСЛИ (Х>10; формула1; ЕСЛИ (X<5; формула2; форм.3)).

Функция ЕСЛИ неявно применяется в функциях СУММЕСЛИ и СЧЁТЕСЛИ, вычисляющих сумму и количество тех значений, которые удовлетворяют заданному условию.

 

Работа со списками

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

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

Операции со списками. Просмотр БД - листание записей списка - выполняют при помощи линейки прокрутки. Очистка поля - < Del > на клавиатуре; удаление целой записи - Delete (Удалить). Поиск записей и их сортировка реализуются при помощи группы команд – Главная - Редактирование (Найти и Выделить и Найти и заменить) Последние две во мноом похожи между собой, имеют диалоговые окна, в которых исползуются одинаковые параметры настройки.

Например, если в поле ФИО задать Н*, будут выданы все фа­ми­лии, начинающиеся с буквы Н; для соотно­шения <=300, выбираются строки-записи со значениями переменной этого поля не более 300. Для выборки данных используют фильтр (Данные -кн. Сортировка и Фильтр или группа команд Сортировка и фильтрация) начинают с установки курсора на ключевое поле. Режим Автофильтр позволяет создать три типа фильтров: по значениям списка, по форматам и по условиям. Для установки автофильтра помещают в таблице и – Данные-Сортировка и фильтр - Фильтр и в строке заголовков столбцов появляются кнопки со стрелками, при нажатии на котрые можно установить нужное значение.

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

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

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

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

Данные - Итоги - При каждом изменении в - Наименование - Использовать функцию: - Сумма - Добавить итоги по: - Колич - Стоим - Заменить текущие итоги - Итоги под данными - OK.

Промежуточные итоги

Наимен	Колич	Цена	Стоим
молоко		3,5	10,5
молоко
молоко Всего			58,5
овощи		3,5	17,5
овощи
овощи Всего			49,5
хлеб
хлеб		2,5	2,5
хлеб Всего			6,5
Общий итог			114,5

Упорядоч. исходная таблица

Наимен	Колич	Цена	Стоим
молоко		3,5	10,5
молоко
овощи		3,5	17,5
овощи
хлеб
хлеб		2,5	2,5

Можно понизить уровень детали­­за­ции списка. При выделении яч-ки с про­межут. итогом левее вертикал. линейки появится кнопка со знаком " - " для удаления с экрана данного итога. Кнопка "+" повышает уровень детализации списка.

Удаление промежуточных итогов выполняется при помощи команды Данные-Промежу­точные итоги-Удалить все итоги

Наимен	Колич	Цена	Стоим
молоко Всего			58,5
овощи Всего			49,5
хлеб Всего			6,5
Общий итог			114,5

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

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

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

	С к л а д 1
ТОВАР	ОТКУДА	ДАТА	СТОИМ
Книга	Киев	мар	55,0
Видео	С.-П.	янв	425,0
Книга	Киев	фев	16,5
Аудио	Москва	мар	148,0
Видео	С.-П.	апр	520,0
Аудио	С.-П.	май	623,0
Книга	Москва	июн	58,0
Аудио	Москва	янв	132,7
Видео	Москва	фев	455,0
Видео	Москва	апр	400,0

Пример 1. Консолидация данных двух таблиц
(Склад 1 и Склад 2) - три варианта

 

	С к л а д 2
ТОВАР	ОТКУДА	ДАТА	СТОИМ
Видео	Киев	мар	781,0
Книга	С.-П.	апр	59,0
Книга	Москва	май	122,0
Видео	Киев	июн	477,0
Аудио	Москва	мар	356,0
Аудио	Москва	апр	533,0
Видео	С.-П.	мар	699,0

 

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

Консолидация по 3, 4 полю Дата Стоим янв 557,7 фев 471,5 мар 2039,0 апр 1512,0 май 745,0 июн 535,0

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

Консолидация по всем полям

Консолидация по 2,3,4 полю

двух таблиц

Товар	Откуда	Дата	Стоим
Видео			3757,0
Книга			310,5
Аудио			1792,7

Откуда	Дата	Стоим
Киев		1329,5
С.-П.		2326,0
Москва		2204,7

 

Сводные таблицы (Вставка-Сводные таблицы).

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

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

Создание СТ-1 в данном примере свелось к группировке данных по полю Наим и суммированию данных по полям Колич и Стоим, для чего курсор был установлен в начало создавае­мой таблицы, а затем выполнены следующие действия: Данные - Сводные таблицы - Далее >-выделение исходной таблицы- Далее >-появляется третье окно Мастера сводных таблиц, в котором справа представлены все поля исходной таб­лицы, а в центре - макет сводной таблицы. На этом шаге поле Наим перемещается в область Строки СТ (чтобы каждый новый элемент из группы этого поля стал заголов­ком строки в СТ), а поле Колич в раздел Данные (все элементы этого столбца, относящиеся к одному наименованию данной строки, будут суммироваться) и - Далее >. В четвертом окне Мастера выбираются необходимые опции - Итоги по строкам и Автоформат; здесь же задается адрес начальной ячейки, если она не была определена установкой курсора в начале этой операции (если она никак не задана, то СТ появится на отдельном листе рабочей книги). Завершается процесс создания сводной таблицы кнопкой Закончить (Готово). Таблица СТ-2 построена аналогично.

Пример 2. Формирование потребительской корзины

Наим	Колич	Цена	Стоим	Дата
сыр				01.03.08
хлеб				08.03.08
мясо	2,5		32,5	08.03.08
молоко		3,5		06.03.08
мясо	0,4		9,2	12.03.08
хлеб				12.03.08
молоко		3,5	10,5	16.03.08
сыр				16.03.08
хлеб				23.03.08
сыр				23.03.08
СТ-1	С в о д н ы е т а б л и ц ы	СТ-2
Сумма по полю Колич		Сумма по полю Стоим
Наим	Всего		Наим	Всего
молоко			молоко	17,5
мясо	2,9		мясо	41,7
сыр			сыр
хлеб			хлеб
Общий итог	20,9		Общий итог	135,2

 

 

Пример 3 П Л А Н С О З Д А Н И Я К Н И Г И С в о д н ы е т а б л и ц ы

Сумма по полю Стр. А в т о р   Глава Иванов Котов Петров Итог                                         Общий итог           Глава	Раздел	Автор	Страниц	Знаков	Дата
	1.1.	Иванов			05.07.04
	1.2.	Иванов			17.09.04
	2.1.	Иванов			30.11.04
	2.2.	Петров			21.06.04
	2.3.	Котов			11.09.04
	2.4.	Котов			01.02.05
	3.1.	Петров			13.09.04
	3.2.	Котов			13.03.05
	3.3.	Петров			27.11.04
Здесь поле Глава размещено в области строки, а поле Автор в области столбца, оба поля использованы для группировки данных. А в область Данные перемещены значения количества страниц (для 1-й СТ) и знаков (для 2-й), к которым по умолчанию применяется операция суммирования.   4	4.1.	Иванов			15.04.05

 

Сумма по полю Знаков	А в т о р
	Глава	Иванов	Котов	Петров	Итог
	Общий итог

Пример 4 ПОСТУПЛЕНИЕ ТОВАРОВ НА СКЛАД С в о д н ы е т а б л и ц ы

ОТКУДА	Киев	¯
Сумма по полю СТОИМ	ДАТА
ТОВАР	фев	мар	май	июн	Общий итог
Видео
Книга	144,5				199,5
Общий итог	144,5				1882,5

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

ОТКУДА

С.-П.

¯

Сумма по полю СТОИМ

ДАТА

ТОВАР

янв

фев

мар

май

июн

Общий итог

Аудио

Видео

Книга

Общий итог

 

2. ЛАБОРАТОРНАЯ РАБОТА
Создание и редактирование таблиц. Функции и вычисления в Excel. Графические возможности Excel. Работа со списками в среде Excel.

ЦЕЛЬ РАБОТЫ

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

Освоение технологии работы со списками. Анализ возможностей поиска и фильтрации данных в среде Excel.

ПЛАН РАБОТЫ

1. Освоение процедуры ввода данных в ячейки,.

2. Знакомство с правилами составления формул. Использование Мастера функций для ввода функций в формулы.

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

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

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

6. Сортировка данных. Знакомство с многоуровневой сортировкой.

7. Подведение итогов в списках и базах данных.

8. Знакомство с правилами составления критериев отбора данных. Работа с Автофильтром.

9. Изучение возможностей анализа данных с использованием сводных таблиц. Освоение технологии формирования сводных таблиц. Анализ данных и подведение итогов с помощью сводных таблиц.

 

 

ПРАКТИЧЕСКОЕ ЗАДАНИЕ