сервер. Учебнометодическое пособие по выполнению пратических работ тольятти 2018 2 содержание
 Скачать 7.66 Mb.
|
|
СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид: =СЧЁТЕСЛИ(диапазон;критерий) Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений. Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области. 1. Выделяем первый элемент дополнительного столбца, в котором будет производиться подсчет количества совпадений. Далее щелкаем по пиктограмме «Вставить функцию». 107 2. Происходит запуск Мастера функций. Переходим в категорию «Статистические». Находим в перечне наименование «СЧЁТЕСЛИ». После его выделения щелкаем по кнопке «OK». 3. Происходит запуск окна аргументов оператора СЧЁТЕСЛИ. Как видим, наименования полей в этом окне соответствуют названиям аргументов. Устанавливаем курсор в поле «Диапазон». После этого, зажав левую кнопку мыши, выделяем 108 все значения столбца с фамилиями второй таблицы. Как видим, координаты тут же попадают в указанное поле. Но для наших целей следует сделать данный адрес абсолютным. Для этого выделяем данные координаты в поле и жмем на клавишу F4. Как видим, ссылка приняла абсолютную форму, что характеризуется наличием знаков доллара. Затем переходим к полю «Критерий», установив туда курсор. Щелкаем по первому элементу с фамилиями в первом табличном диапазоне. В данном случае оставляем ссылку относительной. После того, как она отобразилась в поле, можно щелкать по кнопке «OK». 4. В элемент листа выводится результат. Он равен числу «1». Это означает, что в перечне имен второй таблицы фамилия «Гринев В. П.», которая является первой в списке первого табличного массива, встречается один раз. 109 5. Теперь нам нужно создать подобное выражение и для всех других элементов первой таблицы. Для этого выполним копирование, воспользовавшись маркером заполнения, как это мы уже делали прежде. Ставим курсор в нижнюю правую часть элемента листа, который содержит функцию СЧЁТЕСЛИ, и после преобразования его в маркер заполнения зажимаем левую кнопку мыши и тянем курсор вниз. 110 6. Как видим, программа произвела вычисление совпадений, сравнив каждую ячейку первой таблицы с данными, которые расположены во втором табличном диапазоне. В четырех случаях результат вышел «1», а в двух случаях – «0». То есть, программа не смогла отыскать во второй таблице два значения, которые имеются в первом табличном массиве. Конечно, данное выражение для того, чтобы сравнить табличные показатели, можно применять и в существующем виде, но есть возможность его усовершенствовать. Сделаем так, чтобы те значения, которые имеются во второй таблице, но отсутствуют в первой, 111 выводились отдельным списком. 1. Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ, а именно сделаем её одним из аргументов оператора ЕСЛИ. Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ. В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию». 2. Открывается окно аргументов функции ЕСЛИ. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0» без кавычек. После этого переходим к полю «Значение если истина». Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА. Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение: СТРОКА(D2) Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK». 112 3. Как видим, первый результат отображается, как «ЛОЖЬ». Это означает, что значение не удовлетворяет условиям оператора ЕСЛИ. То есть, первая фамилия присутствует в обоих списках. 113 4. С помощью маркера заполнения, уже привычным способом копируем выражение оператора ЕСЛИ на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк. 114 5. Отступаем от табличной области вправо и заполняем колонку номерами по порядку, начиная от 1. Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения. 115 6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию». 116 7. Открывается Мастер функций. Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ». Щелкаем по кнопке «OK». 117 8. Функция НАИМЕНЬШИЙ, окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения. В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений», который мы ранее преобразовали с помощью функции ЕСЛИ. Делаем все ссылки абсолютными. В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK». 9. Оператор выводит результат – число 3. Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа. 118 10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС. Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ. После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой (;). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию». 119 11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK». 12. Запускается окно аргументов функции ИНДЕКС. Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке. Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ. От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек. 120 В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом. Жмем на кнопку «OK». 13. После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон. 121 Способ 5: сравнение массивов в разных книгах При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке. 122 Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран. Составить отчет по проделанной работе. 123 Практическое занятие №14. Применение функции ВПР в Excel Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве. Стоимость материалов – в прайс-листе. Это отдельная таблица. Необходимо узнать стоимость материалов, поступивших на склад. Для этого нужно подставит цену из второй таблицы в первую. И посредством обычного умножения мы найдем искомое. Алгоритм действий: 1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек. 2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и 124 жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы». 3. Откроется окно с аргументами функции. В поле «Искомое значение» - диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице. 4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить. 5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $. 6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» - ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения. 125 Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат. Теперь найти стоимость материалов не составит труда: количество * цену. Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой». 1. Выделяем столбец со вставленными ценами. 2. Правая кнопка мыши – «Копировать». 3. Не снимая выделения, правая кнопка мыши – «Специальная вставка». 4. Поставить галочку напротив «Значения». ОК. 126 Формула в ячейках исчезнет. Останутся только значения. БЫСТРОЕ СРАВНЕНИЕ ДВУХ ТАБЛИЦ С ПОМОЩЬЮ ВПР Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами. 1. В старом прайсе делаем столбец «Новая цена». 2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: =ВПР($A$2:$A . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2. 127 Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу. ФУНКЦИЯ ВПР В EXCEL С НЕСКОЛЬКИМИ УСЛОВИЯМИ До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям. Таблица для примера: Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику. Дело осложняется тем, что от одного поставщика поступает несколько наименований. 1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы». 128 2. Таким же образом объединяем искомые критерии запроса: 3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: =ВПР(A2;Лист Excel находит нужную цену. Рассмотрим формулу детально: 1. Что ищем. 2. Где ищем. 3. Какие данные берем. ФУНКЦИЯ ВПР И ВЫПАДАЮЩИЙ СПИСОК Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена. Сначала сделаем раскрывающийся список: 1. Ставим курсор в ячейку Е8, где и будет этот список. 2. Заходим на вкладку «Данные». Меню «Проверка данных». 129 3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов. 4. Когда нажмем ОК – сформируется выпадающий список. Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена). 1. Открываем «Мастер функций» и выбираем ВПР. 2. Первый аргумент – «Искомое значение» - ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: =ВПР(E8;A2:B 3. Нажимаем ВВОД и наслаждаемся результатом. Изменяем материал – меняется цена: Составить отчет по проделанной работе. 130 Практическая работа №15. Использование MS Query для работы с OLAP-кубами 1. Запустить MS Excel. Выполнить команду Данные|Из других источников|Из MS Query. 2. В окне выбор источника данных дважды щелкнуть «База данных MS Access Database*» (или выделить и щелкнуть «ОК»). 3. В окне «Выбор базы данных» выбрать папку учебной группы с файлом учебной базы данных NWIND.accdb. Щелкнуть по имени файла. 4. В окне «Создание запроса (выбор столбцов)» щелкнуть по крестику у запроса «Промежуточная сумма заказа». 5. Щелкнуть мышью и выделить столбец «ПромежуточнаяСумма». Затем щелкнуть по значку «>» и переместить столбец «ПромежуточнаяСумма» в область «Столбцы запроса». 6. В окне «Создание запроса (выбор столбцов)» щелкнуть по крестику у таблицы «Заказы». 7. Щелкнуть мышью и выделить столбец «ДатаРазмещения». Затем щелкнуть по значку «>» и переместить столбец «ДатаРазмещения» в область «Столбцы запроса». 8. В окне «Создание запроса (выбор столбцов)» щелкнуть по крестику у таблицы «Клиенты». 9. Щелкнуть мышью и выделить столбец «Название». Затем щелкнуть по значку «>» и переместить столбец «Название» в область «Столбцы запроса». 10.Щелкнуть мышью и выделить столбец «Страна». Затем щелкнуть по значку «>» и переместить столбец «Страна» в область «Столбцы запроса». Щелкнуть кнопку «Далее». 11.В окне «Создание запроса: отбор данных» щелкнуть кнопку «Далее». 12.В окне «Создание запроса: порядок сортировки» щелкнуть кнопку «Далее». 13.В окне «Создание запроса: заключительный шаг» щелкнуть кнопку «Просмотр или изменение данных в Microsoft Query». Щелкнуть кнопку «Готово». 14. Выполнить команду Файл|Сохранить как и сохранить запрос в файле с именем OLAP-куб.dqy. 15.Выполнить выход из MS Query. 16. Выполнить опцию «Вернуть данные в MO Excel». 17. В окне импорта данных выбрать «Отчет сводной таблицы». 18. С панели «Сводные таблицы» перетащить мышью поле «ДатаРазмещения» в область строк (отмечено «Перетащите сюда поля строк»). 19. С панели «Сводные таблицы» перетащить мышью поле «Страна» в область столбцов (отмечено «Перетащите сюда поля столбцов»). 20. С панели «Сводные таблицы» перетащить мышью поле «Название» в область столбцов (рядом с полем «Страна»). 21. С панели «Сводные таблицы» перетащить мышью поле «Сумма из ПромежуточнаяСумма» в область Значения (отмечено «Перетащите сюда элементы данных»). 131 22. Выполнить операции фильтрации данных в окне сводной таблицы и создать сводную диаграмму. 23. Выйти из MS Excel. 24. Войти в MS Query. 25. В окне диалога «Кубы OLAP» - Обзор выбрать файл OLAP-куб.dqy и повторить процедуру создания сводной таблицы. Составить отчет по проделанной работе. 132 Практическая работа №16. Создание витрины данных в MS SQL Server Задание 1: Создание витрины данных страхового агентства. Создаем с помощью СУБД MS SQL Server БД INSURANCE, которая состоит из следующих таблиц (рисунки 12.1-12.4): Agents (Страховые агенты): Рисунок 12.1 Vids (Виды страхования): Рисунок 12.2 Dogovors (Договоры страхования): Рисунок 12.3 Months (Месяцы) Рисунок 12.4 Создаем диаграмму вида (рис. 12.5): 133 Рисунок 12.5 Заполняем таблицы данными по примеру (рисунки 12.6-12.9). Рисунок 12.6 Рисунок 12.7 Рисунок 12.8 134 Рисунок 12.9 Вводим и выполняем следующий код: use insurance go drop table itogs select dogovors agent_id , dogovors vid_id , month ( dzd ) as mes_id , sum ( strax_sum ) as SS , sum ( prem ) as SP into itogs from dogovors , agents , vids where vids vid_id = dogovors vid_id and agents agent_id = dogovors agent_id group by dogovors agent_id , dogovors vid_id , month ( dzd ) order by dogovors agent_id , dogovors vid_id , month ( dzd ) use insurance go drop table olap_ins select fio , name_vid , ss , sp , name_mes into olap_ins from itogs , agents , vids , months where itogs agent_id = agents agent_id and itogs vid_id = vids vid_id and itogs mes_id = months mes_id В результате создается новая таблица « olap_ins », представляющая собой OLAP-куб витрины данных, диаграмма которой изображена на рис. 12.10. |