Excel 2007 Электронная таблица - это средство информационных технологий, позволяющее решать целый комплекс задач. Прежде всего, выполнение вычислений. Многие расчеты выполняются в табличной форме: многочисленные расчетные ведомости, сметы расходов и т. п. Решения многих вычислительных задач на ЭВМ легко реализовать с помощью электронных таблиц без разработки специальных программ. . Встроенные функции облегчают решение различных задач (финансовых, статистических, инженерных и др. ) Основное свойство электронных таблиц — мгновенный пересчет формул при изменении значений входящих в них операндов. Благодаря этому свойству, таблица представляет собой удобный инструмент для: Кафедра автоматизированной обработки информации 1
Excel 2007 подбора параметров, n создание сценариев, n прогноза поведения моделируемой системы, n анализа зависимостей, n поиска решения, n планирования. n Дополнительные удобства для моделирования дает возможность графического представления данных (диаграммы). Электронную таблицу используют также в качестве базы данных для хранения, поиска и сортировка информации. Электронная таблица дает возможность объединять данные путем консолидации и составления сводных таблиц. Наиболее распространенные сферы применения электронных таблиц – это планирование бюджета, учет и составление сводных отчетов, финансовый анализ, управление списками (табличной базой данных). n Кафедра автоматизированной обработки информации 2
Основы Microsoft Excel n n n Рабочая книга и рабочие листы. Рабочие книги - это файлы Excel, которые могут содержать одну или несколько рабочих таблиц. По умолчанию в рабочей книге Еxcel три листа, но при необходимости можно вставлять новые листы. Так сетка Excel 2007 состоит из 1 048 576 строк и 16 384 столбцов. Строки и столбцы поименованы. Строки нумеруют слева числами, столбцы сверху - буквами. Ячейка - область, определяемая пересечением столбца и строки электронной таблицы. Каждая ячейка имеет собственное имя (адрес), которое образуется из названия строк и столбцов, на пересечении которых находится эта ячейка. Любой ввод с клавиатуры на лист происходит в активную или рабочую ячейку. В ячейки Excel можно ввести числа, текст, даты, время, последовательные ряды данных, а также формулы для обработки данных. Кафедра автоматизированной обработки информации 3
Основы Microsoft Excel Ввод и редактирование формул и данных выполняется через строку формул, в которой отражается адрес ячейки и содержимое активной ячейки (данные или формула). n По умолчанию Excel вводит данные в формате Общий, причем он самостоятельно пытается подобрать формат для вводимых данных, например, для чисел: целое, с десятичной запятой или в экспоненциальной форме. Если же вы уже ввели данные и они представляются на экране неправильно или если хотите изменить их формат, то можете это сделать с помощью кнопок панели доступных на вкладке Главная в группе Число. Кафедра автоматизированной обработки информации 4
Вычисления в таблицах Excel n n Любой расчет в Excel строится на основе формул. Ввод формулы всегда начинается со знака равно (=), за которым следуют числовые константы, адреса ячеек или дипазонов с добавленными знаками математических действий и скобками. На рис. 1 показаны введенные формулы. рис. 1 Кафедра автоматизированной обработки информации 5
Вычисления в таблицах Excel Если Excel не в состоянии произвести правильное вычисление формулы, то в ячейку выводится ошибочное значение. Ошибочные значения начинаются со знака #: n #ДЕЛ/0! Деление на нуль. n #Н/Д Ссылка на недопустимое значение. n #ИМЯ? Использование имени, нераспознаваемого MS Excel. n #ПУСТО Неверное пересечение двух областей. n #ЧИСЛО! Неправильное использование числа. n #ССЫЛКА! Ссылка на недопустимую ячейку. n #ЗНАЧЕН! Использование неправильного аргумента или операнда. n ###### Результат вычислений не помещается в ячейке (необходимо расширить столбец). Кафедра автоматизированной обработки информации 6
Вычисления в таблицах Excel n n Чтобы не вводить одинаковые формулы в другие ячейки, они копируются путем автозаполнения. При копировании Excel изменяет в формулах значения ссылок относительно своего нового расположения. В этом случае говорят об относительных ссылках. Если требуется закрепить адрес ячейки, чтобы он не изменялся при копировании, делают абсолютный адрес с помощью символа $. В нашем примере относительный адрес D 8 следует заменить на абсолютный $D$8. При смешанной адресации ячейки закрепляется один параметр адреса, строки или столбца, например D$8. Иногда при создании расчета необходима передача информации по ссылке (из одной ячейки в другую). Например, чтобы данные на конец месяца января – из ячейки Е 5 перешли на начало месяца февраля – в ячейку В 6, надо установить курсор в ячейку В 6 и ввести туда формулу « =Е 5» . Кафедра автоматизированной обработки информации 7
Вычисления в таблицах Excel n n Для показа введенных в ячейки формул необходимо выбрать режим показа формул. Для этого во вкладке Формулы выбрать значок Зависимости формул и команду Показать формулы. Для любой ячейки рабочего листа можно написать примечание, которое будет выводится на экран, когда на эту ячейку будет наведен указатель мыши. Для создания примечания выделите ячейку, для которой создается примечание, выполните команду Рецензирование, Примечание, Создать примечание и в появившееся поле введите текст примечания. Чтобы предотвратить умышленное либо случайное изменение, перемещение или удаление важных данных, можно установить защиту определенных элементов листа или книги с использованием пароля. Можно защитить только формулы, чтобы исключить порчу формул расчета и застраховаться от ошибок. Для этого выполняются следующие операции: Кафедра автоматизированной обработки информации 8
Вычисления в таблицах Excel n 1. Выделить рабочий лист. n 2. В вкладке Главная в группе Ячейки нажать кнопку Формат, в раскрывшемся списке выбрать Формат ячеек, в раскрывшемся диалоговом окне Списки выбрать вкладку Защита и снять флажок на кнопке Защищаемая ячейка. n 3. Отменить выделение рабочего листа. n 4. Выделить блок ячеек с формулами. n 5. На вкладке Главная в группе Ячейки нажать кнопку Формат, в раскрывшемся списке выбрать Формат ячеек, в раскрывшемся диалоговом окне Списки выбрать вкладку Защита и поставить флажок на кнопке Защищаемая ячейка n 6. В вкладке Главная в группе Ячейки нажать кнопку Формат, в раскрывшемся списке выбрать команду Защитить лист, в раскрывшемся окне при необходимости ввести пароль Кафедра автоматизированной обработки информации 9
Вычисления в таблицах Excel n. В результате формулы защищены. Вы можете изменять незащищенные ячейки с исходными данными, а в ячейки с формулами расчета доступ будет заблокирован. Если надо изменить формулы, то для этого надо снять защиту листа. n n Работа с функциями Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов функции, стоящих в скобках после ее имени. При записи функций в Excel используются такие правила: аргументы функции должны заключаться в круглые скобки, причем нельзя ставить пробелы ни перед скобками, ни после них. в качестве аргументов можно использовать числа, логические значения, массивы, текст, ссылки или формулы. Кафедра автоматизированной обработки информации 10
Работа с функциями nв качестве разделителя между аргументами нужно использовать символ точка с запятой (; ) . В поставку Excel входит более 300 функций. При вводе функции с помощью мастера функций необходимо выполнить следующие действия: n Выберите команду Функция меню Вставка. На экране появится первая страница диалогового окна Мастер функций. n Выберите в списке "Категория" нужный тип функции, затем активизируйте в списке "Функция" нужную функцию. Функция ЕСЛИ одна из самых важных функций, так как с помощью этой функции можно принимать вариант решения. Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Кафедра автоматизированной обработки информации 11
Работа с функциями Синтаксис: ЕСЛИ (лог. выражение; значение если истина; значение если ложь) До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов. Первый аргумент – логическое выражение, которое может принимать значение Истина или Ложь, второй и третий аргументы вычисляются, если первый аргумент принимает истинное или ложное значение. n Функция СУММЕСЛИ используется для подсчета суммы показателей, выбранных из диапазона ячеек по заданному критерию. Синтаксис: СУММЕСЛИ (интервал; критерий; сумм интервал) Интервал - это интервал вычисляемых ячеек. Критерий - это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Кафедра автоматизированной обработки информации 12
Работа с функциями Сумм интервал - это фактические ячейки для суммирования. Ячейки в сумм интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют критерий. Если сумм интервал опущен, то суммируются ячейки в аргументе интервал. n Функция СЧЕТЕСЛИ подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию. Синтаксис: СЧЁТЕСЛИ (интервал; критерий) Интервал - это интервал, в котором нужно подсчитать ячейки. Критерий - это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Финансовые функции предназначены для расчета операций по кредитам, ссудам, займам, ценным бумагам, эффективности инвестиций и других расчетов. Кафедра автоматизированной обработки информации 13
Работа с функциями Эти расчеты основаны на концепции временной стоимости денег и предполагают не равноценность денег, относящихся к разным периодам времени. (Сегодняшние деньги стоят меньше, чем вчерашние и больше, чем завтрашние). Деньги, доходы и расходы, относящиеся к разным моментам времени, можно сопоставить путем приведения к одному сроку (путем дисконтирования). Аргументами финансовых функций часто являются следующие величины: n будущее значение – стоимость вложения или ссуды по завершении всех отложенных платежей; n количество выплат – общее количество платежей или периодов выплат; n выплата – объем периодической выплаты по вложению или ссуде; n текущее значение – начальная стоимость вложения или ссуды. Так, начальная стоимость ссуды равна, собственно, сумме 14 займа; Кафедра автоматизированной обработки информации
Работа с функциями Так, начальная стоимость ссуды равна, собственно, сумме займа; n ставка – процентная ставка или скидка по вложению или ссуде; n режим выплат – режим выплат, с которым осуществляются выплаты (в конце или в начале месяца). Функция БС предназначена для расчета будущей стоимости периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки. n Синтаксис: БС(ставка; кпер; плт; пс; тип) Функция ПС Возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат. Например, в момент займа его сумма является приведенной (нынешней) стоимостью для заимодавца. n Синтаксис: ПС(ставка; кпер; плт; бс; тип) Кафедра автоматизированной обработки информации 15
Работа с функциями Функция КПЕР вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, то для того чтобы найти число лет выплат, общее число периодов надо разделить на число периодов в году. n Синтаксис КПЕР(ставка; плт; пс; бс; тип) Функция СТАВКА определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение необходимо умножить на число расчетных периодов в году. n Синтаксис СТАВКА (кпер; плт; пс; бс; тип; прогноз). Кафедра автоматизированной обработки информации 16
Работа с функциями Функция ПЛТ вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки. Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или гонораров, иногда связываемых со ссудой. Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на количество периодов (кпер). n Синтаксис ПЛТ (Ставка; Кпер; Пс; Бс; тип) Функция ОСПЛТ возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянных периодических платежей и постоянной процентной ставки. n Синтаксис ОСПЛТ(ставка; период; кпер; пс; бс; тип) Кафедра автоматизированной обработки информации 17
Работа с функциями Функция ПРПЛТ возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки. n Синтаксис ПРПЛТ(ставка ; период; кпер; пс; бс; тип) Сумма платежа в погашение основной суммы кредита и платежа процентов равняется выплате, возвращаемой функцией ПЛТ. n Построение диаграмм в Excel Типы диаграмм Диаграммы - это наглядное представление данных рабочего листа. Excel позволяет построить разные виды диаграмм, например, график, линейчатую и круговую диаграммы. Они помогают выявить те закономерности и тенденции, которые не столь очевидны в том случае, если данные представлены только на листе. Например, вместо анализа нескольких столбцов чисел на листе можно, взглянув на диаграмму, узнать, падают или растут объемы продаж по кварталам или, каковы отклонения от плана. n Кафедра автоматизированной обработки информации 18
Построение диаграмм в Excel автоматически обновляет диаграмму, если изменяются те данные рабочего листа, на основе которых она была построена Диаграмма в рабочей книге может быть внедрена. непосредственно в рабочий лист, либо размещена на отдельном листе диаграмм. Тип диаграммы выбирается в зависимости от цели и от смыслового содержания данных. Для выбора типа диаграммы во вкладке Вставка в группе Диаграммы вызывается диалоговое окно Вставка диаграммы. Данные, которые расположены в столбцах или строках, можно изобразить в виде гистограммы. Гистограммы используются для демонстрации изменений данных за определенный период времени или для иллюстрирования сравнения объектов. В гистограммах категории обычно формируются по горизонтальной оси, а значения — по вертикальной. Кафедра автоматизированной обработки информации 19
Построение диаграмм в Excel n n Данные, которые расположены в столбцах или строках, можно изобразить в виде графика. Графики позволяют изображать непрерывное изменение данных с течением времени в едином масштабе; таким образом, они идеально подходят для изображения трендов изменения данных с равными интервалами. На графиках категории данных равномерно распределены вдоль горизонтальной оси, а значения равномерно распределены вдоль вертикальной оси. Данные, которые расположены в одном столбце или строке, можно изобразить в виде круговой диаграммы. Круговая диаграмма демонстрирует размер элементов одного ряда данных пропорционально сумме элементов. Точки данных на круговой диаграмме выводятся в виде процентов от всего круга, т. е. отражают структуру данных. Как и круговая диаграмма, кольцевая диаграмма отображает отношение частей к целому, но может содержать более одного ряда данных Кафедра автоматизированной обработки информации 20
Построение диаграмм в Excel n n Данные, которые расположены в столбцах или строках, можно изобразить в виде диаграммы с областями. Диаграммы с областями иллюстрируют величину изменений в зависимости от времени и могут использоваться для привлечения внимания к суммарному значению в соответствии с трендом. Например, данные, отражающие прибыль в зависимости от времени, можно отобразить в диаграмме с областями, чтобы обратить внимание на общую прибыль. Отображая сумму значений рядов, такая диаграмма наглядно показывает вклад каждого ряда. Данные, которые расположены в столбцах и строках, можно изобразить в виде точечной диаграммы. Точечная диаграмма показывает отношения между численными значениями в нескольких рядах данных или отображает две группы чисел как один ряд координат x и y. Кафедра автоматизированной обработки информации 21
Построение диаграмм в Excel Точечные диаграммы обычно используются для представления и сравнения числовых значений, например, научных, статистических или инженерных данных. Для вывода данных таблицы в виде точечной диаграммы следует поместить данные по оси X в одну строку или столбец, а соответствующие данные по оси Y — в соседние строки или столбцы. Данные, которые расположены в столбцах или строках в определенном порядке, можно изобразить в виде биржевой диаграммы. Как следует из названия, биржевая диаграмма наиболее часто используется для иллюстрации изменений цен на акции. Однако эта диаграмма может использоваться также для вывода научных данных. Например, можно использовать биржевые диаграммы для демонстрации колебаний дневных или годовых температур. Для создания биржевой диаграммы необходимо правильно упорядочить выводимые данные. Кафедра автоматизированной обработки информации 22
Построение диаграмм в Excel Способ расположения в электронной таблице данных, которые будут использованы в биржевой диаграмме, очень важен. Так, для создания простой биржевой диаграммы «максимальныйминимальный-закрытие» следует поместить данные в столбцы с заголовками «Максимальный» , «Минимальный» и «Закрытие» в соответствующем порядке. n Создание диаграмм n Любая диаграмма состоит из нескольких стандартных элементов. Большую часть этих элементов можно изменять и создавать отдельно. Ниже приведен пример гистограммы. n Кафедра автоматизированной обработки информации 23
Построение диаграмм в Excel Кафедра автоматизированной обработки информации 24
Построение диаграмм в Excel n n Чтобы создать диаграмму, необходимо сначала ввести для нее данные на листе. Значения этих данных в строке или в столбце, формирующие отдельную линию (или отдельные столбики) на диаграмме, называются рядом данных. Затем выделите эти данные и выберите нужный тип диаграммы на ленте (вкладка Вставка, группа Диаграммы). Создав диаграмму, можно вносить в нее изменения. Например, можно изменить вид осей. Ось Y обычно расположена вертикально, а вдоль нее строятся данные. Ось X обычно расположена горизонтально, а вдоль нее строятся категории, добавить название диаграммы, переместить или скрыть легенду, а также добавить дополнительные элементы диаграммы. У каждого элемента диаграммы имеется контекстное меню, для открытия которого надо поместить на этот элемент указатель мыши и щелкнуть правой кнопкой. Кафедра автоматизированной обработки информации 25
Построение диаграмм в Excel n Данные для биржевой диаграммы 350 30 Биржевая диаграмма 300 25 250 20 200 15 150 10 100 5 50 0 0 Дата 05. май 12. май 03. июн 17. июн 02. окт 21. дек Кафедра автоматизированной обработки информации 26
Построение диаграмм в Excel n При создании диаграммы открывается доступ к инструментам для работы с диаграммой: отображаются вкладки Конструктор, Макет и Формат. Команды этих вкладок можно использовать для изменения представления данных на диаграммах. Например, вкладка Конструктор используется для отображения рядов данных по строкам или по столбцам, внесения изменений в исходные данные, изменения размещения диаграммы, изменения типа диаграммы, сохранения диаграммы в качестве шаблона или выбора предварительно определенных параметров макета и форматирования. Вкладка Макет используется для изменения таких элементов диаграммы, как заголовки диаграмм и подписи данных, использования инструментов рисования, а также добавления к диаграмме текстовых полей и рисунков. Вкладка Формат позволяет добавлять заливку цветом, изменять тип линий или использовать специальные эффекты. Кафедра автоматизированной обработки информации 27
Построение диаграмм в Excel n n Составные диаграммы – это диаграммы, построенные с использованием одновременно двух и более типов диаграмм (для разных рядов данных), или диаграммы, использующие дополнительную ось. Пример составной диаграммы с двумя рядами данных Кафедра автоматизированной обработки информации 28
Построение диаграмм в Excel n Пример диаграммы с дополнительной осью. Кафедра автоматизированной обработки информации 29
Построение диаграмм в Excel n n n Для создания составной диаграммы выделяем правой кнопкой на диаграмме ряд, для которого надо изменить тип диаграммы, в нашем примере «Стоимость продукции» . Выбираем в контекстном меню «Изменить тип диаграммы для ряда» , в открывшемся диалоговом окне «Изменение типа диаграммы» выбираем график. В результате получаем составную диаграмму, в которой совмещены гистограмма и график. Для создания составной диаграммы с дополнительной осью необходимо: щёлкнуть правой кнопкой на диаграмме ряд, в контекстном меню выбрать Формат ряда данных, в диалоговом одноименном окне перейти на вкладку Параметры ряда и выбрать переключатель «По вспомогательной оси» Диаграммы связаны с данными рабочего листа и могут использоваться для анализа данных. Если расчет преобразован в таблицу (появляется возможность фильтрации данных по столбцам ), то при изменяем данных соответственно 30 Кафедра автоматизированной обработки информации изменяется диаграмма.
Построение диаграмм в Excel n Диаграммы связаны с данными рабочего листа и могут использоваться для анализа данных. Если расчет преобразован в таблицу (появляется возможность фильтрации данных по столбцам ), то при изменяем данных соответственно изменяется диаграмма. Если имеются данные, для которых следует спрогнозировать тренд, можно создать на диаграмме линию тренда. Например, если имеется созданная в Excel диаграмма, на которой приведены данные о продажах за первые несколько месяцев года, можно добавить к ней линию тренда, которая представит общие тенденции продаж (рост, снижение или стабилизацию) продемонстрирует предполагаемую тенденцию на ближайшие месяцы. Кафедра автоматизированной обработки информации 31
Построение диаграмм в Excel n n Для этого необходимо выделить диаграмму, выбрать ряд данных, к которому нужно добавить линию тренда, на вкладке Макет в группе Анализ нажать кнопку Линия тренда и выбрать нужный тип регрессионной линии тренда или скользящего среднего. Для определения параметров и форматирования регрессионной линии тренда или скользящего среднего щелкните линию тренда правой клавишей мыши и выберите пункт Формат линии тренда. Выберите параметры линии тренда, тип линий и эффекты, установите флажки «показывать уравнение на диаграмме» , «поместить величину достоверности аппроксимации» . Кафедра автоматизированной обработки информации 32
Построение диаграмм в Excel n Линия тренда и уравнение приведены на следующей диаграмме. Кафедра автоматизированной обработки информации 33
Работа со списками. n n n Список – это упорядоченный набор данных. Список состоит из строки заголовков (наименование столбцов) и строк данных, которые могут быть текстовыми и числовыми. Список можно считать табличной базой данных. Столбцы списков становятся полями базы данных, заголовки столбцов становятся именами полей базы данных, каждая строка списка преобразуется в запись данных. Списки используются для хранения наборов данных, поиска данных, сортировки данных, подведения промежуточных итогов. Рекомендации по созданию списка на листе книги. 1. Размер и расположение списка. На листе не следует помещать более одного списка. Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Кафедра автоматизированной обработки информации 34
Работа со списками n n n n В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка. Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми. 2. Заголовки столбцов должны находиться в первой строке списка. Они используют Excel при составлении отчетов, поиске и организации данных. Для отделения заголовков от расположенных ниже данных следует использовать границы ячеек, а не пустые строки или прерывистые линии. 3. Содержание строк и столбцов Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные. Не следует помещать пустую строку между заголовками и первой строкой данных. 35 Кафедра автоматизированной обработки информации
Работа со списками n n n Ввод данных в список. Данные можно ввести непосредственно в список или при помощи специальной формы ввода данных. Когда Вы начинаете вводить данные в таблицу, то начинает работать автозаполнение, т. е. программа находит закономерность и заполняет остаток ячейки. Другим средством ускоренного ввода данных является команда контекстного меню Выбрать из раскрывающегося списка. Для этого следует выделить ячейку, в которую надо ввести данные, правой кнопкой вызвать команду Выбрать из раскрывающегося списка, в раскрывшемся списке выбрать необходимое значение. Форма ввода данных вызывается при помощи кнопки Форма, которую необходимо добавить на панель быстрого доступа. Для этого щелкните правой кнопкой на панели быстрого доступа и выберете Настройка панели быстрого доступа, в поле «Выбрать команды из: » выберете Команды не на ленте, выделите в левом списке Форма и нажмите кнопку Добавить, ОК. Когда появится форма, в ней будет показана первая запись списка 36 Кафедра автоматизированной обработки информации
Работа со списками n n Для ввода новой записи надо выбрать Добавить, после чего очистятся все поля, далее вводятся данные в соответствующие поля и выбирается Добавить. Форма используется также для поиска данных в списке. Чтобы задать условия поиска или условия сравнения, нажмите кнопку Критерии. Введите условия критериев в соответствующие поля формы. Чтобы найти совпадающие с условиями критериев записи, нажмите кнопки Далее или Назад. Чтобы вернуться к правке формы, нажмите кнопку Добавить. . Фильтрация списка. Для поиска данных используется фильтрация списка. Фильтрация списка – это сокрытие всех строк, кроме тех, которые удовлетворяют определенным критериям. Списки можно фильтровать двумя способами: с помощью режима автофильтр – используется для фильтрации по простым критериям, и с использованием режима расширенный фильтр – применяется для фильтрации по более сложным критериям. Кафедра автоматизированной обработки информации 37
Работа со списками n n Использование автофильтра Найдем с помощью автофильтра работника с окладом больше 12000 руб. Для этого сделаем следующее: 1. Установите курсор в какой-либо ячейке фильтруемого списка. 2. Выберите команду Данные, Сортировка и Фильтр, Фильтр. После этого в заголовках столбцов появятся кнопки раскрывающегося списка Кафедра автоматизированной обработки информации 38
Работа со списками n 3. Нажмите кнопку со стрелкой в столбце Оклад и в появившемся перечне элементов столбца выберите пункт Числовые фильтры, Больше или равно и в появившемся диалоговом окне Пользовательский автофильтр задайте условие Оклад больше 12000 Кафедра автоматизированной обработки информации 39
Работа со списками n n n Повторите шаг 3 для установки условий поиска в других столбцах (если это необходимо). Результаты поиска в списке сотрудников. Если данные уже отфильтрованы по одному из столбцов, то при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке Кафедра автоматизированной обработки информации 40
Работа со списками n n n Фильтрация списка с помощью расширенного фильтра Чтобы отфильтровать список по более сложным критериям, необходимо использовать команду Расширенный фильтр (команда Данные, Сортировка и фильтр, Дополнительно). Последовательность действий при этом следующая: 1. Скопируйте из списка заголовки фильтруемых столбцов (для создания интервала критериев). 2. Вставьте скопированные заголовки столбцов в пустой строке над или под списком, отступив от списка минимум на одну строку. 3. Введите в строки под заголовками условий требуемые критерии отбора (создан интервал критериев). Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка. Кафедра автоматизированной обработки информации 41
Работа со списками n n 4. Установите курсор в списке. Выполните команду Данные, Сортировка и фильтр, Дополнительно и в появившемся окне установите переключатель Обработка в положение Скопировать результат в другое место. Кафедра автоматизированной обработки информации 42
Работа со списками n n Введите в поле Исходный диапазон ссылку на диапазон ячеек списка, в поле Диапазон условий введите ссылку на диапазон условий отбора, включающий также и заголовки столбцов, а в поле Поместить результат в диапазон укажите диапазон ячеек, где будет помещен результат На рисунке показаны исходный список, условия отбора и результаты поиска. Кафедра автоматизированной обработки информации 43
Работа со списками Кафедра автоматизированной обработки информации 44
Работа со списками n n Сортировка списка Excel предлагает Вам удобные средства упорядочения данных списка и создания отчетов на их основе. Одним из таких средств является сортировка данных. Упорядочение данных может проходить в алфавитном, числовом или хронологическом порядке. Перед началом сортировки Вы должны указать порядок сортировки: по возрастанию или по убыванию. Чтобы отсортировать весь список, выделите одну ячейку списка и выполните команду Данные, Сортировка и фильтр, Сортировка. Excel автоматически выделит весь список. В появившемся окне диалога устанавливаются поля списка, по которым производится сортировка. В данном примере сортировка производится по возрастанию по полю Образов. , затем по полю Ф. И. О. Кафедра автоматизированной обработки информации 45
Работа со списками После нажатия на кнопку ОК записи в списке будут отсортированы по образованию и затем по Ф. И. О. Кафедра автоматизированной обработки информации 46
Работа со списками n Результаты сортировки Кафедра автоматизированной обработки информации 47
Работа со списками n n n Создание промежуточных отчетов. После сортировки списка можно подвести промежуточные итоги. Для этого вызывается команда Данные, Структура, Промежуточные итоги, в появившемся окне указывается поле, при изменении которого подводятся итоги, и поля, по которым добавляются итоги. Для отмены промежуточных итогов выполняется команда. Данные, Структура, Промежуточные итоги и в появившемся окне нажимается кнопка Очистить всё. После выполнения команды на листе остается отсортированный список без итогов. Кафедра автоматизированной обработки информации 48
Работа со списками Для расчета различных показателей к данным списка можно применять различные функции Кафедра автоматизированной обработки информации 49
Работа со списками n Результаты подведения итогов Кафедра автоматизированной обработки информации 50
Объединение данных. n n n Консолидация данных. Консолидация – это объединение данных разных областей одного рабочего листа, нескольких рабочих листов и даже нескольких рабочих книг. Консолидация применяется при составлении квартальных отчетов, обработке данных по обороту средств и т. д. С ее помощью можно находить суммы, средние значения, максимальные, минимальные и другие значения данных, представленных в исходных таблицах. 1. Для консолидации данных из трех листов откройте новый лист, дайте ему имя Консолидация и установите курсор в то место, где будут отражены результаты консолидируемых данных. 2. Выполните команду Данные, Работа с данными, значок Консолидация. Кафедра автоматизированной обработки информации 51
Объединение данных. n 3. В появившемся окне выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных (в нашем случае это функция Сумм). Кафедра автоматизированной обработки информации 52
Объединение данных. n n n 4. Введите в поле Ссылка исходную область консолидируемых данных, расположенных на Листе 1. Убедитесь, что исходная область имеет заголовок. 5. Нажмите кнопку Добавить. В поле Список диапазонов появится запись Лист1!$A$3: $D$9. 6. Повторите шаги 4 и 5 для консолидируемых исходных областей на листах 2 и 3. 7. В наборе флажков Использовать в качестве имен установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно. 8. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными и щелкните на ОК. Кафедра автоматизированной обработки информации 53
Объединение данных. n n n Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию. Своды различных форм и отчётов можно также делать при помощи Мастера сводных таблиц и диаграмм использую на 1 шаге работы этого мастера переключатель «в нескольких диапазонах консолидации» . Сводная таблица Отчет сводной таблицы представляет собой интерактивный метод быстрого суммирования больших объемов данных. Отчет сводной таблицы используется для подробного анализа числовых данных и для ответов на непредвиденные вопросы по данным. Отчет сводной таблицы специально предназначен для следующего: Кафедра автоматизированной обработки информации 54
Объединение данных. 1. 2. 3. 4. Организации запросов к большим массивам данных дружественными по отношению к пользователю способами. Подведения промежуточных итогов и применения статистических функций к числовым данным, суммирования данных по категориям и подкатегориям, а также для создания дополнительных вычислений и формул. Развертывания и свертывания уровней представления данных для привлечения внимания к результатам, а также развертывания отчетов с целью получить подробные сведения из итоговых данных по нужным областям. Перемещения строк в столбцы или столбцов в строки (или «сведение» ) для просмотра различных сводных данных по исходным данным. Кафедра автоматизированной обработки информации 55
Объединение данных. Фильтрации, сортировки, группировки и условного форматирования наиболее важных и часто используемых подмножеств данных для привлечения внимания к нужным сведениям. 6. Представления кратких, наглядных и аннотированных отчетов в сети или в напечатанном виде. Сводные таблицы (отчеты) можно создавать одним из следующих способов: с помощью Мастера сводных таблиц и диаграмм, значок которого надо вызвать на панель быстрого доступа, и при помощи команды на ленте Вставка, Таблицы, Сводная таблица. Сводную таблицу можно создавать на основе данных, находящихся в списке, нескольких листах рабочей книги (при помощи Мастера сводных таблиц и диаграмм), во внешней базе данных, а также в другой сводной таблице. 5. Кафедра автоматизированной обработки информации 56
Объединение данных. n В качестве исходных данных рассмотрим следующую таблицу. Кафедра автоматизированной обработки информации 57
Объединение данных. n n n Для создания сводной таблицы курсор устанавливается в то место, где будут отражены результаты (свод) и вызывается Мастер сводных таблиц. На шаге 1 необходимо указать, где находятся исходные данные, и что будет создаваться (сводная таблица или сводная диаграмма). На шаге 2 указываем диапазон, содержащий исходные данные На шаге 3 указываем лист и адрес левого верхнего угла сводной таблицы. Щелкаем по кнопке Готово и получаем заготовку будущей сводной таблицы. Кафедра автоматизированной обработки информации 58
Объединение данных. Кафедра автоматизированной обработки информации 59
Объединение данных. n n Далее мы должны создать структуру сводной таблицы с помощью списка полей сводной таблицы. Для этого надо перетащить поля из Списка полей сводной таблицы в области разметки сводной таблицы. Область полей страниц позволяет сделать таблицу трехмерной, если здесь задано одно поле, и многомерной, если задано несколько полей. В примере в данную область перетащено поле Деталь. В области Строк целесообразно отражать поле с основным показателем необходимым для сводной таблицы. В нашем примере - это Цех. В области Столбцов целесообразно отражать показатели характеризующие показатели строк. . В нашем примере - это Квартал. Область Элементы данных должна содержать значения данных, по которым будут подводиться итоги. В нашем примере - это Количество, Брак, Цена. Кафедра автоматизированной обработки информации 60
Объединение данных. Кафедра автоматизированной обработки информации 61
Объединение данных. n В результате получим вариант структуры сводной таблицы Кафедра автоматизированной обработки информации 62
Объединение данных. n n При создании сводной таблицы по умолчанию Excel подводит общие и промежуточные итоги при помощи суммирования, если поле содержит текст, то подсчитывается число элементов. Чтобы изменить итоговую функцию следует установить курсор в одну из ячеек поля данных и правой кнопкой вызвать контекстное меню и в появившемся диалоговом окне выбрать необходимую функцию (рис. 7. 8. ). Если в сводной таблице есть несколько полей данных, то по каждому итогу можно выбрать свою итоговую функцию. В этом же диалоговом окне можно также заменить операцию на дополнительные вычисления (доля от суммы по строке, доля от суммы по столбцу, доля от общей суммы и т. п. ) Чтобы вставить в таблицу вычисляемое поле следует: установить курсор в область данных, выполнить команду Параметры, Сервис, Формула, Вычисляемое поле и в диалоговом окне Вставка вычисляемого поля: введите имя вычисляемого поля, введите формулу, используя поля из списка, выполните команду Добавить, ОК Кафедра автоматизированной обработки информации 63
Объединение данных. Кафедра автоматизированной обработки информации 64
Объединение данных. n n К данным сводной таблицы можно применять различные функции, используя команду Главная, Редактирование, Сумма На основе сводной таблицы составляется диаграмма, которая также является интерактивной, т. е. изменяется при фильтрации данных и изменении структуры таблицы. Для вставки диаграммы следует установить курсор на сводную таблицу и на вкладке Вставка в группе Диаграммы выбрать тип диаграммы. Не могут быть использованы точечная, пузырьковая и биржевая диаграммы. Кафедра автоматизированной обработки информации 65
Решение задач путем анализа данных Основное свойство электронных таблиц — мгновенный пересчет формул при изменении значений входящих в них операндов. Благодаря этому свойству, таблица представляет собой удобный инструмент для организации численного эксперимента и решения задач «что-если» . При этом используются такие встроенные в Microsoft Excel, как подбор параметра, ведение сценариев, поиск решения Подбор параметра Зачастую Вы знаете тот результат, который нужно получить с помощью вычисления по формуле, однако входное значение, необходимое для получения этого результата, Вам неизвестно. Чтобы решить эту задачу, можно воспользоваться подбором параметра. С помощью подбора параметра Excel варьирует значение в заданной ячейке до тех пор, пока вычисление по формуле, зависящей от этой ячейки, не даст нужный результат. n Кафедра автоматизированной обработки информации 66
Решение задач путем анализа данных n Рассмотрим пример расчета выручки от продаж. Кафедра автоматизированной обработки информации 67
Решение задач путем анализа данных n n n Пусть требуется определить сколько надо продать изделия 3, чтобы общая сумма выручки от продаж составила 55000 тыс. руб. Для решения этой задачи применим инструмент «Подбор параметра» . 1. Выполните команду Данные, Работа с данными, Анализ «чтоесли» , Подбор параметра. На экране появится диалоговое окно Подбор параметра 2. В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу (адрес общей выручки от продаж – D 8). 3. Введите искомый результат в поле Значение (55000). 4. В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение (количество изделия 3 – $B$5). После выполнения расчета количество изделия 3 будет равно 300. (рис. 8. 2. ). Кафедра автоматизированной обработки информации 68
Решение задач путем анализа данных n Результат подбора параметров. Кафедра автоматизированной обработки информации 69
Решение задач путем анализа данных Ведение сценариев Средства Excel позволяют создавать и сохранять в виде сценариев наборы входных значений, приводящих к различным результатам. Сценарий дает возможность узнать, что произойдёт с результатом, если поменять исходное значение в расчете. Для примера сделаем таблицу расчета ипотечной ссуды. Кафедра автоматизированной обработки информации 70
Решение задач путем анализа данных n Для наглядности итогового отчета присвоим изменяемым ячейкам и ячейкам результатов имена. Для этого выделим диапазон расчета, выполним команду Формулы, Определение имени, Создать из выделенного фрагмента. На экране появится окно диалога Создание имен из выделенного диапазона. Кафедра автоматизированной обработки информации 71
Решение задач путем анализа данных n n Для рассмотрения различных вариантов погашения ссуды создадим сценарии, где при изменении срока погашения ссуды и процентной ставки, будут пересчитываться месячная плата на погашение ссуды, общая сумма выплат и сумма комиссионных банка. 1. Выполните команду Данные, Работа сданными, Анализ «чтоесли» , Диспетчер сценариев. Появится окно диалога Диспетчера сценариев. 2. Нажмите кнопку Добавить. На экране появится диалоговое окно Изменение сценария Кафедра автоматизированной обработки информации 72
Решение задач путем анализа данных n n 3. Введите необходимое имя в поле Название сценария. 4. Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки. Нажмите кнопку OK. На экране появится диалоговое окно Значение ячеек сценария Кафедра автоматизированной обработки информации 73
Решение задач путем анализа данных n n n 6. Введите необходимые значения в диалоговом окне Значения ячеек сценария. 7. Чтобы создать сценарий, нажмите кнопку OK. Для создания дополнительных сценариев нажмите кнопку Добавить, а затем повторите шаги с 3 по 7. После завершения создания сценариев нажмите кнопку OK, после чего появится окно Диспетчера сценариев с их именами. Кафедра автоматизированной обработки информации 74
Решение задач путем анализа данных n n После ввода всех сценариев можно провести расчеты и создать итоговый отчет. 8. Нажмите кнопку Отчет, появится диалоговое окно Отчет по сценарию. Кафедра автоматизированной обработки информации 75
Решение задач путем анализа данных n n 9. Установите переключатель в положение Структура. и в поле Ячейки результата введите ссылки на ячейки результатов расчета. 10. Нажмите кнопку ОК, в результате автоматически вставится лист Структура сценария Кафедра автоматизированной обработки информации 76
Решение задач путем анализа данных n n Поиск решения Программа Поиск решения Excel является мощным инструментом оптимизации и распределения ресурсов. С ее помощью Вы найдете наилучший вариант использования ограниченных ресурсов, обеспечивающий максимальное значение для одних величин, например, прибыли, или же минимальное – для других, например, затрат. Задачи, которые лучше всего решаются с помощью данного инструмента, имеют три свойства Во-первых, у них имеется единственная цель, например, максимум прибыли. Во-вторых, имеется набор исходных данных-переменных, непосредственно влияющих на целевой результат. В-третьих, имеются ограничения для переменных, выражающихся, как правило, в виде неравенств. Например, производство продукции ограничено наличием сырья, время работы станка не может превышать 24 часа в сутки и т. п. Кафедра автоматизированной обработки информации 77
Решение задач путем анализа данных n n Следовательно, до выполнения поиска решения необходимо сделать постановку задачи, т. е. определить порядок расчета результата на основе исходных данных-переменных, а уже к этому расчету применять поиск оптимального решения. Для примера рассмотрим следующую задачу. Требуется определить оптимальную цену продажи изделия при которой валовая прибыль достигает максимального значения. Цена изделия может изменяться в пределах от 50 до 100 руб. Расчетная таблица в режиме показа формул приведена ниже. Кафедра автоматизированной обработки информации 78
Решение задач путем анализа данных n n Для определения оптимальной цены изделия выполните следующие действия: 1. Выполните команду Данные, Анализ, Поиск решения. На экране появится диалоговое окно Поиск решения Кафедра автоматизированной обработки информации 79
Решение задач путем анализа данных n В появившемся окне Поиск решения укажите целевую ячейку (Валовая прибыль - $G$6), изменяемую ячейку (цена - $B$6), введите ограничения на цену изделия ($B$6 <= 100 и $B$6 >= 50). Ограничения добавляются по одному за один раз и отражаются в поле Ограничения. Для добавления ограничения щелкнете по кнопке Добавить, появится окно Добавление ограничения, в которое вводится ссылка на ячейку, выбирается оператор ограничения и вводится значение ограничения Кафедра автоматизированной обработки информации 80
Решение задач путем анализа данных n После щелчка по кнопке Добавить введенные данные переносятся в поле Ограничения окна Поиск решения и вводится следующее ограничение. После ввода последнего ограничения щелкните по кнопке ОК. Вновь появляется окно Поиск решения. Щелкните по кнопке Выполнить. После выполнения расчета появится сообщение Результаты поиска решения. Кафедра автоматизированной обработки информации 81
Решение задач путем анализа данных n Расчет валовой прибыли при оптимальной цене приведен ниже. n По найденным результатам можно создавать три типа отчетов для сравнения влияния различных ограничений или исходных данных. Тип отчета выбирается по окончании поиска решения в диалоговом окне Результаты поиска решения. Каждый отчет будет создан на отдельном рабочем листе. Кафедра автоматизированной обработки информации 82
Решение задач путем анализа данных n Отчет по результатам вставляется на отдельный лист и приведен ниже. Кафедра автоматизированной обработки информации 83
Прогнозирование показателей Для расчета ожидаемого исполнения бюджета, при составлении проекта бюджета на следующий год и составлении различных планов используется прогнозирование различных экономических показателей. n В Excel для прогнозирования используются ряд функций (ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ) и диаграммы. Прогнозирование с помощью функций Функция ПРЕДСКАЗ позволяет сделать прогноз, применяя линейную регрессию диапазона известных данных или массивов (x, y). Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям n Синтаксис: ПРЕДСКАЗ(х; изв_знач_y; изв_знач_x) n В качестве примера выполним расчет ожидаемой прибыли за 2008 год на основе данных о полученной прибыли за 2001 -2007 годы, используя функцию ПРЕДСКАЗ n Кафедра автоматизированной обработки информации 84
Прогнозирование показателей n n Для расчета прибыли за 2008 год установите курсор в ячейку С 11, вызовете Мастер функций выберите категорию функций Статистические и затем вызовите функцию ПРЕДСКАЗ. На экране появится диалоговое окно функции ПРЕДСКАЗ, в появившемся окне введите исходные данные и получите результат. Кафедра автоматизированной обработки информации 85
Прогнозирование показателей n Диалоговое окно функции ПРЕДСКАЗ Кафедра автоматизированной обработки информации 86
Прогнозирование показателей n n n Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и yзначений. Функция рабочего листа РОСТ может применяться также для аппроксимации существующих x- и y-значений экспоненциальной кривой. Синтаксис: РОСТ(изв_знач_y; изв_знач_x; нов_знач_x; константа) Для расчета прибыли за 2008 год установите курсор в ячейку С 11, вызовете Мастер функций выберите категорию функций Статистические и затем функцию РОСТ. На экране появится диалоговое окно функции, в появившемся окне введите исходные данные и получите результат. Кафедра автоматизированной обработки информации 87
Прогнозирование показателей Кафедра автоматизированной обработки информации 88
Прогнозирование показателей n Результаты прогнозирования с помощью функции РОСТ n Функция ТЕНДЕНЦИЯ аппроксимирует прямой линией (по методу наименьших квадратов) массивы известных значений y и x. Рассчитывает значения y для новых значений x. Синтаксис: ТЕНДЕНЦИЯ(изв_знач_y; изв_знач_x; нов_знач_x; константа), n n Кафедра автоматизированной обработки информации 89
Прогнозирование показателей n Для расчета прибыли за 2008 год установите курсор в ячейку С 11, вызовете Мастер функций выберите категорию функций Статистические и затем функцию ТЕНДЕНЦИЯ. На экране появится диалоговое окно функции, в появившемся окне введите исходные данные и получите результат. Кафедра автоматизированной обработки информации 90
Прогнозирование показателей n Результаты прогнозирования с помощью функции ТЕНДЕНЦИЯ. n Сведем результаты прогнозирования с помощью этих функций в одну таблицу Кафедра автоматизированной обработки информации 91
Прогнозирование показателей Как видно из этого рисунка результаты прогнозирования с использованием функций ПРЕДСКАЗ и ТЕНДЕНЦИЯ совпадают, так как в обоих случаях использовалась линейная зависимость, в то время как функция РОСТ дала большее значение прогнозируемой величины вследствие применения экспоненциальной зависимости Кафедра автоматизированной обработки информации 92
Прогнозирование показателей n n n Прогнозирование при помощи диаграмм. Прогнозирование различных экономических показателей можно проводить с использованием диаграмм. После создания диаграммы в нее добавляется линия тренда и показывается уравнение тренда, а коэффициенты этого уравнения можно использовать для получения числовых значений прогнозируемых данных В качестве примера рассмотрим прогноз объема продаж на ближайшие два месяца по результатам продаж за предыдущие семь месяцев. Исходные данные соответствуют так называемым сезонным колебаниям спроса. Кафедра автоматизированной обработки информации 93
Прогнозирование показателей Кафедра автоматизированной обработки информации 94
Прогнозирование показателей n n На основе этих данных создадим точечную диаграмму. Добавим линию тренда в диаграмму. Для этого правой кнопкой щелкнем по точке данных на диаграмме и в контекстном меню выберем команду Добавить линию тренда. После выбора этой команды на экране появится диалоговое окно Формат линии тренда Кафедра автоматизированной обработки информации 95
Прогнозирование показателей Кафедра автоматизированной обработки информации 96
Прогнозирование показателей Кафедра автоматизированной обработки информации 97
Прогнозирование показателей n n Наиболее подходящим для наших исходных данных является полиномиальная линия тренда. Поставьте флажки в окошке показать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации. Нажмите кнопку Закрыть, и на экране появится результирующая диаграмма с уравнением линии тренда Кафедра автоматизированной обработки информации 98
Прогнозирование показателей n Введем формулу, указанную на диаграмме, в расчет в виде столбца Прогноз и получим результат Кафедра автоматизированной обработки информации 99
Скачать презентацию Excel 2007 Электронная таблица — это средство информационных
Excel07-09.pptx