Электронные таблицы

Лекция 8. Электронные таблицы

Обзор, назначение и основные функции

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

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

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

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

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

Появление электронных таблиц исторически совпадает с началом распространения персональных компьютеров. Первая программа для работы с электронными таблицами — табличный процессор, была создана в 1979 году, предназначалась для компьютеров типа Apple II и называлась VisiCalc. В 1982 году появляется знаменитый табличный процессор Lotus 1-2-3, предназначенный для IBM PC. Lotus объединял в себе вычислительные возможности электронных таблиц, деловую графику и функции реляционной СУБД. Популярность табличных процессоров росла очень быстро. Появлялись новые программные продукты этого класса: Multiplan, Quattro Pro, SuperCalc и другие.

Что же такое электронная таблица? Это средство информационных технологий, позволяющее решать целый комплекс задач:

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

2.                 Математическое моделирование. Использование математических формул в ЭТ позволяет представить взаимосвязь между различными параметрами некоторой реальной системы. Основное свойство ЭТ — мгновенный пересчет формул при изменении значений входящих в них операндов. Благодаря этому свойству, таблица представляет собой удобный инструмент для организации численного эксперимента:
— подбор параметров,
— прогноз поведения моделируемой системы,
— анализ зависимостей,
— планирование.
Дополнительные удобства для моделирования дает возможность графического представления данных (диаграммы);

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

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

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

Наиболее популярными табличными процессорами являются Microsoft Excel,  Lotus 1—2—3, Quattro Pro.

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

Lotus 1-2-3

Lotus 1-2-3 — профессиональный процессор электронных таблиц. Широкие графические возможности и удобный интерфейс пакета позволяют быстро ориентироваться в нём. С его помощью можно создать любой финансовый документ, отчёт для бухгалтерии, составить бюджет, а затем разместить все эти документы в базах данных. В Lotus 1-­2-­3 включены достаточно сильные средства анализа и обработки данных.

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

Широкий спектр заложенных функций расширяет область применения Lotus 1­-2-­3, делая его инструментом, ориентированным на любых пользователей персональных компьютеров.

Благодаря радикально обновленному интерфейсу и  новым возможностям версия Lotus 1-2-3 для Windows  производит весьма благоприятное впечатление и, возможно, превосходит по простоте использования все остальные электронные таблицы. В сфере решения сложных задач Lotus 1-2-3,  как и прежде, не выдерживает конкуренции с Excel, но фактически она уже обогнала Quattro Pro и вырвалась на второе место.

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

Quattro Pro

 Версия программы Corel Quattro Pro дополнена инструментами для подготовки запросов к Web, которые обеспечивают возможность предварительного просмотра информации, извлеченной из Интернет; такого удобства в Excel нет. Однако имеющиеся в программе средства для работы со сводными таблицами уступают в простоте применения Excel  и не позволяют вносить изменения в готовые таблицы. Настроив Quattro Pro надлежащим образом, можно использовать в качестве принятых по умолчанию файловые форматы Excel или Lotus 1-2-3, но при этом многие функции Quattro Pro становятся недоступными.

 Для реализации сложных функций электронных таблиц в Quattro Pro  включено удобное средство Formula Composer, которое позволяет представить уравнения в виде древовидной структуры, допускающей быструю коррекцию. Операции с данными же остаются менее удобными, чем в Microsoft Excel.

Основы работы в Excel

После запуска Excel на экране появляется окно, состоящее из нескольких элементов (рис 1):

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

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

Контекстные меню активизируются нажатием ПРАВОЙ кнопки мыши (или Shift + F10). Для выхода из контекстного меню нужно щелкнуть кнопкой мыши вне его или нажать Esc.

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

 

 

 

 

 

 

 

 

			Ярлычки рабочих листов
	Строка состояния
					Полосы прокрутки

 

 

 

 

 

 

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

Ø полосы прокрутки (вертикальной и горизонтальной), позволяющих просматривать  и работать с большими таблицами;

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

Ø  ярлычки рабочих листов, содержащих  стандартные имена (Лист1, Лист2, Лист3 и т.д), которые по желанию пользователя могут быть добавлены, удалены или переименованы под конкретную задачу.

Работа с электронными таблицами

В Excel можно работать с 4 основными типами документов:

·        электронная таблица;

·        диаграмма;

·        макротаблица;

·        рабочая книга.

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

Рабочий лист электронной книги состоит из ячеек, каждая из которых имеет свой адрес: сочетание имени столбца и строки. Столбцы идентифицируются буквами латинского алфавита (А. B, C, D…), а строки – арабскими цифрами (1,2,3…). Ячейка, в которой находится курсор, считается активной, то есть предназначенной для ввода данных.

Например, адрес F10 говорит о том, что вводимая информация при активной ячейке F10 попадет именно в эту ячейку, а содержимое  этой ячейки отразится в строке формул.

