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

Последующие лекции посвящены практической работе в прикладных программных пакетах общего и специального назначения. Они помогут выполнить лабораторный практикум и контрольную работу по изучаемой дисциплине. ЛЕКЦИЯ 3 Прежде, чем начать работу с программой MS Excel прочитайте и выполните! В результате Вы научитесь: ·   Корректно использовать адресацию ячеек рабочего листа; ·   Назначать имена ячейкам и диапазонам; ·   Вводить, редактировать и копировать формулы; ·   Обрабатывать ошибки, связанные с циклическими ссылками; ·   Использовать встроенные функции рабочего листа; ·   Применять формулы к массивам и др.; ·   Использовать инструмент Условного форматирования как быстрого и удобного средства наглядного представления данных.   Адресация ячеек на рабочем листе Ссылка (адрес) – это координаты, определяющие расположение ячейки на рабочем листе, включающие номер строки и столбца. Ссылка является идентификатором ячейки или группы ячеек на листе. Создавая формулу, содержащую ссылки на ячейки, мы связываем формулу с ячейками листа. Такой способ вычисления является наиболее удобным и часто используемым при решении задач. Значение формулы зависит от содержимого ячеек, на которые указывают ссылки, и изменяется при изменении содержимого этих ячеек. В MS Excel существует два способа адресации ячеек рабочего листа: относительный и абсолютный. Абсолютный адрес ячейки не изменяется при копировании формулы. Знак $ перед буквой в имени ячейки означает абсолютную ссылку на столбец с данным именем, а $ перед цифрой дает абсолютную ссылку на строку с указанным номером. Для ввода в формулу абсолютного адреса ячейки достаточно после ввода ее относительного адреса нажать клавишу . При адресации ячеек могут применяться смешанные ссылки, которые содержат относительную и абсолютную ссылки. В смешанной ссылке символ «$» может находиться либо перед буквой столбца, либо перед номером строки, например, $А1, А$1. Если символ доллара стоит перед буквой, то координата столбца абсолютная, а строки – относительная; если символ доллара стоит перед номером строки, то координата строки является абсолютной, а столбца – относительной. При переносе формул, содержащих смешанные ссылки, относительная часть ссылок изменяется, а абсолютная часть остается неизменной. Часто в формулах используют обращение к ячейкам и диапазонам вне текущего рабочего листа с помощью внешних ссылок. Внешние ссылки в MS Excel позволяют обратиться к ячейкам, находящихся на других листах этой же рабочей книги или на листах другой рабочей книги. При вводе вручную ссылки на другие листы той же книги необходимо указать перед адресом ячейки также ссылку на лист: например, Лист2!А1 - ссылается на ячейку А1, расположенную на втором листе книги. Ссылка на ячейку может быть как относительной, так и абсолютной. Для записи ссылки на листы других книг указывается имя книги, ссылка на лист и ссылка на ячейку, например, [Книга2]Лист2!А1 . Со ссылками могут использоваться следующие операторы: ¾ оператор интервалов - «:» - определяет ссылку на интервал ячеек, например, (А1:В10); ¾ оператор объединения - «;» - определяет одну ссылку, объединяя ячейки, на которые указывают две ссылки, например, (А1;В10); ¾ оператор пересечения - «пробел» - определяет одну ссылку на ячейки, общие для двух ссылок, например, (B5:D5 С4:С6). Результатом пересечения будет ячейка С5. Ввод ссылок с использованием мыши. Можно сэкономить время и избежать многих ошибок, если вводить в формулы ссылки на ячейки, выделяя их с помощью мыши, а не набирая адреса с клавиатуры. При щелчке по ячейке, вокруг нее появляется подвижная пунктирная рамка, и ссылка на эту ячейку вставляется в формулу. По окончании ввода формулы нужно нажать клавишу . Циклическая ссылка. При решении задач в случае неправильной адресации часто возникает ошибка циклической ссылки. Циклическая ссылка – это формула, которая зависит от своего собственного значения. Для разрешения циклической ссылки нужно выбрать меню Параметры вкладки Файл, в разделе  Формулы области Параметры вычислений установить флажок Включить итеративные вычисления. После этого Excel будет использовать последовательность итераций (метод итераций Гаусса - Зейделя) для поиска решения всех уравнений, использующих циклическую ссылку. Иногда требуется более ста итераций при относительной погрешности 0,001 (по умолчанию), для обеспечения сходимости при разрешении циклической ссылки. При необходимости величину относительной погрешности можно изменять. Рассмотрим следующий пример распределения затрат между отделами компании. Из суммы затрат на подразделение Планово - финансовому отделу (ПФО), Бухгалтерии и Отделу по управлению персоналу (ОК) на обслуживание других подразделений выделяется сумма 300000р., 400000р. и 200000р. соответственно. Доля рабочего времени на обслуживание остальных подразделений приведена в таблице на рабочем листе (рисунок ). Требуется определить суммы затрат, выделяемых на каждое подразделение. На рисунке   представлен пример листа с исходными данными и формулами для решения задачи. Стрелки указывают на влияющие ячейки. После ввода формул Excel выдаст предупреждение о циклической ссылке. Ячейка В13 влияет на В14 и В15, В14 – на В13 и В15, В15 – на В13 и В14. После включения итеративных вычислений, Excel выполнит вычисления (рисунок 1). Стрелки указывают на влияющие и зависимые ячейки. Рисунок 1 – Лист с формулами, содержащими циклические ссылки Рисунок 2 – Лист с расчетами общих затрат для каждого подразделения Присвоение имен. Кроме ссылки на ячейки с указанием адреса  возможен другой способ абсолютной адресации с помощью назначения имен, который позволяет значительно сократить время ввода и сделать понятной запись формул. Рассмотрим три способа создания имен. 1. Для присвоения имени активной ячейке (или диапазону) можно воспользоваться Диспетчером имен вкладки Формулы или просто нажать + для вызова  Диспетчера имен и щёлкнуть по кнопке Создать. В диалоговом окне Присвоение имени ввести имя в соответствующее поле (рисунок 3).   Рисунок - 3 – Процедура создания имен ячеек   2. Можно преобразовать в имена существующие заголовки строк и столбцов. Следующий пример демонстрирует создание имен ячеек с данными и формулами при использовании содержимого ячеек столбца слева. На рисунке 4 показан фрагмент рабочего листа для расчета выручки от продаж. 1)   Выделим диапазон, которому нужно присвоить имя, включая заголовки строк (A4:B8).     Рисунок 4 – Расчет выручки от продажи продукции   2)   На вкладке Формулы в группе Присвоенные имена выберем команду Создать из выделенного.   3)   В диалоговом окне Создание имен из выделенного диапазона укажем место, содержащее заголовки, установив флажок в столбце слева. Имя, созданное таким образом, относится только к ячейкам, содержащим значения, а не к существующим надписям строк и столбцов.     Рисунок 5 – Создание имен из выделенного диапазона Рисунок 6 – Пример использования имен в формулах 3. Существует и более быстрый способ: выделив ячейку, диапазон ячеек или несмежный диапазон, щелкнуть в поле имен и ввести имя, которое будет использоваться для ссылки на выделенный фрагмент, нажать клавишу .   Щелкните поле Имя у левого края строки формулы Поле "Имя"   Выбирая имя для диапазона или константы, можно пользоваться прописными и строчными буквами, например: ОбщиеЗатраты, что значительно облегчает прочтение имени. При этом не рекомендуется употреблять только прописные буквы, так как прописными буквами традиционно записываются названия функций Excel, например, СУММ, СРЗНАЧ. Следует помнить, что при изменении содержимого ячейки в режиме редактирования ей нельзя присвоить имя. При использовании имен сокращается время набора и сводится к нулю вероятность ошибок при вводе имени. Удобно и то, что не нужно запоминать задаваемые имена: все они находятся в списке. Список имен диапазонов и ячейки, которым они соответствуют можно вывести на текущий лист. Для этого нужно нажать клавишу для вызова окна Вставка имени, а затем щелкнуть мышью кнопку Все имена. Для выбора всех ячеек из поименованного диапазона достаточно установить курсор в одну из ячеек и нажать комбинацию клавиш +<*>. Формулы в Excel Формула в электронной таблице представляется выражением, состоящим из операндов. При этом все формулы должны начинаться со знака равенства. Знак равенства указывает Excel на то, что далее следует формула. Мы уже использовали формулы при описании циклических ссылок и использовании в формулах имен ячеек и диапазонов. Для соединения операндов в формуле можно использовать арифметические операторы: плюс (+) и минус (-), знаки умножения (*) и деления (/), операции отношения, например, не меньше (>=), равно (=) и некоторые другие символы. Можно использовать знак % при использовании в формулах конкретных числовых значений, выраженных в процентах. Например, = 10%+20% Результат в этом случае будет иметь процентный формат 30%. Если при этом числовой текст заключен в двойные кавычки,  то результатом применения формулы   ="10%"+"30%" будет является число 30 (без знака процента). При выполнении сложения Excel автоматически преобразует числовой текст в числовые значения. Последовательность вычислений в формулах определяется приоритетом операций. При этом Excel руководствуется следующими правилами: 1)  в первую очередь вычисляются выражения внутри круглых скобок; 2) операции умножения и деления выполняются раньше сложения и вычитания; 3)  операторы с одинаковым приоритетом выполняются последовательно слева направо. Для изменения порядка выполнения операторов используются круглые скобки. Если в формуле количество закрывающих и открывающих круглых скобок не совпадет, редактор выведет сообщение о несоответствии скобок и выделит ошибочную часть формулы. Сразу после ввода закрывающей скобки Excel отображает последнюю пару скобок полужирным шрифтом. Это удобно при вводе длинных формул, когда особенно трудно отследить соответствие скобок. Перемещение и копирование формул. Важно понимать, что может произойти со ссылками (как с абсолютными, так и с относительными) при перемещении формулы путем вырезания и вставки или копирования и вставки. ·   При перемещении формулы содержащиеся в ней ссылки не изменяются вне зависимости от используемого вида ссылок на ячейки. ·   При копировании формулы ссылки на ячейки в ней могут измениться в зависимости от того, какой тип ссылок используется. Для перемещения формулы нужно установить курсор в ячейке с формулой, нажать кнопку Вырезать  в группе Буфер обмена на вкладке Главная. Табличный редактор позволяет копировать формулы одним из двух способов: 1. С помощью буфера обмена нажатием клавиш + или + для копирования в буфер и + или + для вставки из буфера обмена; 2. С помощью кнопок   и на вкладке Главная в группе Буфер обмена. Чтобы вставить формулу вместе с форматированием, на вкладке Главная в группе Буфер обмена нужно нажать  кнопку Вставить . Чтобы вставить только формулу, на вкладке Главная в группе Буфер обмена нужно щелкнуть  по очереди элементы Вставить и в открывшемся окне Параметров вставки элемент Формулы  (рисунок 8). Можно вставить только значения формулы. Для этого на вкладке Главная в группе Буфер обмена выберите команды Вставка и параметр Значения  (рисунок 7).   Рисунок 7 – Параметры вставки Быстрым и наиболее удобным способом копирования формул является копирование с помощью маркера заполнения. Для этого нужно выделить ячейку с формулой, установить указатель мыши на маркере заполнения (черный квадрат в нижнем правом углу ячейки) . При расположении указателя мыши на маркере заполнения, он принимает вид черного крестика. Затем нужно протащить маркер в нужном направлении (в ячейки, куда копируется формула) и отпустить кнопку мыши. При копировании все относительные ссылки соответственно меняют свои значения с сохранением связей, а абсолютные ссылки не изменяются. Во избежание ненужных вычислений Microsoft Office Excel автоматически пересчитывает формулы только при изменении влияющих на формулу ячеек. Это является стандартной процедурой при первом открытии книги и ее редактировании. Однако тем, как и когда Excel будет пересчитывать формулы, можно управлять с помощью соответствующих настроек меню Параметры/ Формулы вкладки Файл. Встроенные функции MS Excel Функция — это заранее определенная формула, которая оперирует с одним или несколькими параметрами и возвращает значение. Многие из функций Excel являются краткими вариантами часто используемых формул. Например, чтобы сложить ряд значений ячеек с помощью функции СУММ(), достаточно просто выделить нужный диапазон. Очевидно, что функция СУММ() делает формулу значительно короче и более удобной для использования: сравните формулу =А1+А2+А3+А4+А5+А6+А7+А8+А9+А10 с функцией =СУММ(А1:А10). Каждая функция имеет имя и аргументы, заключенные в круглые скобки. В предыдущем примере А1:А10 является аргументом функции СУММ(). Если используются несколько аргументов, то они разделяются символом «точка с запятой» (;), например,     =ЕСЛИ(А>В; А+В; А-В). Между аргументами и скобками не должно быть пробела. В функциях можно использовать до 30-ти аргументов, при этом общая длина формулы не должна содержать более 1024 символов. Если функция не содержит аргументов, то все равно после имени функции ставятся пустые скобки, например, =ПИ(), СЕГОДНЯ(). Допускается использование вложенных функций, при этом вложенная функция будет являться аргументом для внешней функции. MS Excel позволяет использовать 7 уровней вложенности, например, =ABS(CP3HAЧ(B1;CУMM(D1:D10))). Следует обращать внимание на то, что количество открывающих и закрывающих скобок должно быть одинаковым. Если при вводе функции, пропущена одна из скобок, Excel исправит ошибку после нажатия клавиши в тех случаях, когда очевидно, где скобка должна находиться. Однако не стоит полагаться на автоматическое исправление допущенных ошибок, так как часто такие исправления могут оказаться некорректными и привести к ошибочным результатам. Вместо ввода полного имени функции полезно использовать средство Автозавершение формул Excel 2010. В строке формул после начала ввода первых символов открывается список имен функций. Теперь достаточно выбрать в списке нужную функцию и нажать , или же дважды щелкнуть левой кнопкой мыши на имени функции.  Библиотека функций  Microsoft Excel 2010 несколько расширена по сравнению с более  ранними версиями редактора, изменены имена и вычислительные алгоритмы некоторых функций таких, например, как БЕТАРАСП, ОСТАТ, СЛЧИС, ОКРВВЕРХ и др.  Для обеспечения обратной совместимости переименованные функции доступны также и по их старым именам в категории Функции совместимости. Способы вызова этой категории функций показаны на рисунках 8 и 9.   Рисунок 8 – Окно Мастера функций Рисунок 9 – Вызов списка функций совместимости Использование возможностей Мастера функций. Для работы с функциями в MS Excel используется Мастер функций, который вызывается командой  Вставить функцию вкладки Формулы или щелчком по кнопке  в строке формул.      Мастер функций позволяет построить функцию за два шага: 1. На первом шаге в диалоговом окне Мастера выбирается категория и функция в выбранной категории. Если пользователь не знает, к какой категории относится функция, то следует просмотреть полный алфавитный перечень имен функций. 2. На втором шаге задаются аргументы функции (обычно в виде ссылок на ячейки или их диапазоны, или имен соответствующих ячеек и диапазонов). Для этого необходимо установить курсор ввода в поле, после чего мышью выделить ячейку или диапазон ячеек. Ссылку на ячейки можно ввести вручную с клавиатуры, однако наиболее надежным способом является выделение ячеек с помощью курсора мыши. В MS Excel используются  13 категорий функций: финансовые, даты и времени, математические, статистические, ссылок и массивов, текстовые, работы с базой данных, логические, проверки свойств и значений, определенные пользователем, инженерные, аналитические, совместимости, а также полный алфавитный перечень и десять недавно использовавшихся функций. Большую коллекцию мощных статистических функций и команд содержит Пакет анализа MS Excel, одни из которых рассчитаны на узкий круг специалистов, а другие имеют широкую сферу применения. Для использования возможностей Пакета анализа необходимо загрузить соответствующую надстройку (меню Файл/ Параметры/Надстройки) и в окне Надстроек установить флажок Пакет анализа. Ошибочные значения. Ошибочное значение — это результат формулы, которую Excel не может разрешить. Например: #ДЕЛ/0!, #ЗНАЧ!, #ССЫЛКА!, #Н/Д, #ЧИСЛО!. В таблице 1 приведено краткое описание причин ошибок, возникающих при использовании формул и встроенных функций.   Таблица 1 – Описание информации об ошибках, возвращаемых формулами Ошибка Описание причины #ДЕЛ/0!          Попытка деления на ноль. Эта ошибка обычно связана с тем, что в формуле делитель ссылается на пустую ячейку. #ЗНАЧ!           Введена математическая формула, которая ссылается на текстовое значение. #ССЫЛКА! Отсутствует диапазон ячеек, на который ссылается формула. #Н/Д Нет данных для вычислений. При построении модели можно ввести  #Н/Д в ячейки для того, чтобы показать, что они зарезервированы для ожидаемых в дальнейшем данных. Любая формула, которая ссылается на ячейки, содержащие значение #Н/Д, возвращает значение #Н/Д. #ЧИСЛО! Задан неправильный аргумент функции. #ЧИСЛО! может также указывать на то, что значение, возвращаемое формулой слишком велико или слишком мало, и не может быть представлено на листе. В MS Excel 2010 существуют дополнительные возможности для поиска и анализа ошибок с помощью группы команд Зависимости формул вкладки Формулы. Элементы этой группы позволяют выявить влияющие и зависимые ячейки, найти ошибки в формулах, показать этапы вычислений, произвести пошаговую отладку путем вычисления отдельных компонентов, отобразить введенные формулы на рабочем листе. Пример использования элемента Влияющие ячейки приведен на рисунке 10. Для определения зависимых ячеек, выделим ячейку А1 и выберем команду Зависимые ячейки в группе Зависимые ячейки вкладки Формулы. Excel стрелкой укажет на зависимую ячейку. В нашем примере это ячейка А4. Одна ячейка может влиять на несколько ячеек рабочего листа. При однократном выполнении команды Зависимые ячейки (один щелчок мышью) Excel укажет на ячейки, напрямую зависящие от активной. При многократном щелчке по кнопке Зависимые ячейки отобразятся все формулы, для которых требуется значение выделенной ячейки. Точно также можно определить влияющие ячейки с помощью команды Влияющие ячейки.   Рисунок 10 – Пример отображения зависимых ячеек Следующий пример демонстрирует отображение влияющих ячеек для данных, хранящихся на разных листах. Выделим ячейку с формулой для расчета прибыли после уплаты налога (А3) на втором рабочем листе. Выберем команду Влияющие ячейки в группе Зависимости формул вкладки Формула. На экране появится пунктирная линия со стрелкой и пиктограмма электронной таблицы, которая указывает, что влияющие ячейки находятся на другом листе. Двойной щелчок мышью на пунктирной линии выводит на экран диалоговое окно Переход. Можно выбрать любую влияющую ячейку из списка, и Excel осуществит переход на лист с влияющей ячейкой.   Рисунок 11 – Исходные данные на Лист1   Рисунок 12 – Вычисление величины прибыли после уплаты налога и отображение влияющих ячеек на Лист2   Рисунок 13 – Вид окна Переход Применение формул к массивам данных Массив в электронных таблицах представляется в виде вектора – строки, вектора - столбца или в виде двумерной таблицы. Можно применять формулы сразу ко всем элементам массива. Вычисления с массивами, определенные формулой, выполняются покомпонентно. Возможно редактирование любого из элементов исходного массива, однако нельзя изменить или удалить часть массива, вычисленного по формуле. Формула массива содержит в себе массив значений или ссылку на массив ячеек. Например, нужно получить вектор B1:B4 путем удвоения элементов массива A1:A4. Для решения задачи нужно ·   выделить диапазон B1:B4; ·   ввести формулу =A1:A4*2 в строку формул, и вместо клавиши нажать комбинацию клавиш  . Выполняется это следующим образом: нажмите клавишу и, не отпуская ее, нажмите ; удерживая нажатыми обе клавиши, нажмите ; отпустите все три клавиши. Если в строке формул выражение заключено в фигурные скобки — это признак того, что Excel интерпретирует введенную формулу как формулу массива (рисунок 14). Поскольку Excel рассматривает массив как один цельный элемент, нельзя перемещать или удалять часть массива. Подобная ошибка является наиболее распространенной. Можно изменять исходные значения в столбце А, но при попытке изменить значения в столбце В,  Excel выдаст ошибку «Нельзя изменять часть массива». При работе с элементами всего массива, нужно выделять его целиком.  Чтобы уменьшить размер массива, к которому применена формула, нужно выделить весь массив, перейти в строку формул и нажать комбинацию клавиш , чтобы преобразовать содержимое строки в обычную формулу. После этого нужно выделить меньший диапазон и повторить ввод формулы массива.   Рисунок 14 – Применение формул к элементам массива Рассмотрим пример, использования имен в формулах. Рассмотрим пример расчетов за переговоры с учетом 30% скидки при длительности разговоров свыше 19 минут. Рисунки  15 и 16 демонстрируют вставку имени диапазона в формулу и применение формулы к массиву значений. Для того, чтобы вставить имя в формулу  можно воспользоваться командой Использовать в формуле из группы команд Определенные имена вкладки Формулы или просто нажать клавишу .  Рисунок 15 – Использование имен диапазонов в формулах   Чтобы правильно ввести формулу, нужно выделить диапазон результатов, ввести формулу и  нажать комбинацию клавиш (рисунок 16). Формула примет следующий вид: {=ЕСЛИ(Длительность_разговора__мин.>=20;Сумма_к_оплате*0,7;Сумма_к_оплате)} Результат расчета приведен на рисунке 17. Рисунок  16 – Применение формул к поименованному диапазону рабочего листа Рисунок 17 – Расчет массива значений с использованием имен диапазонов Следует отметить, что при использовании функций, которые возвращают результат в виде массива, должен быть выделен такой диапазон, чтобы в нем разместился весь результирующий массив, который возвращает функция. Сама функция должна быть введена как формула массива. Многие функции табличного редактора используют массивы в качестве аргументов (ВПР(), ГПР(), ПОИСК(), СУММПРОИЗВ() и др.),  возвращают массив как результат (например, ЛГРФПРИБЛ(), ЛИНЕЙН(), МУМНОЖ()), либо то и другое одновременно ( ИНДЕКС(), ТЕНДЕНЦИЯ(), РОСТ(), ТРАНСП()). Пример. В таблицах приведены данные о цене товаров и объемы продаж (рисунок 18). Требуется рассчитать итоговую выручку от  продаж за первый квартал. В диапазон Е10:Е12 введена формула для расчета значений элементов массива. В данном примере используется функция ТРАНС(), позволяющая преобразовать строку в столбец для выполнения матричных операций.   Рисунок 18 – Расчет итоговой выручки от  продаж за первый квартал   Команды меню Условное форматирование Меню Условное форматирование группы Стили позволяют задать определенный формат диапазона ячеек в зависимости от их содержимого. В соответствии с правилами выделения ячеек и возможностью управления правилами использование для оформления ячеек в специальных наглядных средств. При этом, если одна или несколько ячеек в диапазоне содержат формулу, возвращающую ошибку, условное форматирование к ним применяться не будет. Чтобы гарантировать применение условного форматирования ко всему диапазону, рекомендуется использовать функции ЕСЛИ или ЕСЛИОШИБКА для возврата указанного в параметрах значения (например, 0 или «Н/Д»), отличного от ошибки. На рисунке 19 показано окно команд меню Условное форматирование. Правила выделения ячеек позволяют устанавливать различные условия для наглядного оформления диапазонов данных. Пример использования форматирования (выделения цветом) ячеек активного диапазона со значениями выше среднего представлен на рисунке 20.   Рисунок 19 - Команды Условного форматирования Рисунок 20 - Использование Условного форматирования в соответствии с правилом отбора   Можно создавать собственные правила выделения ячеек. Для этого следует в меню Условное форматирование выбрать команду Правила выделения ячеек и далее  Другие правила. В окне Создание правила форматирования выбирают тип и описание (условие) правила (рисунок 21).  В этом же окне при необходимости изменяют формат ячеек, щелкнув по кнопке Формат. Форматирование только первых или последних значений позволяет найти максимальное и минимальное значения в диапазоне ячеек на основе указанного порогового значения. Например, по данным отчета можно найти 3 самых популярных вида тарифов сотовой связи, 10 % наименее популярных продуктов в клиентском опросе или 25 самых высоких зарплат в отчете по персоналу.   Рисунок 21 - Создание правила форматирования Excel позволяет в качестве условия для определения форматируемых ячеек использовать формулы. В качестве примера приведем заливку желтым цветом все ячейки диапазона, если среднее значение диапазона меньше, чем значение в ячейке E4 и сумма меньше E6. В окне Создание правил форматирования выбираем тип правила Использовать формулу для определения форматируемых ячеек. В поле описания правила введем формулу для определения форматируемых ячеек (рисунок 22): =И(СРЗНАЧ($I$4:$I$15)<$E$4;СУММ($I$4:$I$15)>$E$6) Кнопка Образец позволяет задать цвет заливки. Рисунок 22 – Использование формул для условного форматирования После щелчка по кнопке ОК в случае, если формула возвращает значение ИСТИНА, диапазон ячеек «Количество заявок» будет выделен цветом, в противном случае форматирование не будет выполнено. Обратите внимание, если изменяются значения ячеек активного диапазона или ячеек вне диапазона на которые ссылаются формулы, то автоматически будут изменяться и результаты форматирования. В примере на рисунках 26 и 27 при значении E4 равном 4 результат вычисления функции  ЛОЖЬ. Если изменить значение в ячейке, например на 8, диапазон I4:I15 будет выделен цветом, так как в этом случае функция принимает значение ИСТИНА. Для того чтобы изменить условия форматирования (например, формулу), нужно воспользоваться командой Управление правилами меню Условное форматирование.   Рисунок 23 – Результаты форматирования с использованием функций Команды условного форматирования позволяют по желанию пользователя выбрать вариант оформления ячеек с данными с помощью использования Гистограммы, Цветовой шкалы и Наборов значков. Гистограммы  помогают рассмотреть значение в ячейке относительно других ячеек. Длина столбца гистограммы соответствует значению в ячейке. На рисунке 24 представлен пример условного форматирования с помощью Гистограммы. Рисунок 24 - Пример использования команды «Гистограммы» Цветовые шкалы — это визуальные элементы, которые помогают понять распределение и разброс данных.  Выделенные ячейки с разными значениями могут быть окрашены соответствующими цветами из выбранной цветовой шкалы щелчком левой клавиши мыши на соответствующем элементе двух- или трехцветного градиента (рисунок 25). Рисунок  25 – Пример использования цветовой шкалы для условного форматирования Наборы значков используется для аннотирования и классификации данных по трем-пяти категориям, разделенным пороговым значением. Каждый значок соответствует диапазону значений. Например, в наборе значков 3 стрелки: красная стрелка вверх соответствует высоким значениям, желтая стрелка, направленная в сторону, соответствует средним значениям, а зеленая стрелка вниз соответствует низким значениям. Можно создавать собственные наборы значков или настроить показ значков только для ячеек, соответствующих определенным условиям (например, показ значка предупреждения для ячеек, значения которых находятся ниже критического уровня, и отсутствие значка для ячеек, превышающих этот уровень). Предположим, что в рассматриваемом примере значком должны быть помечены только ячейки со значениями более 500. Для этого в меню Условное форматирование/ Наборы значков выбираем элемент Другие правила. При настройке условий скроем ячейки, выбрав вариант Нет значка ячейки в раскрывающемся списке напротив нужного значка. Для ячеек, которые нужно пометить задаем условие, значение критерия и тип (рисунок 26). Результат применения созданного правила форматирования приведен на рисунке 27. Рисунок 26 – Создание правила форматирования Рисунок 27 – Результат применения правила форматирования Рисунки  демонстрирует создания правила форматирования и возможности одновременного применения цветовых шкал и пользовательских значков для аннотирования данных. Рисунок 28 – Окно создания правила форматирования Рисунок 29 – Пример совместного использования инструментов условного форматирования «Набор значков» и «Цветовые шкалы» Для создания собственных правил, их удаления и изменения выбирают пункт Управление правилами меню Условное форматирование на вкладке Главная в группе Стили. На экран появится диалоговое окно Диспетчер правил условного форматирования (рисунок 30). Рисунок 30 – Окно Диспетчера правил условного форматирования Вопросы для самопроверки 1. Перечислите основные форматы ячеек и способы их изменения. 2. Опишите способы редактирования и копирования формул? 3. Что называется точкой вставки? 4. Для чего используются ссылки? 5. Что означает циклическая ссылка? 6. Когда следует использовать относительные ссылки?     7. В каком случае используются абсолютные ссылки? 8.  Как преобразовать относительную ссылку в абсолютную? 9.  Для чего используются смешанные ссылки? 10. Какие возможности предоставляют пользователю внешние ссылки? 11. Перечислите и поясните способы  присвоения имен ячейкам и диапазонам? 12.  Как ввести формулу в MS Excel? 13. Опишите особенности применения формул к массивам. 14. Поясните назначение и возможности условного форматирования. ФПакеты прикладных программ Конспект лекций назад | содержание | вперед ЛЕКЦИЯ 4 АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СПИСКОВ Технология обработки данных в информационных системах предполагает их хранение в электронных базах данных. В табличном редакторе база данных представляется в виде списка. Результаты обработки данных в списках могут использоваться для принятия управленческих решений различного уровня. Понятие о списке Список представляет собой базу данных (БД) для хранения и извлечения данных и к нему применимы те же требования, что и к реляционным БД. Строки в базе данных называются записями, а столбцы – полями. Метки столбцов рассматриваются как имена полей, а отдельные строки в документе – как записи. Записи можно просматривать, редактировать, добавлять или удалять. В работе со списками используются такие операции обработки данных как сортировка, группировка и фильтрация (отбор данных по критерию), структурирование и консолидация данных. Для достижения наибольшей эффективности при работе со списками необходимо следовать некоторым правилам: 1. В столбце размещается информация одного типа. 2. В первой строке вводятся заголовки столбцов списка (полей записей). Каждый столбец списка должен быть поименован. 3. В списке отсутствуют пустые строки и столбцы. 4. Список желательно размещать на отдельном листе. 5. Список должен быть отделен от других данных на рабочем листе пустыми строками и столбцами. Это позволяет Excel автоматически определять диапазон списка, если курсор находится в области списка (одна из ячеек списка является активной) . Сортировка списка Информация в базе данных изначально не упорядочена. MS Excel предоставляет средства сортировки баз данных, т.е. упорядочение полей данных по одному или нескольким полям (критериям). Можно сортировать строки или столбцы по возрастанию или убыванию, с учетом или без учета регистра. При сортировке строк меняется расположение строк в списке, в то время как порядок столбцов остается неизменным. При сортировке столбцов меняется порядок расположения столбцов, а порядок строк не изменяется. Для выполнения сортировки следует установить курсор в список и выполнить команду Сортировка и фильтр группы команд Редактирование вкладки Главная или соответствующую команду из группы команд Сортировка и фильтр на вкладке Данные. Если выбран вариант Сортировка от А до Я или Сортировка от Я до А, то ключом сортировки будет столбец, в котором установлен курсор. Выбор пункта Настраиваемая сортировка открывает диалоговое окно Сортировка. Пользователь может отсортировать список по нескольким ключам, добавляя уровни сортировки с помощью кнопки Добавить уровень. Кнопка Параметры... диалогового окна Сортировка, позволяет  задать дополнительные условия сортировки: осуществлять сортировку по строкам диапазона или по столбцам, с учетом или  без учета регистра (рисунок 1). Рисунок 1 – Диалоговое окно Сортировка Отбор данных в списке Фильтрация – это выделение из базы данных подмножества данных в соответствии с заданными условиями отбора, или критериями, для последующей работы с ним. В MS Excel существует два способа фильтрации списка: с помощью автофильтра и расширенного фильтра. Автофильтр Для создания простых условий отбора записей в списке (одно –два отношения, связанных одним из операторов И, ИЛИ) удобно использовать автофильтр (команда Фильтр вкладки Данные). При использовании автофильтра строки списка, не удовлетворяющие заданным условиям, будут скрыты, и на рабочем листе останутся только те записи, которые соответствуют критериям отбора. Автофильтр можно применять к любому количеству столбцов и, таким образом, осуществлять последовательную фильтрацию. Автофильтр позволяет выделить наборы записей с помощью раскрывающихся списков в столбцах. Элементы, которые отображаются в каждом раскрывающемся списке, называются критериями фильтра. Кроме любого из значений активного столбца в раскрывающемся поле со списком, можно выбрать варианты, позволяющие осуществить сортировку списка. При щелчке на элементе в раскрывающемся списке столбца автофильтра Excel отображает только те записи, которые содержат выбранный элемент в данном поле.   Рисунок 2 – Окно Автофильтра   Если поле, по которому осуществляется отбор записей, содержит числовые значения, то окно Автофильтра позволяет задать различные критерии для отбора записей с помощью элемента Числовые фильтры (рисунок 3). Для текстовых полей окно автофильтра включает элемент Текстовые фильтры, который позволяет задавать критерии отбора с учетом специфики поиска для этого типа данных. Рисунок 3 – Окно раскрывающегося списка автофильтра Рисунок 4 – Окно Пользовательский фильтр На рисунке 5 приведен пример использования Автофильтра для отбора записей о работниках со стажем от 10 до 15 лет с помощью Пользовательского автофильтра. Рисунок 5 – Результат отбора записей с помощью расширенного фильтра Для отмены Автофильтра конкретного столбца нужно щелкнуть по кнопке  и в списке значений выбрать пункт Все… Для отмены режима фильтрации и возврата к исходному списку следует щелкнуть по элементу Фильтр или Очистить из группы Сортировка и фильтр вкладки Данные. Удалить все заданные фильтры можно также, щелкнув на значке  меню Сортировка и фильтр вкладки Главная. Следует помнить, что отфильтрованный с помощью Автофильтра список автоматически размещается на месте исходного. Для того, чтобы сохранить результаты в другом месте текущего листа рабочей книги или на другом рабочем листе, следует скопировать отфильтрованный список и вставить в нужный диапазон. Отбор данных в списке с помощью расширенного фильтра Возможности отбора записей с помощью расширенного фильтра гораздо шире. Расширенный фильтр позволяет организовать отбор записей базы данных по более сложным по сравнению с Автофильтром критериям. Отобранные данные могут размещаться как в области исходного списка (переключатель Фильтровать на месте), так и в новом диапазоне рабочего листа.  Замечание. Чтобы применить расширенный фильтр, столбцы списка должны обязательно иметь заголовки. Расширенный фильтр позволяет: ¾ задать условия, соединенные логическими операторами для нескольких полей базы данных; ¾ задать три и более условий для конкретного поля с использованием, по крайней мере, одного логического оператора; ¾ задать вычисляемые условия для организации фильтрации; ¾ извлекать из базы данных записи и вставлять копии этих строк в новый диапазон рабочего листа. Отбор данных с помощью Расширенного фильтра требует создания дополнительной таблицы условий отбора записей в отдельном диапазоне ячеек (таблицы критериев). Этот диапазон должен содержать, по крайней мере, 2 строки: ¾ в верхней строке размещаются метки полей, по которым осуществляется отбор (они должны точно совпадать с метками полей в исходном списке, поэтому их желательно копировать); ¾ во второй и последующих строках диапазона таблицы критерия записываются условия отбора.   Порядок отбора данных с помощью расширенного фильтра:  1. Скопировать заголовки столбцов списка, которые должны отражаться в результатах, в свободный диапазон рабочего листа. 2. Скопировать заголовки критериев отбора в незаполненные смежные ячейки. Предусмотрите хотя бы одну свободную клетку под заголовком каждого из критериев для ввода условий отбора. 3. Ввести в ячейки под заголовками критериев требуемые условия отбора. 4. Установить курсор в список. В этом случае Excel автоматически определит диапазон списка. 5. Выбрать меню Фильтр / Дополнительно вкладки Данные. 6. В диалоговом окне Расширенный фильтр ¾ установить переключатель Обработка в положение Скопировать результат в другое место, иначе отобранные записи будут размещены на месте исходного списка; ¾ в поле Исходный диапазон проверить правильность диапазона исходного списка (при необходимости ввести адрес диапазона ячеек, содержащего исходный список с заголовками столбцов); ¾ в поле Диапазон критериев ввести ссылку на диапазон условий отбора; ¾ в поле Поместить результат в диапазон ввести диапазон заголовков столбцов результирующей таблицы, созданной в п.1.   Примеры условий отбора расширенного фильтра Отбор записей, удовлетворяющих трем и более условиям для одного поля списка. В этом случае критерии вводятся в ячейки  смежных строк (т.е. в столбец). Например, ФИО Романов Петров Соболева   Отбор записей, удовлетворяющих одновременно нескольким условиям отбора для двух или более столбцов списка (логическое «И»). Для одновременного выполнения условий отбора записей критерии вводят в ячейки, расположенные в одной строке диапазона условий. Например,  Пол Оклад, руб. м >20000       Отбор записей, удовлетворяющих одному из нескольких условий для разных полей списка (логическое «ИЛИ»). В этом случае критерии отбора вводятся в ячейки, расположенные в разных строках диапазона условий.   Пол Оклад, руб. ж     <15000   В следующем примере показана таблица критериев для поиска записей об инженерах со стажем менее 10 лет и техников со стажем более 10 лет.   Должность Стаж инженер <10 техник >10   Значение, возвращаемое формулой. В условии фильтрации можно использовать значение, возвращаемое формулой. В этом случае в таблице критериев в качестве заголовка условия  нельзя использовать заголовок столбца списка. Заголовок поля критерия должен отличаться от любой из меток полей списка. Для приведенного ниже примера критерия отбора в результате фильтрации будут отображены строки, в которых значение  столбца F списка превышает среднее значение в ячейках E2: E15. Таблица критериев содержит две ячейки, одна из которых пустая  (поле заголовка условия не заполнено).    =D4>СРЗНАЧ($D$4:$D$23)   Можно также рассчитать среднее значение по формуле СРЗНАЧ() в любой из ячеек рабочего листа (например, в I2), и затем использовать ссылку на эту ячейку в условии отбора.  Оклад больше среднего =F2>$I$2   После ввода формулы в ячейке со значением критерия в зависимости от результата первой проверки появится логическое значение ЛОЖЬ или ИСТИНА. Замечания. 1. Для ячеек вне столбца, по которому осуществляется отбор данных, используются абсолютные ссылки! 2. Формула в условии отбора должна ссылаться либо на заголовок столбца (например, «Стаж»), либо на соответствующее поле в первой записи. При использовании заголовка столбца в формуле условия в ячейке вместо логического значения будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации. 3. Для создания составного вычисляемого критерия можно использовать функции И, ИЛИ, НЕ. Например, =И(В2<100; C2<500).   Пример 1. Размер заработной платы сотрудников фирмы зависит от уровня их компетенции и грейда. На основе этих показателей рассчитывается рейтинг сотрудника, пропорционально которому распределяется ФОТ. На рисунке  32 приведен пример заполнения списка с рассчитанными суммами ЗП сотрудников. В ячейке D1 введена величина ФОТ, а в ячейке F24 рассчитана общая величина рейтинга. Сумма заработной платы в ячейке G4 вычисляется по формуле:     =$D$1*F4/$F$24. Выведем сведения о менеджерах с рейтингом более единицы.   Порядок действий:  1. Создадим исходный список в диапазоне A3: G23 (рисунок 6) и рассчитаем значения в столбце G. 2. В диапазоне I3:J4 создадим таблицу критериев с заданными условиями отбора. 3. Скопируем заголовки столбцов, которые должны отображаться в результатах в диапазон I7:K7. 4. Установим курсор в список и выберем команду Дополнительно из группы Сортировка и фильтр вкладки Данные. 5. Заполним окно Расширенный фильтр (рисунок 7) и щелкнем по кнопке ОК. Рисунок 6 – Пример заполнения рабочего листа с таблицей критериев и заголовками результирующего диапазона Рисунок 7 – Диалоговое окно Расширенный фильтр Рассмотрим последовательность действий при использовании расширенного фильтра для отбора данных по вычисляемому условию.   Пример 2. Из списка для расчета З/П на основании рейтинга выберем записи о сотрудниках, чья  зарплата превышает среднюю величину. Для этого: 1. Скопируем список, созданный в предыдущем примере на новый рабочий лист. 2. В ячейке I4 рассчитаем среднее значение З/П (рисунок 8) с использованием функции   =СРЗНАЧ(D4:D23). 3. В ячейках I6:I7 рабочего листа создадим таблицу критериев для отбора записей. Рисунок 8 – Вид рабочего листа с таблицей критериев отбора записей по вычисляемому условию После ввода формулы таблица критериев примет вид, как на рисунке 8.   4. Установим курсор в список и выберем команду Дополнительно из группы Сортировка и фильтр вкладки Данные. 5. В окне Расширенный фильтр установим переключатель Обработка в положение Скопировать результаты в другое место. 6. В поле Исходный диапазон введем диапазон ячеек, содержащий исходный список вместе с метками столбцов (A3:G23). 7. Введем в поле Диапазон критериев ссылку на диапазон таблицы критериев (I6:I7). 8. В поле Поместить результат в диапазон укажем верхнюю левую ячейку области вставки нового списка (рисунок 9). Рисунок 9 – Диалоговое окно «Расширенный фильтр» Вопросы для самопроверки 1. Чем база данных отличается от любой другой таблицы, созданной в MS Excel? 2. Каким образом можно упорядочить записи в базе данных? 3. Какие способы фильтрации используются в MS Excel? 4. Каковы отличия Автофильтра и  Расширенного фильтра? 5. Как формируется диапазон критериев Расширенного фильтра? 6. Как организовать фильтрацию по вычисляемым условиям?   Пакеты прикладных программ Конспект лекций назад | содержание | вперед ЛЕКЦИЯ 5 Прочитав эту лекцию, Вы узнаете: ·   о способах подведения автоматических итогов по группам показателей; ·   о возможностях сводных таблиц и диаграмм для обобщения и анализа данных в списках; ·   о дополнительных вычислениях и добавлении расчетных полей в сводные таблицы. Использование итогов для анализа данных В экономических информационных системах часто необходимо  обобщить данные по группам показателей и произвести анализ по рассчитанным итогам. На основании полученных значений формируются отчеты, которые могут быть использованы для сравнительного анализа и принятия решений, а также передачи во внешние информационные системы. В MS Excel существует несколько способов получения обобщенных данных. Для подсчета итогов в списках в MS Excel, как правило, используют формулы и функции рабочего листа. Однако Excel позволяет создавать автоматические промежуточные отчеты командой Промежуточный итог из группы Структура вкладки Данные. В результате в список добавляются строки промежуточных итогов для выделенных групп элементов списка и строка общих итогов. При этом список структурируется, и пользователь получает возможность просмотра как детализированных данных, так и только итоговых строк.   Алгоритм создания простых промежуточных отчетов Подготовка данных для создания промежуточных отчетов: 1. Создать исходный список. 2. Отсортировать список по полю, в котором необходимо сгруппировать данные для расчетов промежуточных итогов. Excel вычисляет автоматические промежуточные итоги только на основании предварительно сгруппированных данных в выбранном поле. Создание промежуточного отчета: 1.   Установить курсор в список и выполнить команду Промежуточный итог вкладки Данные. 2.   В окне Промежуточные итоги выполнить следующие действия: ¾ в поле При каждом изменении в выбрать из списка метку столбца для которого подводятся итоги, т.е. столбец, по которому проводилась сортировка списка; ¾ из списка Операция выбрать функцию, необходимую для подведения итогов. Excel предлагает список из 11 операций (Сумма, Количество, Среднее, Максимум, Минимум, Произведение, Количество чисел, Смещенное отклонение, Несмещенное отклонение, Смещенная дисперсия, Несмещенная дисперсия); ¾ в поле Добавить итоги по выбрать метки столбца (столбцов), содержащих значения, которые будут участвовать в расчетах. Для расчетов итогов по нескольким полям базы данных в раскрывающемся списке поля Добавить итоги по диалогового окна Промежуточные итоги следует выбрать метки нужных столбцов. Можно применять различные функции к одному столбцу списка.   В диалоговом окне Промежуточные итоги (рисунок 38) пользователь может устанавливать с помощью флажков конец страницы между группами и итоги под данными. Если убрать флажок Итоги под данными, то строки промежуточных итогов будут размещаться над связанными с ними детализирующими данными, а строка общего итога – вверху списка. Чтобы каждая группа выводилась на отдельной странице нужно установить флажок  Конец страницы между группами. Удалить итоговые строки из списка можно нажатием кнопки Убрать все в диалоговом окне Промежуточные итоги. Рассмотрим примеры создания простых промежуточных отчетов.   Задание. На основании данных списка вычислить фонд оплаты труда в каждом цехе. Порядок действий: 1. Создайте на первом рабочем листе список, содержащий не менее 25 строк с информацией о сотрудниках предприятия (рисунок 1).   Список сотрудников Номер п/п ФИО Пол Стаж Подразделение Должность Оклад 1 Новиков П.Р. м 6 Технический отдел ст.инженер  25 000р. 2 Петров К.Н. м 2 Отдел маркетинга специалист  18 000р. 3 Иванова В.П. ж 18 Отдел маркетинга экономист  25 000р. 4 Соболева М.И. ж 15 Диспетчерский техник  13 000р. Рисунок 1– Фрагмент исходного списка 2. Подготовьте данные для создания промежуточных итогов. Для этого установите курсор в список и отсортируйте список по полю «Подразделение» (меню Сортировка вкладки Данные) (рисунок 2). Это необходимо для создания групп данных. Порядок сортировки может быть любым. Рисунок 2 – Пример диалогового окна Мастера сортировки 3. Установите курсор в список и выполните команду Промежуточные итоги вкладки Данные. Заполните  диалоговое окно Промежуточные итоги в соответствии с рисунком 3. Рисунок 3 – Пример диалогового окна Мастера промежуточных итогов После нажатия кнопки ОК диалогового окна на рабочем листе будет создана структура отчета: слева от названия столбцов – строка номеров уровней. Уровень детализации изменяется с помощью кнопок, расположенных левее номеров строк списка (номера 1,2,3 уровней структуры). Выбор 1-го уровня  позволяет увидеть на экране только строку общего итога, выбор 2-го уровня – строки промежуточных итогов и общего итога, 3-й уровень отображает все строки списка, включая  итоговые. К итоговым строкам можно применять различные действия, например, сортировать, форматировать, оформлять в виде печатных отчетов. Изменение уровня детализации используется для получения более наглядной информации об итоговых расчетах и построения графиков по итоговым значениям.   Рисунок 4 –Промежуточные итоги для каждого подразделения 1. Оставьте на экране только итоговые значения и постройте круговую диаграмму по полученным результатам. Для этого ¾ выделите столбец «Подразделение» (вместе с заголовком). Нажав и удерживая клавишу , выделите диапазон итоговых значений (без общего итога); ¾ в группе элементов Диаграммы вкладки Вставка выберите тип диаграммы (рисунок 5). Рисунок 5 – Диаграмма ФОТ (суммы окладов) для каждого подразделения   Для того чтобы добавить промежуточные итоги для нового расчетного поля, необходимо еще раз установить курсор в список и повторить команду Промежуточный итог. При этом флажок Заменить текущие итоги в диалоговом окне должен быть снят! На рисунках 6 и 7 приведен пример вычисления среднего стажа работников в каждом подразделении. Рисунок 6 –Диалоговое окно Мастера при добавлении новых промежуточных итогов Рисунок 7 – Промежуточные итоги для двух функций Создание сложных промежуточных отчетов Сложные промежуточные отчеты создаются в результате выполнения нескольких операций над разными показателями одновременно. Например, нужно определить ФОТ для каждого подразделения и средний стаж по каждой должности в каждом подразделении.   Порядок действий:  1. Список сортируют по нескольким ключам. В нашем примере в качестве первого (внешнего) ключа сортировки используем поле «Подразделение», а в качестве второго ключа – «Должность» (рисунок 8). Рисунок 8 – Пример диалогового окна Мастера сортировки по двум ключам 2. Создают промежуточные итоги по внешнему ключу (рисунок 9). Рисунок 9 – Создание промежуточных итогов для подразделений 3. Затем создают промежуточные итоги для всех подгрупп. При этом флажок Заменить текущие итоги должен быть сброшен.     Рисунок 10 – Диалоговое окно создания промежуточных итогов для поля Должность   Рисунок 11 – Фрагмент результирующего списка со сложными промежуточными итогами   Вопросы для самопроверки 1. Опишите алгоритм создания промежуточных итогов. 2. Какие операции используются при вычислении итогов? 3. Для чего используются уровни структуры? 4. Какие действия можно выполнять над итоговыми строками? 5. Как удалить итоговые строки из базы данных? Контрольное задание для самопроверки 1. Создайте произвольный список продаж товаров, включающий ФИО продавца, дату продажи, наименование товара, цену за единицу, количество проданного товара, сумму сделки. Определите общее количество товаров, проданное каждым продавцом за время работы и среднюю сумму продаж. 2. По рассчитанным данным постройте гистограмму средних значений сумм продаж. 3. Скопируйте исходный список на новый рабочий лист. Определите среднее количество товаров, проданное за каждый день работы каждым продавцом. Анализ итоговых значений групп показателей с помощью сводных таблиц Создание сводных таблиц в MS Excel  Промежуточные итоги значительно удлиняют список, поэтому для обобщения и анализа данных часто применяются сводные таблицы. Сводные таблицы позволяют выводить сведения, хранящиеся в БД, с различной степенью детализации. Сводная таблица может быть создана на основе данных  рабочего листа MS Excel,  нескольких рабочих листов книги, данных другой уже созданной сводной таблицы, внешней БД. МS Ехсеl позволяет создавать сводные таблицы на любом рабочем листе, причем на одном рабочем листе можно разместить несколько сводных таблиц. Для построения сводных таблиц используют возможности Мастера сводных таблиц, предоставляющего ряд диалоговых окон для осуществления всех шагов по выбору исходных данных и созданию макета сводной таблицы.   Порядок создания сводной таблицы  1. Выделить любую ячейку внутри списка, по данным которого будет создаваться сводная таблица. 2. Выбрать команду Сводная таблица вкладки Вставка (рисунок 12). Рисунок 12 – Меню Сводная таблица вкладки Вставка  В результате на экране появится диалоговое окно Создание сводной таблицы, в котором определяется: Þ на основе каких данных будет создаваться сводная таблица, то есть источник данных. В зависимости от источника данных будут зависеть дальнейшие шаги Мастера…. Рассмотрим вариант  Выбрать таблицу или диапазон. Þ определяется местоположение исходных данных (если была выделена ячейка внутри списка, то этот диапазон определяется автоматически, и пользователю следует лишь проконтролировать его правильность); Þ определяется местоположение отчета сводной таблицы: на новом или на текущем листе. В последнем случае в поле диалогового окна потребуется указать ссылку на левый верхний угол интервала размещения сводной таблицы. После нажатия кнопки ОК на рабочем листе появляется макет сводной таблицы, который заполняется в соответствии с условием задачи путем перетаскивания полей списка в области Строк, Столбцов и Данных макета. От того, каким он будет, зависит вид и наглядность сводной таблицы (рисунок 13). Рисунок 13 – Макет и панель Список полей сводной таблицы Замечание. При создании макета в область Строка следует перемещать метку того поля, которое содержит больше различных значений, чтобы сводная таблица не «растягивалась» вправо. Рассмотрим пример создания сводной таблицы для списка, представленного на рисунке 1. Задание. Определить ФОТ для каждой должности в каждом подразделении. Порядок действий:   1. На рабочем листе создайте список продаж из 25 записей. 2. Установите курсор в список. 3. Выполните команду Сводная таблица вкладки Вставка. 4. В окне Создание сводной таблицы указать диапазон с исходными данными и местоположение отчета сводной таблицы. Рисунок 14 – Окно Создание сводной таблицы 5. Заполнить макет сводной таблицы, разместив ·   в область Названия столбцов – поле Подразделение; ·   в область Названия строк - поле Должность; ·   в область суммарных значений - поле Оклад; ·   в область Фильтр отчета –поле Пол. Вид сводной таблицы представлен на рисунке 15. В каждую из областей можно перемещать метку не одного, а нескольких полей. В этом случае сводная таблица будет содержать дополнительные (промежуточные) итоги. Именно область Строка следует делать «вложенной». Поля, помещенные в область Значения, будут математически складываться в таблице. Щелчок по кнопке в области Значения открывает окно Параметры поля значений, в котором предоставляется возможность выбора функций для выполнения сводных вычислений.    Рисунок 15 – Сводная таблица Фильтр отчета позволяет выбрать подмножество значений полей, так что сводная таблица отразит вычисления только на основе этого подмножества. Например, можно выбрать итоговые данные по ФОТ для представителей только одного пола (рисунок 16). Рисунок 16 – Пример использования Фильтра отчета Для того, чтобы убрать заголовки областей нужно щелкнуть по элементу Список полей группы команд Показать. Сводную таблицу можно редактировать: ¾ вставлять, удалять, переупорядочивать поля или элементы; ¾ изменять способ вычисления; ¾ отображать или удалить промежуточные и групповые итоги; ¾ переименовать поля и элементы; ¾ изменять формат; ¾ скрывать или показывать детализирующие данные; ¾ группировать и сортировать элементы; ¾ иллюстрировать результаты с помощью диаграмм. Все эти действия выполняются с помощью меню Работа со сводными таблицами.  Щелчок в области сводной таблицы вызывает линейку меню Работа со сводными таблицами, включающую две закладки: Параметры и Конструктор.  Конструктор позволяет изменить вид сводной таблицы. Команды из группы Макет вкладки Конструктор позволяют вставить пустые строки после каждой группы, скрыть промежуточные или основные итоги, изменить макет отчета и стиль сводной таблицы. Работа с итоговыми строками. При создании сводных таблиц МS Ехсеl автоматически вставляет строки и столбцы промежуточных и групповых (общих) итогов. При подсчете итогов соблюдаются некоторые правила: ¾ значения в строке или столбце общего итога рассчитываются с помощью той же функции, которая задана в области Данные сводной таблицы; ¾ МS Ехсеl вычисляет общие итоги, не включая при этом скрытые элементы; ¾ общие итоги не зависят от промежуточных; ¾ для расчета промежуточных итогов можно использовать функцию, отличную от той, которая используется в области данных. Для изменения параметров нужно поместить курсор в область сводной таблицы и на вкладке Параметры выбрать нужную команду из соответствующей группы команд. Рассмотрим некоторые действия над сводной таблицей, выполняемых с помощью меню вкладки Параметры. Детализация данных в сводной таблице. МS Ехсеl позволяет детализировать любые данные сводной таблицы. Пользователь может проверить, из каких составляющих складывается та или иная величина. Для детализации итогов сводной таблицы нужно дважды щелкнуть на названии поля или воспользоваться командой контекстного меню Показать детали. На рисунке 17 приведен результат детализации сведений об инженерах диспетчерского отдела. Рисунок 17 – Детализация данных сводной таблицы Вычисления в сводных таблицах. Щелчок по кнопке Итоги по из группы команд Вычисления открывает список функций для расчета сводных значений команд. Excel мгновенно пересчитает значения в поле данных при выборе новой функции. Изменить вычислительную функцию можно также с помощью команды Параметры поля группы Активное поле (рисунок 18) или с помощью контекстного меню Параметры полей значений (рисунок 19). Вкладка Дополнительные вычисления диалогового окна Параметры поля значений добавляют новые вычислительные возможности (рисунок 20). Расчет процента ФОТ по каждой должности в каждом подразделении к общему итогу приведен на рисунке 21. Рисунок 18 – Изменение способа вычисления Рисунок 19 – Изменение способа вычисления с помощью команды контекстного меню Рисунок 20 – Список дополнительных вычислений Рисунок 21 – Результат вычисления процента от общей суммы Возможен еще один вариант доступа к дополнительным вычислениям. Группа команд Вычисления включает меню Дополнительные вычисления, который также открывает список возможных вариантов вычислений, необходимых для анализа данных (рисунок 22). Рисунок 22 – Элементы команды Дополнительные вычисления Рассмотрим следующий пример. Компания, производящая три вида продукции, реализует ее в несколько регионов. Заполним лист MS Excel данными о фактических и планируемых доходах от продаж (рисунок 23). Создадим сводную таблицу для расчета суммы доходов от поставок в каждый регион за каждый отчетный месяц и определим вклад каждой ячейки в общий доход от реализации. Установим курсор мыши в область данных сводной таблицы и воспользуемся кнопкой Дополнительные вычисления из группы команд Вычисления. На рисунке 24 представлен результат применения функции Индекс для отображения важности (рейтинга) каждой ячейки относительно общего итога. Рисунок 23 – Отчет о реализации продукции Рисунок 24 – Результат применения функции Индекс Вычисляемое поле. С помощью команды Вычисляемое поле меню Поля, элементы и наборы можно ввести формулу для расчета значений дополнительного поля и подвести по этому полю сводные итоги (рисунок 25). Фрагмент сводной таблицы с вычисляемым полем для рассмотренного выше примера приведен на рисунке 25. Здесь в качестве вычисляемого поля добавлено поле  Разница, отражающего разницу между планируемым и реальным доходом. В окне Вставка вычисляемого поля вводится имя нового поля и формула для вычислений. Вставка имени поля в формулу выполняется кнопкой Добавить. Фрагмент сводной таблицы с итогами по вычисляемому полю приведен на рисунке 26.     Рисунок 25 – Вставка вычисляемого поля Рисунок 26 – Фрагмент сводной таблицы с вычисляемым полем Изменение формата. Чтобы изменить формат всех значений, выберите меню Параметры поля из группы команд Активное поле,  нажмите кнопку Числовой формат в диалоговом окне Параметры поля значений, а затем выберите из списка нужный формат. Формат ячеек таблицы можно изменять командой Формат ячеек контекстного меню. Обновление данных в сводной таблице. Следует помнить, что при изменении данных в БД не происходит автоматического пересчета итогов в сводной таблице. Для пересчета значений в сводной таблице служит команда Обновить данные из группы Данные вкладки Параметры или команда Обновить контекстного меню. Переименование и удаление полей и элементов. Имена полей и элементов сводной таблицы создаются автоматически на основе исходного списка. Для изменения имени поля достаточно выделить ячейку с этим именем и ввести новое имя непосредственно в поле ввода строки формул. При изменении имени поля или элемента следует помнить, что нельзя использовать имя другого поля в качестве нового имени поля даже в том случае, когда это другое поле не выведено на экран (не включено в сводную таблицу), нельзя заменять имя одного элемента именем другого элемента поля. Для удаления или перемещения поля или элемента щелкните по кнопке в области строк, столбцов, значений или списка полей макета сводной таблицы и выберите соответствующую команду из списка (рисунок 27). Удалить или переместить поле или элемент можно также командой контекстного меню, а также простым перетаскиванием мышью метки соответствующего поля за пределы таблицы. При удалении следует помнить, что в каждой области должно остаться хотя бы одно поле.     Рисунок 27 – Элементы окна обновления макета   Группировка элементов сводной таблицы. Excel предоставляет возможность группировки данных в сводных таблицах. Для создания группы выделите элементы, которые нужно сгруппировать, и выберите команду контекстного меню Группировать.. Для группировки несмежных диапазонов выделите несмежные фрагменты при нажатой клавише . По умолчанию MS Excel создает группы с именами Группа 1, Группа 2 и т.д., которые пользователь может изменить (рисунок 28). Группировку данных можно выполнить с помощью меню Группировать по выделенному вкладки Работа со сводными таблицами /Параметры. К ту же группе входит команда Разгруппировать…, альтернативой которой является одноименная команда контекстного меню. Рисунок 28 – Группировка данных в сводной таблице Сортировка элементов сводной таблицы. Сортировка позволяет быстро упорядочить элементы в поле строки и столбца в соответствии со значениями в области данных. Элементы поля можно сортировать по меткам, по значениям или использовать порядок сортировки, заданный пользователем. Для выполнения сортировки по меткам или значениям пользователю необходимо: ¾ установить курсор в поле сводной таблицы, по которому осуществляется сортировка; ¾ щелкнуть по кнопке Сортировать по возрастанию от А до Я или Сортировать по убыванию от Я до или выполнить команду Сортировка из группы команд Сортировка и фильтр вкладки Параметры. Окно Сортировка позволяет задавать параметры сортировки. Фильтрация данных в сводной таблице. Группа команд Сортировка и фильтр включает команду Вставить срез, которая используется для интерактивной фильтрации данных. Срез  значительно упрощает и ускоряет фильтрацию данных в сводной таблице. На рисунке 29 приведен пример фильтрации данных сводной таблицы по направлению. В результате выполнения команды Вставить срез и выбора критерия отбора на рабочем листе останутся только данные о поставках в Красноярский край. Рисунок 29 – Фильтрация данных в сводной таблице Построение диаграмм. Для построения сводной диаграммы достаточно установить курсор в области сводной таблицы и выполнить команду Сводная диаграмма из группы команд Сервис вкладки Параметры. Окно Вставка диаграммы включает большое количество шаблонов диаграмм. Пользователю остается только выбрать понравившийся тип диаграммы. Результат показан на рисунке 30. Поля со списком, включенные в поле диаграммы позволяют оперативно выполнить «срез» данных, необходимый при анализе. На рисунке приведена диаграмма по результату среза данных по полю Направление. Построение сводных таблиц и диаграмм является эффективным способом обобщения данных для создания детализированных или сводных отчетов, и могут являться полезным средством анализа в работе экономиста. Рисунок 30 – Сводная диаграмма Вопросы для самопроверки 1. Для чего используются сводные таблицы? 2. На основе каких данных можно построить сводную таблицу в MS Excel? 3. Как заполняется макет сводной таблицы? 4. Как внести изменения в сводную таблицу при изменении данных в исходном списке? 5. Какие функции (операции) используются по умолчанию при подсчете итогов в сводной таблице? 6. Как изменить способ вычисления в сводной таблице? 7. Как отформатировать сводную таблицу? 8. Как удалить и отобразить итоговые строки? 9. Для чего используется группировка элементов? 10. Как переупорядочить элементы в сводной таблице?   Пакеты прикладных программ Конспект лекций назад | содержание | вперед ЛЕКЦИЯ 6 Консолидация данных В фирмах и предприятиях часто приходится создавать текущие отчеты по определенным шаблонам, содержащие значительные объемы данных, а затем объединять их в итоговые отчеты. MS Excel позволяет автоматизировать рутинную работу по созданию сводных отчетов с помощью средств консолидации. Консолидация – особый способ вычисления итогов для диапазона ячеек. Консолидируемые данные могут находиться на одном и том же или нескольких листах одной рабочей книги или нескольких рабочих книг. При консолидации доступны все функции статистических итогов. Результат консолидации записывается на лист рабочей книги. На одном листе можно представить несколько результатов консолидации для одних и тех же диапазонов ячеек с различными функциями итогов. Изменения на отдельных листах будет отражаться в консолидирующей электронной таблице. В результате консолидации создается отчет, содержащий структуру, позволяющую в случае необходимости детализировать данные. Консолидация по позиции используется в случае, когда листы данных для консолидации имеют идентичный шаблон. Excel объединяет данные из нескольких рабочих листов с использованием одних и тех же координат диапазона на каждом листе. Для консолидации данных необходимо указать исходные диапазоны данных для консолидации и целевой диапазон, в котором будут отображаться консолидированные данные. Рассмотрим возможности электронных таблиц для консолидации данных на примере составления годового отчета о продажах компанией 20-ти моделей сотовых телефонов. Для этого 1. Создадим «Отчет за первое полугодие» на рабочем листе книги MS Excel (рисунок 1). Сохраним файл с именем «Отчет за первое полугодие». 2. На рабочем листе в новой книге введем данные отчета за второе полугодие. Сохраним новую книгу с именем «Отчет за второе полугодие». 3. Откроем новую книгу и сохраним ее с именем «Годовой отчет». 4. Выполним команду Консолидация вкладки Данные. В диалоговом окне Консолидация ¾ выберем из списка функцию итога – Сумма; ¾ в поле Ссылка укажем диапазоны ячеек таблиц каждой рабочей книги (выделим и добавим диапазоны отчетов за первое и второе полугодие с помощью кнопки Добавить); ¾ установим следующие флажки (рисунок 2): ü Использовать в качестве имен подписи верхней строки и значения левого столбца. ü Создавать связи с исходными данными. 5. После нажатия кнопки ОК на рабочем листе появится структура сводного отчета (рисунок 3). Изменение уровня структуры консолидированного отчета позволяет просмотреть значения показателей в каждом отчетном периоде. При изменении данных в квартальных отчетов изменится и сводный отчет. Рисунок 1 – Лист с исходными данными для консолидации Рисунок 2 – Вид заполненного окна Консолидация Рисунок 3 – Результат консолидации Вопросы для самопроверки 1. Для чего используется консолидация данных? 1. Опишите порядок формирования консолидированной отчетности.     Пакеты прикладных программ Конспект лекций назад | содержание | вперед ЛЕКЦИЯ 7 В Excel существуют специальные функции, которые позволяют автоматически находить данные по критериям в больших таблицах. В  этой лекции описаны функции списков для поиска элементов в массивах данных, приведены примеры использования функций поиска при решении различных прикладных задач. Внимательно изучите назначение и особенности каждой из функций для того, чтобы правильно выбрать ту или иную функцию при решении конкретной задачи.   ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ РАБОЧЕГО ЛИСТА ДЛЯ ПОИСКА ДАННЫХ В СПИСКАХ Функции просмотра элементов массива В Excel встроены функции вертикального и горизонтального просмотра, принадлежащие к категории «Ссылки и массивы». Функция ВПР() – вертикального просмотра – ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Формат функции =ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр), где искомое_значение – значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой. При этом регистр не учитывается (т.е. строчные и прописные буквы не различаются); таблица – это таблица с информацией, в которой осуществляется поиск данных. Можно использовать ссылку на интервал или имя интервала, например, «Список»; номер_столбца – это номер столбца массива таблица, в котором должно быть найдено соответствующее значение.  Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента таблица и так далее. Если номер_столбца меньше 1, то функция ВПР() возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе таблица, то функция ВПР() возвращает значение ошибки #ССЫЛ!; интервальный_просмотр – логическое значение, которое определяет точный или приближенный поиск. Если интервальный_просмотр имеет значение ИСТИНА, то осуществляется приближенный поиск, и значения в первом столбце аргумента таблица должны быть расположены в возрастающем порядке (например, -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА), иначе функция ВПР() может возвратить неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то осуществляется точный поиск и таблица может быть не отсортированной. По умолчанию вектор для поиска значения в таблице должен быть упорядочен по возрастанию.        Замечания. 1. Если ВПР() не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение (ведь столбец упорядочен по возрастанию!). 2. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, то функция ВПР() возвращает значение ошибки #Н/Д. 3. Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР() возвращает значение ошибки #Н/Д. 4. Для обработки подобной ошибки удобно использовать функцию =ЕНД() из категории «Проверка свойств и значений». Функция  =ЕНД() возвращает значение «истина», когда ее аргументами являются значения  #НД.   Функция   ГПР() – горизонтального просмотра – просматривает таблицу по строкам, начиная с первой. Функция ищет значение в первой строке таблицы и возвращает значение в том же столбце из указанной в списке аргументов строки. Функция по назначению и формату похожа на ВПР(). Все пояснения и замечания к функции ВПР() относятся и к функции горизонтального просмотра. Формат функции   =ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр), где искомое_значение – значение, которое необходимо найти в первой строке таблицы, может быть значением, ссылкой или текстовой строкой; таблица – массив, в первой строке которого осуществляется поиск значений, может быть задан ссылкой на диапазон или именем диапазона; номер_строки – номер строки, из которой нужно вернуть значение. Если функция находит соответствие, то возвращает данные из указанного номера строки; интервальный_просмотр – логическое выражение, определяющее способ поиска в первой строке. Если интервальный_просмотр имеет значение ИСТИНА, то осуществляется приближенный поиск и первая строка таблицы должна быть упорядочена по возрастанию (например, -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА), иначе функция ГПР() может выдать неверный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, осуществляется точный поиск и таблица может быть не отсортированной.      Замечания. 1. По умолчанию интервальный_просмотр равен 1 и вектор для поиска значения в таблице должен быть упорядочен по возрастанию. 2. Если ГПР() не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение. 3. Если искомое_значение меньше, чем наименьшее значение в первой строке аргумента таблица, то функция ВПР возвращает значение ошибки #Н/Д. 4. Если ГПР() не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то функция вернет значение ошибки #Н/Д. Рассмотрим пример использования функции вертикального просмотра. Пример 1. Пусть в таблице «Справочник тарифов»  содержится информация о тарифах на междугородние разговоры. Рассчитаем в таблице «Учет междугородних разговоров» сумму к оплате на основании данных о тарифах и времени разговора в минутах без скидок, а также сумму к оплате с учетом того, что в выходные дни стоимость минуты уменьшается на 30%. Порядок действий: 1. На рабочем листе создадим два списка исходными данными (рисунок 1). Рисунок 1 – Диапазоны с исходными данными 2. В ячейку H4 введем формулу   =ДЕНЬНЕД(G4;2) из категории Дата и время и скопируем ее до конца диапазона значений столбца. Это позволит далее определить, какие из дней являются выходными. 3. В ячейку J4 введем формулу   =ВПР(F4;$B$3:$C$10;2;0)*I4  и скопируем ее до конца диапазона. Вид диалогового окна функции приведен на рисунке 2. Теперь заменим одно из значений населенного пункта (например, Каргат на Иркутск). Как изменились результаты? Если в справочной таблице отсутствуют данные о тарифах для какого- либо населенного пункта, то функция ВПР() возвращает в соответствующих ячейках значение #Н/Д (рисунок 3). Учтем в формуле возможность возникновения подобной ситуации.   Рисунок 2 – Диалоговое окно функции ВПР() Рисунок 3 – Пример использования функции вертикального просмотра 4. Для обработки ошибки будем использовать функции ЕНД() и ЕСЛИ(). В ячейку J4 введем новую формулу =ЕСЛИ(ЕНД(ВПР(F4;$B$3:$C$10;2;0));"нет_данных";ВПР(F4;$B$3:$C$10;2;0)*I4)  и скопируем ее до конца диапазона (рисунок 4). Результат обработки ошибки в столбце J приведен на рисунке 5. 5. Для расчета суммы к оплате с учетом дня недели в ячейку K4 введем формулу проверки сложных условий =ЕСЛИ(J4="нет_данных";"";ЕСЛИ(ИЛИ(H4=7;H4=6);J4*0,7;J4)) (рисунок 5). Рисунок 5 – Пример использования проверки сложных условий Пример 2. Следующий пример демонстрирует расчет сумм, выставленных к оплате оператором в текущем месяце. Компания предоставляет абонентам Интернет - услуги и предлагает абонентам две тарифные группы, в которые входит по пять тарифных планов: с абонентской платой и бесплатным трафиком выхода в Интернет и с неограниченной скоростью передачи данных. В связи с этим для расчетов используются две отдельные таблицы тарифных групп (рисунок 6). В ячейку I5 введена формула, которая в зависимости от принадлежности тарифа к определенной группе (Б/Т или Н/С), возвращает сумму к оплате, включающую величину абонентской платы и платы за входящий трафик. =ЕСЛИ(I5="Б/Т";ВПР(J5;$A$4:$E$9;2;0)+ВПР(J5;$A$4:$E$9;3;0)*K5; ВПР(J5;$A$14:$E$19;2;0)+ВПР(J5;$A$14:$E$19;3;0)*K5) Результаты расчетов приведены на рисунке 7. Рисунок 6 – Справочники тарифов Рисунок 7 – Расчет суммы к оплате для абонентов, пользующихся Интернет – услугами Для обработки возможных ошибок воспользуемся функцией ЕСЛИОШИБКА(), которая выполняет проверку на предмет ошибки в формуле и, если ошибка не обнаружена,  возвращается значение, иначе выводится заданный текст (сравните с вариантом обработки ошибки в предыдущем примере). В ячейку L3 введем следующую формулу: =ЕСЛИ(I5="Б/Т";ЕСЛИОШИБКА((ВПР(J5;$A$4:$E$9;2;0)+ВПР(J5;$A$4:$E$9;3;0)*K5);"нет_данных");ЕСЛИОШИБКА((ВПР(J5;$A$14:$E$19;2;0) +ВПР(J5;$A$14:$E$19;3;0)*K5);"нет данных")) Результат расчета входящего трафика и суммы к оплате для каждого абонента с обработкой ошибок представлен на рисунке 8. Рисунок 8 – Список счетов абонентов Функции ИНДЕКС() и  ПОИСКПОЗ()    Функции ИНДЕКС() и ПОИСКПОЗ()  также относятся к категории Ссылки и массивы. Функция ПОИСКПОЗ() возвращает относительное положение элемента массива, который соответствует заданному значению. Функция ПОИСКПОЗ() используется вместо функций типа ПРОСМОТР(), если нужна позиция элемента в диапазоне, а не сам элемент. Формат функции  =ПОИСКПОЗ(искомое_значение; массив;тип_сопоставления), где   искомое_значение – это значение, поиск которого ведется. Искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение; массив   – это непрерывный интервал ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив; тип_сопоставления   – это число -1, 0 или 1. Тип_сопоставления указывает,  как MS Excel сопоставляет искомое_значение  со значениями в просматриваемом массиве. Если тип_сопоставления равен 1, то функция ПОИСКПОЗ() находит наибольшее значение, которое равно или меньше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА. Если тип_сопоставления не указан, то предполагается, что он равен 1. Если тип_сопоставления равен 0, то функция ПОИСКПОЗ() находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен. Если тип_сопоставления равен -1, то функция ПОИСКПОЗ() находит наименьшее значение, которое равно и больше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, -2, ..., и так далее. Замечания. 1. ПОИСКПОЗ() не различает регистры при сопоставлении текстов. 2. Если функция ПОИСКПОЗ() не находит соответствующего значения, то возвращается значение ошибки #Н/Д. 3. Возможен поиск значения по маске. Если тип_сопоставления равен 0 и искомое_значение является текстом, то искомое_значение может содержать символы шаблона, звездочка (*) и знак вопроса (?). Звездочка соответствует любой последовательности символов, знак вопроса соответствует любому одиночному символу.   Пример 1. Фирма участвует в высокотехнологичном проекте. Определим срок возмещения первоначальных инвестиционных затрат при заданном прогнозируемом годовом движении денежных средств на 10-летний период без учета изменения стоимости денежных средств во времени. Начальные инвестиции составляют 6000000р., поток денег в первый год – 500000р., ожидаемый ежегодный прирост денежных потоков - 20%. Для решения задачи 1. Введем исходные данные на рабочем листе (рисунок 9). 2. В ячейку В8 введем поток денежных средств на начало периода. 3. В диапазоне В8:В18 вычислим денежные потоки в каждом году с учетом предполагаемого роста (значение в ячейке В3). 4. В диапазоне С8:С18 вычислим совокупные ежегодные потоки денежных средств. 5. В ячейку В5 введем функцию для нахождения срока окупаемости. В нашем случае нужно отыскать год, в котором совокупный поток денежный средств превысит нулевое значение. Функция возвратит номер строки, содержащей первый год, в течение которого денежный поток станет положительной величиной.  Столбец  С упорядочен по возрастанию и в формуле тип сопоставления равен 1. Рисунок 9 – Вид рабочего листа с исходными данными и расчетными формулами Результат решения представлен на рисунке 10. Рисунок 10 – Результат расчета срока окупаемости с помощью функции ПОИСКПОЗ() Пример 2. Фирма учитывает грейд сотрудников (квалификацию, степень ответственности, сложность труда и др.) при оплате труда. В таблице на рисунке 83 представлены данные о грейде 20 сотрудников фирмы. Требуется вывести номер сотрудника с максимальным грейдом и сотрудника с третьим по величине грейдом. В данном случае будем использовать функцию ПОИСКПОЗ() для поиска точного соответствия. Вид рабочего листа с формулами приведен на рисунке 83.  Диапазону С4:С27 присвоено имя грейд, которое используется в формулах. В ячейку Е6 введена формула =МАКС(Грейд). Функция =ПОИСКПОЗ(E6;Грейд;0) в ячейке Е4 возвращает номер работника с максимальным грейдом. В ячейке F6 введена формула =НАИБОЛЬШИЙ(Грейд;3) для нахождения третьего по величине значения грейда, а в ячейке F4 – функция для поиска соответствующего номера сотрудника. Результаты поиска приведены на рисунке 11. Рисунок 11 – Вид листа с исходными данными с функциями поиска информации Рисунок 12 – Результаты поиска Функция ИНДЕКС(). Часто функции ПОИСКПОЗ() и ИНДЕКС() используются вместе в формулах для создания сложных условий поиска значений в списках. Функция ИНДЕКС() имеет две синтаксические формы: ссылка и массив. Ссылочная форма всегда возвращает ссылку, а форма массива всегда возвращает значение или массив значений.  Функция ИНДЕКС() для массива позволяет выполнять поиск данных в списке в двух измерениях и выводит значения по указанным в аргументах строке и столбцу.   Формат функции  =ИНДЕКС(массив;номер_строки;номер_столбца), где   ·   массив – список (таблица) для поиска данных; ·   номер_строки и номер_столбца определяют позицию искомого элемента списка. Если используются оба аргумента номер_строки  и номер_столбца, то функция ИНДЕКС() возвращает значение, находящееся в ячейке на пересечении номера строки и номера столбца. Если массив содержит только одну строку или один столбец, то соответствующий аргумент номер_строки или номер_столбца не является обязательным. ·   номер_строки   - это номер строки в массиве, из которой нужно вернуть  значение Если номер_строки пропущен, то аргумент номер_столбца нужно задавать обязательно; ·   номер_столбца   - это номер столбца в массиве, из которого нужно вернуть значение. Если номер_столбца пропущен, то аргумент номер_строки нужно задавать обязательно. Если массив занимает больше, чем одну строку и больше, чем один столбец, а задан только один аргумент номер_строки или номер_столбца, то функция ИНДЕКС() возвращает массив из целой строки или целого столбца аргумента массив. Если задать номер_строки или номер_столбца равным 0, то функция ИНДЕКС() также вернет массив значений для целого столбца или целой строки, соответственно. Для того, чтобы использовать значения, возвращаемые как массив, функцию ИНДЕКС() нужно ввести как формулу массива в горизонтальный интервал ячеек. Для ввода формулы массива в Microsoft Excel в окне Мастера функции вместо кнопки ОК нужно нажать комбинацию клавиш . Пример. Вывести наименование самого дорогого товара, который можно приобрести, имея сумму наличности 650$.     Исходные данные и результаты решения приведены на рисунке 13. В ячейку результата С14 введена формула для поиска товара в соответствии с условием задачи: =ИНДЕКС(A4:C11; ПОИСКПОЗ(A14; B4:B11; 1);1) Расчет остатка денежных средств после покупки товара выполнен по формуле      =A14-ВПР(A14;$B$3:$C$11;1;1) в ячейке  C17. Рисунок 13 – Пример использования функций поиска данных в списке Функции поиска можно использовать для поиска данных в разных полях, используя операцию конкатенации в списках аргументов функции. Например, нужно найти данные о сотруднике в списке, где могут встречаться однофамильцы, и вывести сведения о занимаемой им должности. Пример решения задачи приведен на рисунке 14. Результаты функции ПОИСКПОЗ() используются в аргументах функции ИНДЕКС() в формуле массива. В примере на рисунке 14 функция ПОИСКПОЗ() используется для поиска в двух измерениях: по строкам и по столбцам. Рисунок 14 – Пример использования поиска по двум столбцам таблицы Рисунок 15 – Использование функций поиска для массива данных Ссылочная форма ИНДЕКС() всегда возвращает ссылку.  Функция возвращает ссылку на искомую ячейку или ячейки в аргументе ссылка. Формат функции =ИНДЕКС(ссылка; номер_строки; номер_столбца; номер_области) ·   ссылка – это ссылка на один или несколько интервалов ячеек; ·   номер_строки – это номер строки в аргументе ссылка, на которую возвращается ссылка; ·   номер_столбца – это номер столбца в аргументе ссылка, на который возвращается ссылка; ·   номер_области – интервал ссылки, из которого надо возвращать пересечение номер_строки и номер_столбца. Первая введенная или выделенная область имеет номер 1, вторая – 2, и так далее. Если номер_области пропущен, то функция ИНДЕКС() использует область номер 1. Например, если аргумент ссылка описывает ячейки (A1:B4,D1:E4,G1:H4), то номер_области 1 соответствует интервалу A1:B4, номер_области 2 соответствует интервалу D1:E4 и номер_области 3 соответствует интервалу G1:H4. После того, как с помощью аргументов ссылка и номер_области выбран конкретный интервал, с помощью аргументов номер_строки и номер_столбца выбирается конкретная ячейка: номер_строки 1 соответствует первой строке интервала, номер_столбца 1 соответствует первому столбцу интервала и так далее. Ссылка, возвращаемая функцией ИНДЕКС(), это ссылка на пересечении номера_строки и номера_столбца. Если установить номер_строки или номер_столбца равным 0 (нулю), то функция ИНДЕКС() вернет ссылку соответственно на целую строку или столбец. Если каждая область в ссылке содержит только одну строку или один столбец, то соответственно аргумент номер_строки или номер_столбца указывать необязательно. Например, для одиночной строки следует использовать форму   =ИНДЕКС(ссылка;;номер_столбца).   Замечания. 1. Номер_строки, номер_столбца и номер_области должны указывать на ячейку внутри аргумента ссылка; в противном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛ!. Если номер_строки и номер_столбца пропущены, то функция ИНДЕКС() возвращает область в аргументе ссылка, заданную аргументом номер_области. 2. Результатом функции ИНДЕКС() является ссылка, и она интерпретируется в качестве таковой другими функциями. В зависимости от формулы, возвращаемое функцией ИНДЕКС() значение может быть использовано как ссылка или как значение. Например, формула ЯЧЕЙКА("ТАБЛ";ИНДЕКС(A1:С6;1;2)) эквивалентна =ЯЧЕЙКА("ТАБЛ";B1). Функция ЯЧЕЙКА() использует значение, возвращаемое функцией ИНДЕКС(), как ссылку. С другой стороны, такая формула, как   =2*ИНДЕКС(A1:B2;1;2), переводит значение, возвращаемое функцией ИНДЕКС, в число, содержащееся в ячейке B1. Рассмотрим более сложный пример с использованием необходимости проверки нескольких логических условий и поиска информации.   Вопросы для самопроверки 1. Перечислите функции поиска данных в одномерных массивах. 2. Назовите аргументы функций ВПР() и ГПР(). 3. Какой результат возвращает функция ПОИСКПОЗ()? 4. Какой формат имеет функция ИНДЕКС()? 5. Поясните возможные формы записи функции ИНДЕКС().   Контрольные задания для самопроверки  Задание 1. Создайте на рабочем листе таблицы о стоимости моделей ПК (4 вида) и сведений о продажах за неделю (20 записей). Рассчитайте графу «Сумма к оплате» на основании данных справочной таблицы.   Модель         Цена, тыс.           Модель Дата продажи Количество Сумма к оплате           Задание 2. Создайте на рабочем листе таблицы о стоимости товаров и сведений о продажах. Рассчитайте сумму покупок для каждого вида товара   № Товар Страна  изготовитель Цена   № Дата Товар Количество Сумма покупки 1         1         2          2         3          ..         4          ..         5          ..                    ..                    15           Задание 3. На рабочем листе «Прейскурант» расположен прейскурант следующего вида   ТОВАР 1 20 50 А 300 270 250 М 30 26 22 С 60 50 45 Б 35 30 25   В строке «Товар» проставлены граничные значения количества товара, а ниже – цены за единицу товара. Для оптовых покупателей цены снижаются. Приведите таблицу «Накладная», в которой рассчитывается цена покупки.   ТОВАР Дата покупки Количество Цена Стоимость покупки А 02.03.11 36     С 02.03.11 53     Б 03.03.11 36     М 04.03.11 4     А 04.03.11 16       Задание 4. Будем считать, что минимальный оклад  составляет 1000 руб. Составить таблицу со списком сотрудников (20 записей), содержащую следующие поля: ФИО, Должность, Разряд, Оклад. Рассчитать в созданной таблице графу «Оклад» с учетом коэффициентов разрядной сетки.   Разряд Коэффициент 8 3,12 9 3,53 10 3,99 11 4,51 12 5,10 13 5,76 14 6,51 15 7,36 16 8,17 17 9,07 18 10,07   Пакеты прикладных программ Конспект лекций назад | содержание | вперед ЛЕКЦИЯ 8 Анализ информации в экономических информационных системах позволяет принимать нужные решения для эффективного развития бизнеса. Сравнение значений показателей позволяет выбрать наиболее оптимальный вариант действий. Технологии MS Excel позволяют автоматизировать процесс принятия решения на основе использования встроенных логических функций и функций проверки свойств и значений. После прочтения этой лекции Вы получите навыки использования потенциала логических функций для решения задач, связанных с принятием решения. АВТОМАТИЗАЦИЯ процесса поддержки принятия решения средствами MS Excel Работа с логическими функциями рабочего листа Использование функции ЕСЛИ() при выборе решения  Функция ЕСЛИ() является одной из наиболее часто используемых логических функций MS Excel в решении задач принятия решений. Функция ЕСЛИ() позволяет реализовать в MS Excel разветвляющийся вычислительный процесс, когда в зависимости от результатов проверки логического условия выбирается одно из альтернативных действий. Функция имеет следующий формат:  =ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь), где логическое_выражение – проверяемое условие: любое значение или выражение, которое при вычислении возвращает значение ИСТИНА или ЛОЖЬ. В логическом выражении используются операторы сравнения ->; <; =; >=; <=. Операндами в выражении могут быть числа, функции, формулы, текстовые или логические значения. значение_если_истина – значение, которое будет занесено в активную ячейку, если проверяемое условие выполняется. Если аргумент значение_если_истина пропущен, то возвращается значение ИСТИНА. значение_если_ложь – это значение, которое будет занесено в активную ячейку, если проверяемое условие не выполняется. Если аргумент значение_если_ложь пропущен, то возвращается значение ЛОЖЬ.   В качестве аргументов функции могут использоваться результаты, возвращаемые другими функциями. Рассмотрим примеры записи функции. =ЕСЛИ(А1<В1; «да»; «нет») - в активную ячейку будет выводиться текст «да», если содержимое ячейки А1 меньше В1, и «нет», если условие не выполняется. =ЕСЛИ(B3>80;"Сдал";"Не сдал") - в активную ячейку заносится текст «Сдал», если содержимое ячейки ВЗ превышает величину 80, и «Не сдал» – в противном случае. =ЕСЛИ(А1=“ТЕСТ”;1;0) – в этом примере сравниваются текстовые значения. Совпадение должно быть точным, но без учета регистра. В активную ячейку будет занесено число 1, если в А1 находится строка ТЕСТ, и 0, если это не так. Функция ЕСЛИ может использоваться в упрощенном варианте, например, действия производятся только при выполнении условия проверки логического выражения (=ЕСЛИ(А1=“ТЕСТ”;1). =ЕСЛИ(СУММ(А1:А10)>0;СУММ(А1:А10);0) – в активную ячейку будет занесена сумма значений ячеек из диапазона А1:А10, если она положительная, и ноль, если сумма меньше или равна нулю. =ЕСЛИ(СУММ(А1:А10)>0;СУММ(А1:А10);"") – в отличие от предыдущего примера  данная функция возвратит пустое значение, а не 0, если проверяемое условие ложно. =ЕСЛИ(СУММ(А1:А10)>0;ПОВТОР(“>”;СУММ(А1:А10))). Чем больше сумма чисел в указанном диапазоне, тем больше знаков ”>” будет повторяться в ячейке вывода результата (рисунок 1). Если функция СУММ() возвратит отрицательное значение, то в ячейке результата будет выведено значение ЛОЖЬ, так как в функции ЕСЛИ() не определено действие для этого случая. Рисунок 1 – Пример использования функций ЕСЛИ(), СУММ() и ПОВТОР() Часто функцию ЕСЛИ() используют перед выполнением деления, чтобы избежать ошибки деления на ноль. Зачастую в задачах необходимо проверить сразу нескольких условий. Для решении таких задач используются вложенные функции ЕСЛИ(). В этом случае при оценки аргументов значение_если_истина и (или) значение_если_ложь возникает очередной выбор варианта действий. Например, в ячейку С1 следует занести число 1, если содержимое ячейки В1>0; число -1, если содержимое ячейки В1<0 и 0, если содержимое ячейки В1=0. В данном случае формула, введенная в ячейку С1, будет иметь вид   =ЕСЛИ(В1>0;1;ЕСЛИ(В1<0;-1;0)). Комбинации логических функций ЕСЛИ(), И(), ИЛИ() и НЕ() позволяют выполнять сложные проверки. Если необходимо выполнить одну и ту же проверку для многих ячеек можно применить функцию ЕСЛИ() к диапазону и ввести формулу как массив. Функции ЕЧИСЛО() и ЕПУСТО() из категории Проверка свойств и значений в зависимости от содержимого в проверяемой ячейке возвращают логическое значение ИСТИНА или ЛОЖЬ. Они используются в качестве логического выражения в списке аргументов функции ЕСЛИ().   Пример 1. Для статистической выборки длительности междугородних переговоров за определенный период  вычислим общее время разговоров с длительностью более 20 минут. Результаты решения приведены на рисунке 2. Ввод формулы =СУММ(ЕСЛИ(A2:A15>20;A2:A15)) заканчиваем нажатием комбинации клавиш ++. Рисунок 2 – Пример использования функций для элементов массива Пример 2. Произведем расчет сумм к оплате за переговоры на основе тарифа и количества минут разговора (рисунок 3). Если длительность разговора равна или более 20 минут, абоненту назначается 30% скидка. В ячейку В4 введем и скопируем до конца диапазона формулу  =$B$1*A4. В выделенный диапазон ячеек (C4:C17) введем формулу для расчета массива сумм к оплате с учетом скидки: =ЕСЛИ(Длительность_разговора__мин.>=20;Сумма_к_оплате*0,7;Сумма_к_оплате). В формуле используем заранее назначенные имена диапазонов. Рисунок 3 – Расчет оплаты за переговоры Пример 3. На рисунке 4 функция проверки условия используется для формирования суммирующего отчета на основании данных о продажах за каждый квартал. Итоговые суммы выводятся в верхних границах интервала по формуле: =ЕСЛИ(ОСТАТ(СТРОКА();4)=0;СУММ(C4:C7);""). Функция ОСТАТОК в данном случае проверяет, нужно ли выводить значение в текущей строке. Функция СТРОКА() возвращает номер текущей строки и, если он без остатка делится на 4, то выводятся итоговая сумма, иначе ячейка остается пустой. Рисунок 4 – Отчет о продажах за 4 года Функции списков, использующие критерии в качестве аргумента Рассмотрим наиболее часто применяемые при анализе и вычислениях функции СЧЕТЕСЛИ(), СУММЕСЛИ(), в списке аргументов которых используется условие. Данные функции работают только с одним критерием. Позже мы рассмотрим функции, которые обладают тем преимуществом, что работают с диапазонами критериев и позволяют использовать составные и вычисляемые условия. Функция СЧЕТЕСЛИ() относится к категории статистических. Функция СЧЕТЕСЛИ() подсчитывает количество ячеек, соответствующих одному заданному условию в указанном диапазоне. Формат функции =СЧЕТЕСЛИ(диапазон; критерий), где диапазон – это диапазон ячеек, в котором проверяется условие и подсчитывается количество соответствующих критерию значений; критерий – критерий, введенный как текст, указывает, какие значения должны участвовать в подсчете. Критерий может быть задан в форме числа, выражения или текста. Например, критерий может быть выражен следующим образом: 50, «12», «>20», «Запад», ЛОЖЬ. В аргументе критерий допускается использование масок, например, «К*». Пример 1. Параметр SQI используется в сотовой связи для оценки качества передачи речи. Если SQI находится в пределах от 18 до 30, то качество считается отличным, если в пределах от 10 до 18 – хорошим, менее 10 - плохим. На рисунке 5 приведены данные выборки наблюдений. Требуется определить количество каналов трафика с отличным, хорошим и плохим качеством.  Рисунок 5 – Вид рабочего листа с исходными данными, окном функции и расчетными формулами Полезными функциями также относящихся к категории статистических являются функции: СЧЕТ() - возвращает количество ячеек в указанном диапазоне, значениями которых являются числа; СЧЕТЗ() – подсчитывает количество непустых ячеек в диапазоне. При подсчете учитываются ячейки, содержащие значение ошибки или пустой текст “”; СЧИТАТЬПУСТОТЫ() – вычисляет количество пустых ячеек в диапазоне. Ячейки, содержащие пустой текст также учитываются при подсчете. Функция СУММЕСЛИ() относится к категории математических. Функция СУММСЛИ() вычисляет сумму ячеек диапазона суммирования, соответствующих одному заданному условию в диапазоне критериев. Формат функции =СУММЕСЛИ(диапазон; критерий; диапазон_суммирования)   диапазон – это диапазон ячеек, в котором проверяется условие; критерий – критерий, введенный как текст. Excel применяет этот критерий к указанному в аргументах диапазону. Критерий может быть задан в форме числа, выражения или текста; диапазон_суммирования- это диапазон из которого выбираются значения для суммирования. Excel суммирует только те ячейки из диапазона суммирования, которые соответствуют ячейкам столбца диапазон, удовлетворяющим критерию. Если диапазон_суммирования пропущен, то суммируются ячейки в аргументе диапазон, т.е. в том же интервале, в котором проверялось условие. Пример 2. На основании данных учета (рисунок 3) определим общую сумму к оплате для разговоров с длительностью более 20 минут. Для решения вставим функцию СУММЕСЛИ() в ячейку E4. Диалоговое окно функции с аргументами приведено на рисунке 6. Рисунок 6 – Диалоговое окно функции СУММСЛИ() Вопросы для самопроверки 1. Какие аргументы содержит функция ЕСЛИ()? 2. Как проверить «пустое» значение? 3. Как определить, является ли проверяемое значение числовым? 4. Как записать вложенную функцию ЕСЛИ()? 5. Как применить функцию ЕСЛИ() к элементам массива? 6. Для чего используется функции СЧЕТ() и СЧЕТЕСЛИ()? 7. Когда вместо функции СУММ() следует использовать функцию СУММЕСЛИ()? Контрольные задания для самопроверки 1.  Создайте таблицу, содержащую результаты сдачи экзаменов студентами группы и выполните следующие задания: ·   рассчитайте средний балл по каждому предмету и средний балл каждого студента; ·   добавьте столбец «Примечание», в котором бы в соответствующих строках выводился текст «отличник» или «хорошист». Остальные ячейки столбца должны остаться пустыми; ·   подсчитайте количество отличников и хорошистов; ·   определите процент качественной успеваемости в группе.   № ФИО Математика Физика Информ1атика История Средний балл                               Средний балл по предмету             2.  Создайте таблицу расчета имущественного налогового вычета для 20 налогоплательщиков. Рассчитайте налоговую базу с учетом следующего: · если объект недвижимости находился во владении более 3-х лет, то имущественный вычет (уменьшение налоговой базы) предоставляется в сумме, полученной от продажи; · если объект недвижимости находился во владении менее 3-х лет, то имущественный вычет предоставляется в сумме, либо 2000000 руб., либо уменьшается на сумму документально подтвержденных расходов. Заполните в таблице графы «Сумма уменьшения налоговой базы» и «Налоговая база» с учетом того, какой из вариантов предоставления вычета будет наиболее предпочтителен для налогоплательщика. № п/п Полученный доход Дата покупки Дата  продажи Док. потвержд. расходы Сумма уменьшения  налоговой базы Налоговая база                               Сумма             Пакеты прикладных программ Конспект лекций назад | содержание | вперед ЛЕКЦИЯ 9 Имитационное моделирование неопределенных процессов Это её одна интересная и полезная функция MS Excel, используемая  в экономическом моделировании для оценки вероятности неопределенных событий, таких, например, как эффективности инвестиций, прогнозирования чистой прибыли,  рисков, колебаний валют и т.д.  Метод Монте – Карло позволяет «проигрывать» различные ситуации и может использоваться как составная часть процесса принятия решения.  Excel включает все инструменты для построения имитационной модели на основе функций рабочего листа и технологии анализа «ЧТО-ЕСЛИ». Рассмотрим следующий пример. Пример 1.  Предприятие связи закупило поздравительные почтовые открытки в количестве 2300 шт. по цене 5,5 руб. за штуку. Цена реализации составляет 12 рублей. Не проданные вовремя  открытки продают по цене 1 руб. Предположим, что спрос описывается дискретной случайной величиной. Сколько открыток нужно заказать для получения максимальной прибыли?   Порядок действий:   Смоделируем многократно возможные объемы заказов (в нашем примере для 20 случаев) и определим, какой из объемов заказов принесет максимальную прибыль.   1. Заполним лист «Монте-Карло» исходными данными в соответствии с рисунком 148. В диапазоне D3:E6 введем границы диапазонов случайных величин (D3:D6) и интервалов возможного спроса (Е3:Е6).   Рисунок 1 – Лист с исходными данными Случайные числа от 0 до 0,1 соответствуют спросу 10000, от 0,1 до 0,45 – 20000 и т.д. Присвоим имена диапазонам и ячейкам для простоты понимания формул, которые будут использоваться в расчетах (рисунок 2). Рисунок 2 – Окно Диспетчер имен 2. В ячейку В6 вставим функцию СЛЧИСЛО(), возвращающую с равной вероятностью значение из интервала от 0 до 1. Поскольку функция будет заново генерировать случайное число при каждом обновлении рабочего листа, то вид рабочего будет постоянно изменяться при вводе новых данных. Случайное число будем использовать для определения величины спроса при очередном варианте расчета (диапазон D3:E6, рисунок 1). 3. В ячейку В3 введем функцию вертикального просмотра для определения величины спроса при сгенерированном значении вероятности. Пример заполнения окна функции приведен на рисунке 3. 4. В ячейку В12 введем формулу для расчета выручки. Выбираем минимальное среди значений спроса и заказа и умножаем его на цену продажи открытки. Учитываем в формуле не проданные открытки, которые реализуются по сниженной цене с помощью функции ЕСЛИ(): =МИН(Спрос;Заказано)*Цена_продажи+ЕСЛИ(Спрос<Заказано;(Заказано-Спрос)*Цена_со_скидкой;0). 5. В ячейке В13 рассчитаем общую сумму затрат по формуле: =Заказано*Цена_закупки. 6. В ячейку В14 введем формулу расчета прибыли:  =Выручка-Затраты. Вид рабочего листа с формулами приведен на рисунке 4. Результаты расчета основных показателей представлены  на рисунке 5. Рисунок 3 – Вид окно функции ВПР() Рисунок 4 – Вид листа с формулами Рисунок 5 – Результаты расчета основных показателей 7. Подсчитаем сумму прибыли при каждой величине заказа для 20 испытаний. ¾ В диапазоне А17:Е37 создадим таблицу чувствительности с двумя переменными (рисунок 6). Для этого заполним диапазон А18:А37 номерами испытаний, а диапазон В17:Е17 – возможными объемами заказов. ¾ В ячейку А17 введем ссылку на формулу для расчета прибыли. Выделим диапазон таблицы чувствительности (А17:А37) и выполним команду Таблица данных… Заполним окно таблицы данных: o В поле Подставлять значения по столбцам в: введем адрес ячейки с суммой заказа (В5). o В поле Подставлять значения по строкам в: укажем ссылку на любую свободную ячейку, например, D8 (рисунок 6). После нажатия кнопки ОК таблица будет заполнена расчетными значениями. Рисунок 6 – Окно таблицы данных Поясним, как Excel выполняет расчеты таблицы чувствительности. В расчетах значений в столбце В таблицы чувствительности Excel будет использовать значение из ячейки В5, равное 10000. Для расчета значения в ячейке, например, В18 в свободную ячейку вводится значение входного столбца таблицы данных, равное 1 и в ячейке В6 генерируется случайное число. Рассчитанная сумма прибыли записывается в ячейку В18. Далее значение ячейки входного столбца таблицы 2 подставляются в свободную ячейку, а в ячейке В19 вычисляется прибыль, величина которой определяется сгенерированным числом. 8. Вычислим среднее значение смоделированной прибыли с помощью функции СРЗНАЧ().  В ячейках В38:Е38 вставим соответственно функции  =СРЗНАЧ(B18:B37), =СРЗНАЧ(C18:C37), =СРЗНАЧ(D18:D37), =СРЗНАЧ(E18:E37). В диапазоне В39:Е39 рассчитаем стандартное отклонение по выборке с помощью функции СТАНДОТКЛОН.В() с указанием соответствующих диапазонов выборок (рисунок 7). Рисунок 7 – Формулы расчета доверительного интервала Каждая функция вводится  только в первую ячейку диапазона, а в остальные ячейки копируется путем перетаскивания мышью. Нажатием клавиши для всех объемов заказов моделируются 20 итераций разных уровней спроса. На рисунке 9 приведены результаты моделирования с использованием метода Монте-Карла при текущем случайном значении ячейки В6. Искомой величиной заказа будет являться та, которая будет давать наибольшую расчетную прибыль. В нашем примере, заказ, обеспечивающий максимальную прибыль равен 60000. Если заказ будет составлять 10000, то прибыль минимальна, но всегда постоянна (см. рисунок 8), а стандартное отклонение равно 0. В этом случае спрос превышает предложение, все открытки будут распроданы и полностью отсутствует риск превышения суммы затрат над суммой выручки. 6. Определим, в какой интервал с вероятностью 95% попадает истинное значение средней прибыли, т.е. доверительный интервал для средней прибыли с доверительной вероятностью 95%. Доверительный интервал вычисляется по формуле: Средняя_прибыль±1,96*Станд.отклон/ В ячейки Е12 и Н13 введем расчетные формулы для нижней и верхней границы доверительного интервала для средней прибыли при разных объемах заказов (рисунок 8): Рисунок 8 – Формулы расчета доверительного интервала На рисунке 9 в диапазоне E12:H13 представлены результаты вычислений границ доверительного интервала. Таким образом, с 95% уверенностью можно говорить, что средняя прибыль от продажи открыток будет находиться в пределах соответствующих нижних и верхних границ для каждой величины заказа. Рисунок 9 – Результаты моделирования Вопросы для самопроверки 1. Каково назначение функции СЛЧИСЛО()? 2. Опишите принцип построения имитационной модели с использованием метода Монте-Карло. Для каких задач используют этот метод? 3. Что показывает и как вычисляется доверительный интервал? 4. В каком случае стандартное отклонение равно 0?   Пакеты прикладных программ Конспект лекций назад | содержание | вперед ЛЕКЦИЯ 10 MS Excel включает мощные средства для решения задач статистики. Изучение материалов лекции позволит приобрести навыки решения задач анализа и прогнозирования средствами табличного редактора. Оцените, насколько просто и быстро решаются подобные задачи с помощью прикладных программ. Частотный анализ в среде MS Excel  В задачах анализа и статистики приходится отвечать на вопрос, как часто данные выборки встречаются в заданных интервалах значений. Инструментом, позволяющим решать подобные задачи, в MS Excel является функция ЧАСТОТА(), относящаяся к категории статистических функций. Функция используется для построения интервального распределения и возвращает распределение частот в виде вертикального массива. Для данного множества значений и заданного множества карманов (интервалов) частотное распределение подсчитывает, сколько значений попадает в каждый интервал.  В качестве массива данных может быть одномерный  или двумерный массив. Формат функции =ЧАСТОТА ( массив_данных; массив_карманов)   Для частотного анализа можно использовать команду Анализ данных вкладки Данные. В окне Анализ данных следует выбрать элемент Гистограмма. Пример 1. Определим, как часто параметр, характеризующий качество радиоканала, принимает значения из указанных диапазонов. Будем использовать результаты наблюдений, представленные на рисунке 1.   Порядок действий:  1. Заполним рабочий лист А3:А22 исходными данными (рисунок 1). 2. В свободный диапазон клеток (С3:С10) введем верхние границы интервалов (от -10 до 25 с шагом 5). 3. Выделим блок ячеек столбца, смежного со столбцом интервалов (D3:D11). Для того чтобы подсчитать количество значений, превышающих нижнюю границу интервала, выделяется диапазон, на одну ячейку  больше, чем диапазон интервалов. 4. В диапазон D3:D11введем формулу  { =ЧАСТОТА(E2:E15;J2:J6)}. 5. Для этого воспользуемся библиотекой функции вкладки Формулы. Из категории «Статистические» выберем функцию ЧАСТОТА(). В диалоговом окне функции заполним поля  массива выборки и массива интервалов (рисунок 1). Для расчета элементов массива, не выходя из окна диалога, нажмем комбинацию клавиш + + . Рисунок 1 – Пример заполнения диалогового окна функции ЧАСТОТА() Рисунок 2 – Результат частотного анализа Рассмотрим еще один пример обработки статистических наблюдений. Пример 2. По результатам отчетных данных о продажах определим, как распределяются объемы продаж в рассматриваемых интервалах.   Порядок действий:  1. Заполним диапазон рабочего листа исходными данными. Для расчета совокупного потока денежных средств воспользуемся формулой   =C2+B3 (рисунок 3). 2. В свободном диапазоне, например, Е2:Е5, введем верхние границы интервалов (например, 1000, 2000, 3000 …). 3. Выберем элемент Гистограмма в меню Анализ данных. 4. Заполним диалоговое окно Мастера в соответствии с рисунком 3: в качестве входного диапазона укажем диапазон В2:В25, диапазона интервалов – Е2:Е5, выведем результаты и график на новый рабочий лист. 5. Нажмем ОК. Результаты частотного анализа представлены на рисунке 4. Рисунок 3 – Вид рабочего листа с заполненными диапазонами исходных данных, интервалов и окном Гистограмма Рисунок 4 – Результаты частотного анализа с помощью инструмента Гистограмма Контрольное задание для самопроверки Задание 1. На рабочем листе MS Excel введите данные об объемах выполненных заказов в 20 филиалах фирмы  за сентябрь в виде таблицы, фрагмент которой показан на рисунке 5. По результатам выборки определите, сколько значений попадает в заданные интервалы значений. Например, от 0 до 1000,  от 1001 до 1500, от 1501 до 2000, от 2001 до 2500, свыше 2500. Используйте функцию ЧАСТОТА() для массива значений.   А B C D E 1 № филиала Сентябрь       2 1 1230   1000   3 2 980   1500   4 … ….   2000   5       2500   6           Рисунок 5 – Фрагмент рабочего листа с исходными данными для частотного анализа Задание 2. Определите возрастную структуру населения района на основе статистических данных. Оформите данные выборки наблюдений на рабочем листе в виде двумерного массива произвольной размерности. Для решения задачи воспользуйтесь возможностями Пакета анализа. Решение задач прогнозирования в среде MS Excel Под прогнозом понимается научно-обоснованное описание возможных состояний системы в будущем. Любая организация при разработке краткосрочных и долгосрочных планов вынуждена прогнозировать значение важнейших показателей экономической деятельности, таких как объем продукции или услуг, продаж, издержек производства и т.д. В настоящее время для решения задач прогнозирования используют современные информационные технологии, программные средства которых включают статистические программные пакеты. Составить прогноз можно с использованием приложения Excel для автоматического создания будущих значений на основе существующих данных или для автоматического создания экстраполированных значений, основанных на линейном или экспоненциальном приближении. Для решения задач прогнозирования в среде MS Excel используется Пакет анализа, включающий инструменты анализа. Выбрав инструмент для анализа данных, и задав необходимые параметры, можно быстро решать сложные статистические задачи, сопровождая их графической интерпретацией. Предварительный анализ временных рядов экономических показателей предполагает выявление аномальных значений уровней ряда, нарушающих определение наличия тренда. Для устранения аномальных значений показателей применяется процедура сглаживания временного ряда. При этом для выявления тенденции ряда фактические значения заменяются расчетными. При выборе метода прогнозирования учитывают характер изменения случайной величины временного ряда. Если вариация средних значений незначительна и все наблюдения временного ряда имеют одинаковую значимость для прогноза, то используют метод скользящего среднего. Скользящие средние позволяют сгладить (отфильтровать) случайные и периодические колебания временного ряда. Сглаживание простой скользящей средней является наиболее распространенной процедурой сглаживания. В инструменте анализа MS Excel Скользящее среднее количество значений, участвующих в вычислении прогнозируемой величины, задается параметром Интервал. Величину интервала сглаживания выбирают тем больше, чем более необходимо сгладить мелкие колебания значений ряда. Метод Простой скользящей средней дает хорошие результаты в динамических рядах с линейной тенденцией развития.  Если для прогноза наиболее значимыми являются последние результаты наблюдений, то используют метод экспоненциального сглаживания. В методе экспоненциального сглаживания каждое значение участвует в формировании прогнозируемых значений с переменным весом, который убывает по мере «устаревания» данных. В инструменте анализа MS Excel Экспоненциальное сглаживание весовой коэффициент, или параметр сглаживания,  определяется параметром Фактор затухания. Обычно для временных рядов в экономических задачах величину параметра сглаживания задают в интервале от 0,1 до 0,3. Начальное расчетное значение  в процедуре Экспоненциальное сглаживание пакета Анализа MS Excel принимается равным уровню первого члена ряда. Метод  обеспечивает хорошее согласование исходных и расчетных данных для первых значений ряда. Если конечные вычисленные значения значительно отличаются от соответствующих исходных данных, то целесообразно изменить величину параметра сглаживания. Оценить величины расхождений можно на основе стандартных погрешностей и графика, которые пакет Анализа позволяет вывести вместе с расчетными значениями ряда. Рассмотрим возможности прогнозирования показателей деятельности предприятия, занимающегося предоставлением услуг связи.  Пример 1. Вычислить прогнозируемое значение величины объема продукции (услуг) предприятия методом скользящего среднего.   Для решения создадим на рабочем листе столбец, содержащий данные об объеме услуг в млн. руб., оказанных предприятием за последние 10  лет. Выявим тенденцию изменения показателя с помощью скользящего среднего. Выберем трехгодичный период скользящего среднего, так как за меньший период скользящее среднее может не отразить тенденцию, а за более продолжительный – сгладит ее. Для вычислений воспользуемся способом прямого введения формулы. Чтобы получить трехлетнее скользящее среднее объема выполненных услуг для нашего примера, введем в ячейку B5 формулу для вычисления  =СРЗНАЧ(A2:A4). Скопируем формулу в интервал B6:B11 (рисунок 6). Рисунок 6 – Вычисление простого скользящего среднего Проиллюстрируем результаты графиком, отражающим динамику изменения исходных данных и скользящего среднего (рисунок 7). Рисунок 7 – График тенденции изменения показателя объема услуг, полученной методом простого скользящего среднего Другим способом решения является использование для определения скользящего целого Пакета анализа. Опишем порядок действий для решения задачи: 1. Выполним команду Данные/Анализ данных и выберем из списка инструментов анализа Скользящее среднее. 2. В диалоговом окне укажем параметры для вычисления скользящего среднего: · в качестве входного интервала выделим блок ячеек, содержащий данные об объеме услуг; · укажем Интервал- 3 (по умолчанию используется 3), в качестве выходного интервала – любую ячейку рабочего листа (просто щелкните на ячейке рабочего листа, начиная с которой должны выводиться результаты); · зададим вывод графика и стандартных погрешностей. Excel сам выполнит работу по внесению значений в формулу для вычислений скользящего среднего. Из-за недостаточного количества данных при вычислении среднего значения для первых результатов наблюдений в начальных ячейках выходного диапазона будет выведено значение ошибки #Н/Д. Проанализируйте используемые расчетные формулы и полученные результаты. Аналогично вычислите пятилетние простые скользящие средние. Сравните результаты сглаживания для двух вариантов расчета.  Пример 2. Вычислим прогнозируемое значение величины объема продукции (услуг) предприятия методом экспоненциального сглаживания.     Порядок действий:  1. На листе MS Excel создадим список, содержащий данные о численности сотрудников фирмы за последние 10  лет. Данные введем произвольно так, чтобы прослеживалась тенденция. 2. Проведем сглаживание временного ряда с использованием экспоненциальной средней с параметрами сглаживания 0,1 и затем 0,3. По результатам  расчетов построим график и определим, какой из полученных временных рядов носит более сглаженный характер. 3. Воспользуемся командой меню Данные/Анализ данных и выберем из списка инструментов анализа Экспоненциальное сглаживание. Укажем параметры для вычисления скользящего среднего: ¾ в качестве входного интервала выделим блок ячеек, содержащий данные о численности; ¾ укажем значение Фактора затухания, а в качестве выходного интервала – любую ячейку рабочего листа; ¾ зададим вывод графика и стандартных погрешностей. 4.   Добавим линии тренда на полученных графиках. Для этого выберем линию графика (просто щелкните правой кнопкой мыши на линии графика) и в контекстном меню – пункт Добавить линию тренда. В диалоговом окне выберем наиболее подходящий для наших данных тип тренда (например, линейная фильтрация) и установим флажок вывода уравнения аппроксимирующей кривой на графике.   В экономическом прогнозировании применяют различные модели роста. Кривая роста представляет собой некоторую функцию, аппроксимирующую заданный динамический ряд. При разработке прогноза с использованием кривых роста производят выбор кривых, форма которых соответствует динамике временного ряда, оцениваются их параметры, проверяется адекватность выбранных кривых прогнозируемому процессу и производится расчет точечного или интервального прогноза. Существует несколько методов подбора кривых. Одним из самых простых является визуальный метод. Если на графике недостаточно просматривается тенденция развития (тренд), то производят, как описано выше, сглаживание ряда, а затем подбирается кривая, соответствующая новому ряду. В этом случае также применяются современные программные средства компьютерных систем. В  MS Excel встроены специальные функции, позволяющие рассчитывать прогнозируемые значения на определенный период. Excel проводит линейную экстраполяцию, т.е. рассчитывает наиболее подходящую прямую, которая проходит через серию заданных точек. Задача заключается в нанесении на график набора точек, а затем в подборе линии, по которой можно проследить развитие функции с наименьшей ошибкой. Эта линия называется линией ТРЕНДА. Пользователь может использовать результат вычислений для анализа тенденций и краткосрочного прогнозирования. Можно ввести последовательность значений, соответствующих простому линейному или экспоненциальному тренду, с помощью маркера заполнения или команды Ряд. Для расширения сложных и нелинейных данных можно использовать функции или средство регрессионного анализа, доступное в надстройке «Пакет анализа». Excel может автоматически проводить линии тренда, различных типов непосредственно на диаграмме. Вычисления можно производить двумя способами: ¾ с помощью маркера заполнения; ¾ с помощью функций рабочего листа. Опишем порядок действий для каждого из способов.  Первый способ: Линейное приближение  1. Выделить ячейки с результатами наблюдений. 2. Перетащить с помощью левой кнопки мыши маркер заполнения, чтобы выделенными оказались также и ячейки, для которых необходимо рассчитать прогнозируемые значения. Рассчитанные таким образом значения соответствуют линейному прогнозу.  Экспоненциальное приближение  1. Выделить ячейки с результатами наблюдений. 2. Перетащить маркер заполнения с помощью правой кнопки мыши, чтобы выделенными оказались также и ячейки, для которых необходимо рассчитать прогнозируемые значения. 3. В появившемся контекстном меню выбрать команду «Экспоненциальное приближение».   Второй способ В MS Excel встроены статистические функции рабочего листа. ТЕНДЕНЦИЯ() - возвращает значения в соответствии с линейной аппроксимацией по методу наименьших квадратов. РОСТ() - возвращает значения в соответствии с экспоненциальным трендом. Использование этих функций – это еще один способ проведения регрессионного анализа. Формат функции ТЕНДЕНЦИЯ()   =ТЕНДЕНЦИЯ (изв_знач_Y; изв_знач_X; нов_знач_X; константа)   Функция РОСТ() имеет тот же формат, но возвращает значения в соответствии с экспоненциальным трендом. Прогнозирование с использованием функции ЛИНЕЙН() Для линейной регрессии линия тренда, полученное методом наименьших квадратов, представляется простым уравнением y = mx+b где у – зависимая переменная, которая представляет значение тренда; x – независимая переменная; m – наклон линии тренда; b – константа, представляющая собой стартовое значение тренда. Функция ЛИНЕЙН() вычисляет коэффициент наклона и константу на линии тренда, используя метод наименьших квадратов для получения прямой, наилучшим образом аппроксимирующей имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Зная эти коэффициенты можно вычислить прогнозируемые значения, подставив новые значения  x в уравнение линейной регрессии. Уравнение для прямой линии имеет следующий вид: y = mx + b или y = m1x1 + m2x2 + ... + b (в случае нескольких диапазонов значений x), где зависимое значение y — функция независимого значения x; значения m — коэффициенты, соответствующие каждой независимой переменной x; b — постоянная. Формат функции =ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика) Y, x и m могут быть векторами. Функция возвращается массив {mn;mn-1;...;m1;b} и должна задаваться в виде формулы массива. Функция ЛИНЕЙН() может также возвращать дополнительную регрессионную статистику. Известные_значения_y — множество значений известных y для уравнения y = mx + b. Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная. Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.   Известные_значения_x — необязательное множество значений x, которые уже известны для соотношения y = mx + b. Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором. Если известные_значения_x пропущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.   Конст  — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если конст имеет значение ИСТИНА или пропущено, то b вычисляется обычным образом. Если аргумент конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y=mx.   Статистика  — логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН() возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ или пропущен, то функция ЛИНЕЙН() возвращает только значения m и b. Вычисление значений степенного тренда и прогнозных значений Экспоненциальный и логарифмический тренды могут использоваться для прогнозов в случаях, если в различных частях кривой это функции имеют очень разный темп роста значений. Линия экспоненциального тренда начинает расти медленно, а затем постоянно растет с возрастающим темпом. Логарифмический тренд вначале резко возрастает, а затем медленно сбавляет темп роста. На практике в большинстве статистических показателей таких резких изменений динамики не наблюдается, чаще это модели с некоторым стабильным ростом. В этом случае логично использовать линейный тренд, но если он дает плохую аппроксимацию, то следует попробовать использовать степенной тренд. Регрессионное уравнение для степенного тренда имеет следующий вид: y = mxb   ,   где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная. Зная эти коэффициенты и значения независимой переменной x, можно получить значения соответствующих точек на линии тренда. В Excel нет функции, позволяющей непосредственно вычислить значения коэффициентов m и b. В этом случае можно «сгладить» степенную кривую, преобразовав масштаб переменных x и y в логарифмическую шкалу, т.е. преобразовать степенную регрессию в линейную с помощью функции LN(). В этом случае в примере на рисунке 8 формула массива для анализа зависимости объема продаж от цены будет иметь вид: {=ЛИНЕЙН(LN(B2:B10);LN(A2:A10))} Рисунок 8 – Расчет коэффициентов степенного тренда Полученный коэффициент b в регрессионном уравнении применяется как экспонента, поэтому делать преобразование обратное логарифмического не нужно. Коэффициент а  представляется в логарифмической форме, поэтому в ячейке G2 по формуле =EXP(F2) вычислено реальное значение коэффициента m, которое затем используется в формуле для вычисления прогнозных значений (рисунок 9). Рисунок 9 – Расчет прогнозных значений степенного тренда Использование множественного регрессионного анализа Во множественном регрессионном анализе чаще всего решаются задачи «ЧТО-ЕСЛИ». Все описанные приемы регрессионного анализа можно применять для анализа нескольких независимых переменных. На основе отчетных данных об объемах  продаж (рисунок 10), включающих расходы на рекламу и цену товара, проведем регрессионный анализ с помощью функции ТЕНДЕНЦИЯ(). Аргументы функции и результаты прогноза приведены на рисунках 11, 12. Рисунок 10 – Окно функции Тенденция Рисунок 11 – Окно функции Тенденция Рисунок 12 – Результаты множественного регрессионного анализа Корреляционный анализ в MS Excel   Наличие взаимосвязи между двумя явлениями, свойствами или показателями определяется с помощью корреляционного анализа. Коэффициент корреляции между двумя массивами переменных, например, x и y – это безразмерная величина в диапазоне от -1 до 1, определяющая степень линейной зависимости между x и y. Если коэффициент корреляции близок к +1, это означает наличие сильной положительной линейной зависимости между значениями исследуемых данных. Коэффициент корреляции близкий к -1 говорит о наличии сильной отрицательной линейной зависимостью между x и y. Коэффициент корреляции близкий к 0 означает слабую линейную зависимость между массивами переменных. В MS Excel существует специальная функция КОРРЕЛ(), относящаяся к категории статистических. Функция КОРРЕЛ(), возвращает коэффициент корреляции меду интервалами ячеек массив1 и массив2.   Формат функции =КОРРЕЛ(массив1;массив2), где   массив1   — это ячейка интервала значений; массив2   — это второй интервал ячеек со значениями. Замечания. 1. Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются. 2. Если массив1 и массив2 имеют различное количество точек данных, то функция КОРРЕЛ() возвращает значение ошибки #Н/Д. 3. Если массив1 либо массив2 пуст, или если σ (стандартное отклонение) их значений равно нулю, то функция КОРРЕЛ() возвращает значение ошибки #ДЕЛ/0!. Пример. Определим зависимость активного населения страны и числа предприятий. Вариант решения приведен на рисунке 13. Рисунок 13 – Вычисление коэффициента корреляции для двух массивов значений Рассчитанный коэффициент корреляции указывает на наличие сильной положительной линейной зависимости между численностью активного населения и числом предприятий. Оценим степень корреляции годовой доходности акций трех компаний за ряд лет. Воспользуемся возможностями Пакета анализа, который является удобным инструментом для поиска корреляций. На вкладке Данные в группе Анализ в диалоговом окне выберем команду Корреляция (рисунок 14). В качестве входного интервала укажем диапазон исследуемых данных вместе с заголовками столбцов ($B$4:$D$30). Абсолютные ссылки Excel установит автоматически после выделения диапазона с помощью курсора. Установим флажок Метки в первой строке. Укажем верхнюю левую ячейку выходного интервала ($F$4), куда будут записаны результаты расчетов. На рисунке 15 приведен лист с исходными данными и матрицей коэффициентов корреляции. Рисунок 14 – Диалоговое окно Корреляция Рисунок 15 – Вид рабочего листа с исходными данными и коэффициенты корреляции годовой доходности акций Как видно из рисунка, некоторые элементы матрицы оказались пустыми. Для того, чтобы полностью заполнить матрицу воспользуемся инструментом Специальная вставка: 1. Скопируем матрицу с помощью команды контекстного меню; 2. Щелкнем мышью в ячейке свободного диапазона и выберем в контекстном меню щелчком на кнопке Вставить вкладки Главная команду Специальная вставка (рисунок 16). В диалоговом окне Специальная ставка установим флажок транспонировать. После щелчка по кнопке ОК на листе отобразится транспонированная матрица (рисунок 17). Рисунок 16 – Диалоговое окно Специальная вставка 1. Скопируем транспонированную матрицу. 2. Выделим верхнюю левую ячейку исходной матрицы и выполним команду Специальная вставка. 3. В диалоговом окне Специальная вставка установим флажок пропускать пустые ячейки и щелкнем по кнопке ОК.  Результат заполнения матрицы корреляции представлен на рисунке 17. Рассчитанные коэффициенты корреляции говорят о слабой положительной линейной зависимости между доходностью акций компаний «Электросвязь» и «Вымпел» и слабой отрицательной линейной зависимости между доходами компаний «Бином» – «Вымпел» и «Бином» – «Электросвязь». Рисунок 17 – Результат заполнения матрицы коэффициентов корреляции