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

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

1.1.1.   Назначение электронных таблиц

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

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

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

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

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

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

Наиболее популярными текстовыми процессорами для ПК в свое время являлись SuperCalc, Quattro Pro (фирмы WordPerfect), Lotus-1-2-3  (фирмы Lotus). В России в настоящее время лидирующие позиции занимает текстовый процессор Excel фирмы Microsoft, входящий в пакет программ Microsoft Office.

Возможности табличных процессоров:

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

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

·        оформление таблицы в удобном для пользователя виде;

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

·        создание многотабличных документов, объединенных формулами;

·        представление информации в графическом виде, т.е. автоматическое построение диаграмм, их модификация;

·        сортировка таблиц;

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

·        создание итоговых и сводных таблиц;

·        статистическая обработка информации;

·        решение оптимизационных задач;

·        хранение ЭТ на дисках для многократного использования;

·        разработка макрокоманд, настройка среды под потребности пользователя и т.д.

1.1.2.   Обработка данных в Excel

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

Рис. 7. Окно табличного процессора Excel

Заголовки столбцов - содержат буквы латинского алфавита, обозначающие столбцы ЭТ (A,B,C,...,AA,AB,...,IV), всего 256 столбцов.

Заголовки строк - расположены в первом столбце и идентифицируют строки арабскими цифрами.

Ячейка - место пересечения столбца и строки. Каждая ячейка имеет свой адрес, состоящий из имени столбца и строки, например, А1 (но не 1А), Е89, АС23. Выделенную ячейку называют активной или текущей.

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

Блоки ячеек - это прямоугольная группа смежных ячеек, имена ячеек в блоках разделяются двоеточием. Поэтому адрес блока - это адреса любых противоположных угловых ячеек блока, разделенных двоеточием, например, A1:A6, А1:С8, А1:Е1.

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

Рабочая книга - основное рабочее пространство ЭТ. Рабочая книга состоит из нескольких листов. По умолчанию книга открывается с рабочими листами - Лист1, Лист2 и т.д., число которых можно увеличить или уменьшить (командой Сервис/Параметры/ вкладка Общие, поле Листов в новой книге).

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

Рабочие листы можно вставлять (команда Вставка/Лист), удалять (команда Правка/Удалить лист), переименовывать (команда Формат/Лист/Переименовать), перемещать и копировать (команда Правка/Переместить/скопировать лист…). Эти же команды можно выполнять, используя контекстное меню листов книги.

1.1.2.1. Создание, сохранение и чтение рабочих книг

При запуске Excel автоматически создается новая рабочая книга – Книга1.

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

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

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

Сохранить рабочую книгу под новым именем нужно командой Файл/Сохранить как.

По умолчанию Excel автоматически устанавливает стандартное расширение имени файла - .xls.

1.1.2.2. Ввод и редактирование данных

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

-           нажатием клавиши <Enter> или <Tab>;

-           нажатием клавиши управления курсором;

-           щелчком левой кнопки мыши по другой ячейке.

В процессе ввода данных MS Exсel автоматически распознает, что вводится - числа, текст или формулы.

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

Константы можно разделить на три основные категории:

-           числовые значения;

-           текстовые значения;

-           значения дат и времени;

и специальные типы констант:

-           логические значения;

-           ошибочные значения.

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

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

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

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

Числовой формат определяет внешнее представление числа в ячейке. Изменить числовой формат можно командой Формат/Ячейки (вкладка Число).

Текст. Текст может содержать практически любые символы.

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

Чтобы показать весь длинный текст в ячейке, можно расширить столбец или вывести этот текст в несколько строк в одной ячейке, используя команду Формат/Ячейки/Выравнивание/ флажок Переносить по словам.