Многие команды Excel  позволяют работать с блоками ячеек. Блок ячеек - это прямоугольник, задаваемый координатами противоположных углов, обычно, верхней левой и нижней правой ячеек. Имена ячеек в блоках разделяются двоеточием  (“:“). Например, блок А1:В4 включает в себя ячейки А1, А2, А3, А4, В1, В2, В3 и В4. Выделение блока ячеек осуществляется протаскиванием курсора мыши на нужный диапазон. При этом ячейка, начиная с которой выделяется блок, остается белого цвета, а остальные ячейки  затемняются. Но блок ячеек не обязательно может включать смежные ячейки.

Ячейкам и блокам для удобства работы можно давать имена.

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

Типы данных, используемых в Excel

Excel  распознает два основных типа данных:

Ø текст, то есть последовательность символов (при вводе они автоматически выравниваются по левому краю ячейки);

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

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

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

Например, данными числового типа в Excel являются следующие выражения:

ü =46+55;

ü =200*В5 (если в ячейке В5 находится число );

ü =А7/В4 (если в ячейках A7 и B7 находятся числовые выражения).

Примечания:

§  Если формула начинается с адреса ячейки, то перед первой координатой необходимо ввести знак « =» или  «+»;

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

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

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

При вводе десятичных чисел используется запятая (,) в качестве десятичного разделителя.

При обработке формул Excel придерживается основных математических правил:

1.     В первую очередь вычисляются выражения внутри круглых скобок.

2.     Умножение и деление выполняются раньше сложения и вычитания.

3.     Операторы с одинаковым приоритетом выполняются слева направо.

4.     Для изменения порядка выполнения операторов используют круглые скобки.

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

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

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

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

Имя функции (аргумент1; аргумент2; аргумент3 и т.д.), где:

Имя функции – показывает в программе, о каких вычислениях идет речь. Примеры имен функции – СУММ, СРЗНАЧ и др.

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

Результат – значение, полученное при вычислении функции.

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

Функции могут быть выбраны из списка функций с помощью меню Вставка|Функция или с помощью нажатия пиктограммы f(_x) на стандартной панели инструментов с соответствующим выбором необходимой функции.

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

Функции в Excel подразделяются на следующие основные группы:

·        Математические, арифметические и тригонометрические функции.

·        Функции для работы с датами и временем.

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

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

·        Функции для работы со ссылками и массивами.

·        Функции для работы с базами данных.

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

·        Текстовые функции и др.

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

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

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

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

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

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

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

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

Диагностика ошибок в формулах Excel

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

Сообщения об ошибках в Excel могут принадлежать к одному из 8 типов:

·        ######

·        #ЗНАЧ!

·        #ДЕЛ/0!

·        #ИМЯ?

·        #Н/Д

·        #ССЫЛКА!

·        #ЧИСЛО!

·        #ПУСТО!

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

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

Ошибка #ДЕЛ/0 появляется, когда в формуле делается попытка деления на ноль.

Сообщение об ошибке типа #ИМЯ? появляется , когда Excel не может найти имя, используемое в формуле. Например, такая ситуация возникнет, если:

Þ       при наборе имени произошла опечатка.

Þ       текст ошибочно не был заключен в кавычки.

Þ       в ссылке на диапазон ячеек пропущен знак двоеточия (:).

 Ошибка #Н/Д является сокращением термина «Неопределенные Данные».

Ошибка #ССЫЛКА! генерируется, когда при ссылке на ячейку указывается недопустимый адрес.

Сообщение об ошибке вида #ЧИСЛО! возникает в том случае, когда формула использует некорректное число:

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

Ввод и обработка данных в Excel

Значительная часть работы в Excel приходится на ввод данных, их редактирование и обработку.

Общие принципы

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

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

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

Форматирование и защита рабочих листов

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

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

Форматирование в Excel включает в себя решение следующих вопросов:

·        изменение шрифта, размеров, начертания и цвета символов

·        выравнивание и изменение ориентации текста и чисел в ячейках

·        форматирование чисел, дат и времени

·        форматирование строк и столбцов

·        создание и использование пользовательских форматов

·        условное форматирование

·        защита ячеек, листов и рабочих книг

·        использование стилей при форматировании

·        применение автоформатов

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

 

 

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

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

§   Мастер диаграмм;

§   Панель Диаграммы.

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

Элементы диаграммы

Основные компоненты диаграммы представлены на следующей схеме:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Примечание: для объемной диаграммы составные части несколько отличаются.

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

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

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

§    график отражает тенденции изменения данных за определенные промежутки  времени;

§    круговые диаграммы предназначены для наглядного отображения соотношения частей и целого

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

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

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

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

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

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

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

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

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

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

Построение диаграмм при помощи Мастера диаграмм

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

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

Для вызова Мастера диаграмм используется:

Ø пункт меню Вставка|Диаграмма;

Ø кнопка Мастер диаграмм на стандартной панели инструментов

Вывод на экран панели Диаграммы осуществляется при помощи  пункта меню Вид|Панели инструментов|Диаграммы:

 

 

 

 

 

 

 

 

 

 

 

 

Мастер диаграмм предполагает несколько шагов, которые должен сделать пользователь.

Созданные диаграммы в любое время могут редактироваться. Excel позволяет:

§  Изменять размеры диаграммы

§  Перемещать диаграммы на рабочем листе

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

§  Добавлять новые и удалять ненужные данные и т.п

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

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

Добавление линии тренда к ряду данных

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

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

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

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

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

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

Сохранение таблиц

При сохранении таблицы вместе с этим сохраняются и установки, сделанные для этой книги, включая конфигурацию окна  и характеристики дисплея, формулы, функции, шрифты и стили. В меню Файл имеются пять команд, позволяющих сохранить файл EXCEL: Сохранить, Сохранить как, Сохранить в формате HTML, Сохранить рабочую область. Закрыть и Выход. Каждая из этих команд имеет свою специфику. Команда Сохранить как обычно используется при первом сохранении документа или для выбора способа его сохранения. А команда Сохранить применяется для сохранения изменений, сделанных в существующем документе. Сохранить в формате HTML сохраняет документ на Web-странице. Но и при выборе команд Выход или Закрыть, во избежание случайной потери выполненной работы, Excel всегда спрашивает, не хотите ли Вы сохранить свои изменения. Кроме того, нажав кнопку Сохранить на панели инструментов, вы можете быстро сохранить текущий документ точно так же, как с помощью команды Сохранить из меню Файл. После сохранения файла книги, с помощью одной из перечисленных выше команд сохранения, книга остается открытой. Файл удаляется с экрана только при закрытии книги.

Примечание: на Стандартной панели  инструментов есть кнопка СОХРАНИТЬ (значок дискеты), выполняющая действия, аналогичные команде СОХРАНИТЬ меню ФАЙЛ.

Создание пользовательских списков

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

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

Работа в Excel с базами данных (списками)

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

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

·        Работа с внешними данными с помощью специальных запросов.

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

Чтобы работать со списками, необходимо следовать следующим правилам:

Ø Каждый столбец должен содержать информацию одного типа.

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

Ø Список не должен включать пустые строки и столбцы.

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

Ø Не следует размещать данные слева и справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.

Над списками можно производить следующие действия:

Ø Сортировать список.

Ø Добавлять или изменять данные с помощью формы.

Ø Фильтровать список таким образом, чтобы были выделены только те строки, которые удовлетворяют заданному критерию.

Ø Автоматически вычислять промежуточные итоги.

Ø Создавать для списка структуру.

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

Ø Проверять вводимые в ячейку данные.

Все основные возможности работы со списками включены в пункт меню Данные.

Сортировка списков и диапазонов

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

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

Использование форм

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

Фильтрация списков

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

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

Применение Автофильтра

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

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

Применение расширенного фильтра

Команда Расширенный фильтр» позволяет:

Ø Задавать условия, соединенные логическим оператором И и ИЛИ для нескольких столбцов

Ø Задавать три или более условия для конкретного столбца с использованием, по крайней мере, одного логического оператора ИЛИ.

Ø Задавать вычисляемые условия

Ø Извлекать строки из списков. Вставлять копии этих строк в другую часть текущего листа

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

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

Ø Условия на одной строке считаются соединенными логическим оператором И.

Ø Условия на разных строках считаются соединенными логическим оператором ИЛИ.

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

Расширенный фильтр с использованием вычисляемых значений

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

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

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

Ø Ссылки на ячейки в таблице должны быть относительными.

Подведение промежуточных итогов

При работе со списками команда Данные|Итоги быстро создает промежуточные итоги по выбранным категориям. Эта команда выполняет две достаточно сложные задачи:

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

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

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

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

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

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

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

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

Проверка данных

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

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

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

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

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

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

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

Проведение анализа средствами Excel

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

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

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

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

Обычными задачами, решаемыми с помощью Поиска решения, являются:

·        Штатное расписание. Составление штатного расписания для достижения наилучших результатов при наименьших расходах.

·        Планирование перевозок. Минимизация затрат на транспортировку товаров.

·        Ассортимент продукции. Максимизация выпуска товаров при ограничениях на сырье для производства этих товаров.

 

 

Visual Basic for Applications (VBA)

 

Несомненно, с помощью Excel можно создавать сложные и мощные приложения, но существует средство, которое позволяет создавать приложения более виртуозно. Это средство – Visual Basic for Applications (VBA) – объектно-ориентированный язык программирования, разработанный в свое время для записи макросов, а вскоре дополненный возможностью создания процедур в приложениях.

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

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

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

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

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

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

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

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