Чтобы ввести как текст числовое выражение, нужно использовать апостороф ('). Например, номер телефона '24-24-00.

Даты. При вводе дат число, месяц и год отделяются знаком слэш (/), или дефис (-), или точкой (.). Например, 1.1.99 или 1/1/99 или 1-1-99.

При необходимости формат представления даты можно изменить командой Формат/Ячейки/Число.

Независимо от формата, используемого для представления дата, в Excel все даты сохраняются в памяти как последовательные числа. Система отсчета дат в Excel - 1.01.1900. Это первый день, т.е. запоминается как число 1. Благодаря этому, даты можно складывать, вычитать и др. Например, записав формулу                 =5.1.2004-1.1.2004 получим результат 4.

Формулы. Все формулы в Excel должны начинаться со знака =. При вводе формулы в ячейке электронной таблицы отображается значение, вычисленное по этой формуле, а в строке формул – сама формула. Приоритет арифметических операций обычный (по возрастанию): (+) - сложение, (-) - вычитание, (*) - умножение, (/) - деление, (^) - возведение в степень. Для изменения приоритета операций используют круглые скобки.

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

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

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

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

Абсолютные ссылки (адреса) при копировании формул в другие ячейки остаются неизменными. Для того, чтобы адрес сделать абсолютным, нужно записать его, используя символ $. Например, $B$3.

Смешанные ссылки (адреса) могут иметь часть адреса неизменной. Например, $В3 (не меняется столбец) или В$3 (не меняется номер строки).

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

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

Чтобы рассчитать долю дохода за январь введем в ячейку D4 формулу =С4/С7. Если эту формулу скопируем в ячейки D5:D6, то получим следующий результат:

Почему получился такой результат? Проанализируем формулы, полученные после копирования:

В данном примере необходимо чтобы адрес в знаменателе не менялся. Для того чтобы знаменатель при копировании формулы не менялся, используем при записи формулы смешанный адрес C$7 (т.к. копирование формулы производится вниз – на новые строки, то достаточно зафиксировать в адресе номер строки).

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

Рис. 8. Диалоговое окно команды Сервис/Параметры

Теперь формулы после копирования примут вид:

Результат вычислений:

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

Для нашего примера выполним еще один вариант: введем формулу с использованием имени блока ячеек. Выделим ячейку С7 (одна ячейка – частный случай блока ячеек) и присвоим ей имя Итого_за_квартал. При вводе формулы в ячейку D4 после выделения ячейки С7 ее адрес автоматически заменится на имя.

Для ввода в ячейки последовательности  данных:

-         можно воспользоваться инструментом Автозаполнения, протаскивая мышью маркер заполнения, находящийся на рамке ячейки;

-         с помощью команды Правка/Заполнить /Прогрессия можно быстро создать ряд чисел или дат.

Редактировать данные можно двумя способами:

-         выделить ячейку и редактировать данные в строке формул или прямо в ячейке;

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

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

Для удаления ячеек, столбцов или строк выполняют команду Правка/Удаление.

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

При копировании ячеек они выделяются, затем сначала выполняется команда Правка/Копировать, затем указывается ячейка, которая станет верхним левым углом скопированного диапазона, и выполняется команда Правка/Вставить.

1.1.2.3. Функции

Очень часто в вычислениях при записи формул используют функции, которые оперируют с одним или несколькими значениями - аргументами. Аргументы при записи функций отделяются точкой с запятой. Excel содержит более 400 так называемых встроенных функций. Каждая функция имеет имя и аргументы, заключенные в круглых скобках и отделяемые друг от друга точкой с запятой. Самый простой и удобный способ использования встроенных функций в Excel – использование Мастера функций. В Мастере функций все функции разделены на 10 категорий, например, математические, статистические, логические и т.д. Мастер функций можно вызвать, нажав кнопку  на панели инструментов, или выполнив команду Вставка/Функция.

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

Рис. 9. Выбор категории функции

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

Рис. 10. Окно ввода аргументов функции

Примечание! Если в качестве аргумента функции используется также функция, то внутреннюю (вложенную) функцию следует выбирать с помощью списка функций, который находится слева от строки формул. После ввода аргументов внутренней функции не щелкайте кнопку Ok (Готово), т.к. это досрочно завершит ввод формулы. Вместо этого установите курсор в строке формул на имени внешней функции. Тогда окно Мастера функций внутренней функции заменится на окно Мастера функций внешней функции. Например, в ячейку D7 требуется ввести формулу =ОКРУГЛ(СУММ(D4:D6);-1). Выбор функции СУММ показан на рис.11.

Рис. 11. Ввод вложенной функции

Рассмотрим одну из самых часто используемых функций – математическую функцию СУММ. Эта функция может иметь до 30 аргументов, причем, каждый аргумент может быть числом, адресом ячейки или блока ячеек, содержащих число или формулу, возвращающую числовое значение. Например, функция СУММ(А2; В2:К2; 500) имеет три аргумента (первый аргумент – адрес ячейки A2, второй – адрес блока ячеек B2:K2, третий – числовая константа 500). Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения. Т.к. СУММ является очень часто используемо функцией, то на панели инструментов для ввода этой функции есть специальная кнопка – Автосуммирование (å).

Хотелось бы обратить внимание на несколько математических функций:

ОКРУГЛ – округляет число до указанного количества десятичных знаков;

ОТБР – отбрасывает дробную часть числа;

ЦЕЛОЕ – округляет число до ближайшего меньшего целого.

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

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

Таблица 1

Математические функции

Таблица 2

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

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

Приведем два варианта расчета зарплаты.

Вариант 1. Зарплата, начисленная одному члену бригады, вычисляется как общий фонд зарплаты бригады, деленный на сумму индивидуальных коэффициентов и умноженный на индивидуальный коэффициент этого работника. Чтобы при копировании формулы из ячейки С5 в ячейки С6:С9 адрес ячейки В2 (фонд зарплаты) и адрес блока ячеек В5:В9 (сумма индивидуальных коэффициентов) не менялись, достаточно в этих адресах зафиксировать номер строки.

Рис. 12. Использование смешанных ссылок (адресов)

Примечание. В данной задаче можно использовать абсолютные адреса $B$2 и СУММ($B$5:$B$9), результат от этого не изменится.

Рис. 13. Результат вычисления формул

Вариант 2. Добавим в условие задачи требование округлить суммы в графе «Начислено» до двух знаков после запятой. В этом варианте в ячейке В10 подсчитаем сумму индивидуальных коэффициентов и присвоим этой ячейке имя ВсегоИндКоэф. Напоминаем, что имя блока ячеек заменяет собой абсолютную адресацию, поэтому формулы приобретут следующий вид:

Рис. 14. Использование функции округления

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

Рис. 15. Использование арифметических и статистических функций

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

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

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

Простое условие (простое логическое выражение) представляет собой отношение вида: А*В, где

* – одна из операций отношения (=, < >, >, >=, <, <=),

А и В – сравниваемые значения, которые могут быть числами, формулами, текстовыми или логическими значениями.

Результатом логического выражения является логическое значение «истина» (1) или «ложь» (0). Например, результатом логического выражения 15>7 является «истина», а результат логического выражения F5>7 зависит от содержимого ячейки F5.

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

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

Функция И. Эта функция имеет следующий синтаксис:

=И (логическое_выражение1; логическое_выражени2;…)

Функция И возвращает логическое значение ИСТИНА, если только все аргументы (логические значения) имеют значение ИСТИНА, т.е. если все логические выражения  - истинны (т.е. если все условия выполняются).

Пример 1. Условие – «сданы ли все предметы только на 5»  (оценки записаны в ячейках B4;C4;D4), можно записать:

=И(B4=5;C4=5;D4=5)

Если в ячейку Е4 записать формулу, с использованием этой логической функции, то в ячейке Е4 получим результат ИСТИНА или ЛОЖЬ:

Пример 2. Мы имеем три ячейки А1, В1 и С1. Составить условие, которое позволит определить, является ли значение ячейки А1 больше остальных. Чтобы значение ячейки А1 было наибольшим, необходимо чтобы оно было больше значения ячейки В1 и больше значения ячейки С1. То есть нужно проверить условие:

=И(А1>В1; A1>C1)

Функция ИЛИ. Эта функция имеет следующий синтаксис:

=ИЛИ (логическое_выражение1; логическое_выражение2;…)

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

Пример 3. Условие «если хотя бы одна оценка ниже 4» (оценки записаны в ячейках B4;C4;D4) можно записать:

=ИЛИ(B4<4;C4<4;D4<4)

Пример 4. Выяснить, есть ли среди ячеек А1, В1, С1 ячейки с отрицательным результатом. То есть нам требуется выяснить, есть ли хотя бы в одной ячейке отрицательное значение. Для этого запишем в ячейке Е1 логическое выражение: =ИЛИ(А1<0;B1<0;C1<0):

Функция НЕ. Эта функция имеет следующий синтаксис:

=НЕ (логическое_значение)

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

Пример 5. Логическое выражение =НЕ (7<10) имеет значение ЛОЖЬ.

Пример 6. Выражение =НЕ(F1>=13) имеет значение ИСТИНА, если F1<13:

Для записи результата после анализа значения логического выражения (т.е. после анализа выполнения условия) используется логическая функция ЕСЛИ.

Функция ЕСЛИ. Эта функция имеет следующий синтаксис:

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

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

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

Пример 7. Записать результат вступительных экзаменов абитуриента. Если абитуриент набрал 13 баллов и больше (набранный балл введен в ячейку С2), то он зачислен в институт.

Пример 8. Записать в ячейку В7 формулу, которая анализирует значение суммы ячеек В1:В6 –  если значение суммы положительное, то в ячейку В7 записывается значение этой суммы, иначе записывается 0:

Пример 9. Студенту начисляется стипендия, если все экзамены в сессии сданы на 4 и 5. Записать в ячейку F3 информацию – начислена ли стипендия студенту, который сдал 3 экзамена; результаты экзаменов введены в ячейки С3, D3, E3. В ячейку F3 вводим формулу:

Вложенная функция ЕСЛИ. Иногда после проверки одного какого-то условия требуется проверка дополнительных условий. В этом случае используют вложенные функции ЕСЛИ (можно использовать до 7 уровней вложения функции ЕСЛИ, но необходимо помнить, что максимальная длина записи в ячейке – 255 символов). Вложенная функция ЕСЛИ в качестве одного из аргументов – значение_если_истина или значение_если_ложь использует опять же функцию ЕСЛИ.

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

Пример 10. Вычислить значение функции Y в зависимости от значения аргумента X:

Значения Х будем вводить, например, в ячейку А2, а значение Y получим в ячейке В2. Тогда в В2 запишем формулу:

Пример 11. Предположим, что банковский процент зависит от величины вклада следующим образом: для вкладов размером до 10 тысяч рублей процент составляет 7%, для вкладов свыше 10 тысяч и до 30 тысяч – 8%, а свыше 30 тысяч – 9%. Записать выражение для вычисления размера вклада, записанного в ячейке А3, по истечении года хранения вклада. Формулу для вычисления размера вклада запишем в ячейку В3.

Мы намеренно усложнили формулу, чтобы еще раз показать использование функции И, в данном примере можно ввести такую формулу:

=А3*ЕСЛИ(А3<=10000;107%;ЕСЛИ(А3>30000;109%;108%)).

1.1.3.   Виды экономической информации. Справочные таблицы

Относительно процесса обработки различают следующие виды экономической информации:

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

Нормативно-справочная (или условно-постоянная) информация – информация, которая остается неизменной в течение длительного периода времени и используется многократно для решения одной или нескольких задач. Изменение справочной информации осуществляется по мере необходимости.

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

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

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

•         Справочник в ЭТ должен содержать не менее двух строк и столбцов.

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

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

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

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

•         Записи могут сортироваться по нескольким полям. При этом сначала записи сортируются по первому полю, затем записи, имеющие одинаковое значение ключа по этому полю, сортируются по второму указанному полю и т.д.

•         Записи справочника должны располагаться по возрастанию ключа. Поэтому при создании справочника его записи сортируют по возрастанию. (Это требование в последних версиях Excel не является обязательным).

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

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

ВПР(искомое значение; табл_массив; номер индекса столбца; диапазон просмотра)

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

табл_массив – таблица, в которой ищутся данные;

номер индекса столбца – номер столбца в табл_массиве, в котором должно быть найдено соответствующее значение. Первый столбец имеет номер 1;

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

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

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

Функция ВПР используется для поиска информации в таблицах-справочниках. Эта функция записывается в клетку выходного документа, куда следует поместить искомое данное из справочника.

Например, имеется таблица-справочник:

Для включения в выходную таблицу информации из справочника запишем в соответствующие ячейки формулы с функцией ВПР:

1.1.4.   Графические возможности Excel

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

Диаграмма состоит из графического образа и вспомогательных элементов.

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

По характеру графического образа различают: графики, гистограммы, круговые, точечные диаграммы и т.д.

Существуют два варианта размещения диаграмм:

-         внедренные диаграммы - хорошо выглядят в отчетах, когда надо, чтобы данные и диаграммы были представлены рядом;

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

В любом из этих вариантов диаграммы сохраняются вместе с книгой, в которой они находятся. Поэтому чтобы сохранить диаграмму, достаточно сохранить книгу командой Файл/Сохранить или Файл/Сохранить как.

При открытии книги командой Файл/Открыть открываются и все диаграммы этой книги.

1.1.4.1. Основные понятия деловой графики

Рис. 16. Основные элементы диаграммы

Каждая диаграмма может и должна иметь название.

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

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

Числовая ось - ось Y , ось значений. Метки располагаются на осях координат через равные ин-тервалы и помогают идентифицировать данные на диаграмме.

Названия осей - оси X и Y могут и должны иметь названия для лучшего понимания диаграмм.

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

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

Например, отображаем прибыль фирмы по каждому из регионов за 2000 и 2001 годы. Категориями являются регионы. 1 ряд данных - множество значений прибыли фирмы по всем регионам за 2000 г. 2 ряд данных - множество значений прибыли фирмы по всем регионам за 2001 г.

Маркер данных - это отметка на диаграмме конкретного значения данных. Все значения одного ряда данных изображаются на диаграмме одинаковыми маркерами.

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

1.1.4.2. Типы диаграмм

В EXCEL 97 Мастер диаграмм позволяет строить диаграммы 14 базовых типов. Причем каждый тип имеет несколько подтипов - видов. Пользователь, выбирая определенный тип и вид диаграммы, может получить вариант, наилучшим образом отображающий данные. Поэтому пользователю нужно хорошо ориентироваться в том, какие типы диаграмм предоставляет Excel. Подробно рассмотрим все виды только для одного типа диаграмм - График, чтобы показать, какие возможности предлагает один тип диаграмм.

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

Этот тип диаграммы имеет 7 видов.

1 й вид - каждый ряд данных представляется на диаграмме отдельной ломаной линией. Легенда указывает, какой тип линии используется для каждого ряда данных.

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

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

4-й, 5-й, 6-й вид аналогичны 1-ому, 2-ому и 3-ему, но с выводом маркеров данных на линиях графиков.

7-й вид представляет собой объемный вариант графика, где каждый ряд изображается не ломаной линией, а ломаной лентой.

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

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

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

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

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

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

Биржевая. Биржевая диаграмма может использоваться для слежения за ценой акций, отсюда и название этого типа диаграмм. Этот тип диаграмм имеет 4 вида. Остановимся на первом типе подробнее.

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

1.1.4.3. Построение и редактирование диаграммы

Для построения диаграммы в Excel используется Мастер диаграмм, вызвать который можно, нажав кнопку панели инструментов или использовав команду Вставка/Диаграмма. Мастер диаграмм строит диаграммы за 4 шага:

1 шаг - выбор типа и вида диаграммы;

Рис. 17. Шаг 1. Выбор типа и вида диаграммы

2 шаг - определение источника данных диаграммы (указание диапазона данных и размещения рядов данных);

Рис. 18. Шаг 2. Определение источника данных для построения диаграммы

3 шаг - задание параметров диаграммы (заголовков, легенды, подписей данных и т.д.);

Рис. 19. Шаг 3. Ввод параметров диаграммы

4 шаг - размещение диаграммы (на отдельном листе или на имеющемся).

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

1.1.4.4. Форматирование любого элемента диаграммы

1.     Двойной щелчок на любом элементе диаграммы вызывает соответствующее диалоговое окно форматирования.

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

3.     Щелкните на элементе диаграммы правой кнопкой мыши и выберите нужный пункт из контекстного меню.

Контрольные вопросы

1.     Что такое табличный процессор или электронная таблица?

2.     Как сохранить и открыть книгу ЭТ?

3.     Опишите структуру экрана табличного процессора.

4.     Перечислите возможности табличных процессоров.

5.     Что такое ячейка в ЭТ, блок ячеек?

6.     Объясните основные понятия - книга, лист ЭТ.

7.     Какие типы данных вы знаете?

8.     Что такое относительный и абсолютный адрес ячейки?

9.     Каковы особенности данных типа дата?

10. Что такое формула в ЭТ?

11. Как вводятся функции в формулы?

12. Какие типы диаграмм вы знаете?

13. Назовите основные элементы диаграмм.

14. Какие 4 шага нужно выполнить, чтобы построить диаграмму?

15. Объясните назначение логических функций И, ИЛИ, ЕСЛИ.

16. Назовите правила организации справочников в Excel.

17. Для чего предназначена функция ВПР?

Скачано с www.znanio.ru