Файл: Лабораторные работы.docx

Добавлен: 19.10.2018

Просмотров: 7104

Скачиваний: 112

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

СОДЕРЖАНИЕ

Задание

Форматирование деловых документов

Задание

Лабораторная работа №3 Шаблоны писем, рассылки

«Почтовая рассылка и распечатка бланков Word с данными из Excel»

Выпадающий список с наполнением в электронных таблицах Excel

Ссылки по теме

Веб-опрос с помощью Excel и SkyDrive

Шаг 1. Создаем опрос в SkyDrive

Шаг 2. Создаем вопросы

Шаг 3. Публикация опроса и сбор данных

Ссылки по теме

Проектная диаграмма Ганта с помощью условного форматирования

Ссылки по теме

Лабораторная работа №7. Деловая графика Интерактивная диаграмма

Шаг 1. Создаем дополнительную таблицу для диаграммы

Шаг 2. Добавляем флажки (checkboxes) для валют

Шаг 3. Транслируем данные в дополнительную таблицу

Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования

Шаг 5. Создаем динамический именованный диапазон

Шаг 6. Строим диаграмму

Ссылки по теме

Анализ детализации мобильной связи (Билайн)

Постановка задачи для принятия решения.

Шаг 1. Выгружаем детализацию

Шаг 2. Доработка детализации

Шаг 3. Создание отчетов

Ссылки по теме

Решение

Теоретическая часть.

Организация данных

Целостность данных

Задание 1

Создание инфологической и логической моделей базы данных.

Технология работы

Задание 2

Создание реляционной базы данных.

Технология работы

Задание 3

Создание форм для ввода данных в таблицы.

Технология работы

Лабораторная работа №11. Формирование сложных запросов

Задание

Технология работы

Лабораторная работа № 12. Создание сложных форм и отчетов

Задание 1

Создание сложных форм.

Технология работы

Задание 2

Создание сложных отчетов.

Технология работы

Задание 3

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

Технология работы

Лабораторная работа № 13 Заполнение бланков данными из таблицы

Постановка задачи

Шаг 1. Создаем бланк

Шаг 2. Подготовка таблицы платежей

Шаг 3. Связываем таблицу и бланк

Шаг 4. Чтобы не было двух "х"...

Ссылки по теме

Учебная информация

Лабораторная работа № 16 Защита данных в Microsoft Excel

Уровень 0. Защита от ввода некорректных данных в ячейку

 Уровень 1. Защита ячеек листа от изменений

Уровень 2. Выборочная защита диапазонов для разных пользователей

Уровень 3. Защита листов книги

Уровень 4. Шифрование файла

Ссылки по теме

Лабораторная работа №5 Облачные вычисления.

Веб-опрос с помощью Excel и SkyDrive

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

Самое простое и эффективное решение в такой ситуации - использовать новую возможность бесплатного облачного сервиса SkyDrive и создать на его основе веб-опрос. Участники смогу зайти на специальную страницу в интернете и ответить на вопросы в созданной вами форме. Причем результаты будут автоматически собираться в вашу таблицу Excel.

Шаг 1. Создаем опрос в SkyDrive

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

Skydrive интегрирован с Office и Windows и позволяет получать доступ к вашим файлам и даже работать с ними с помощью встроенного интернет-варианта Office - Office WebApps. Файлы, помещенные в папку Skydrive на одном из ваших компьютеров, автоматически синхронизируются с облачным хранилищем и почти мгновенно становятся доступны на всех ваших подключенных к сервису устройствах (домашний ПК, ноутбук, смартфон, планшет и т.д.).

На вашей странице в SkyDrive в верхнем меню создания папок и файлов выбираем пункт для создания опроса Создать - Опрос Excel (Create - Excel survey):

На следующем шаге нам предложат придумать имя файла, куда будут автоматически помещены собранные результаты (файл будет находится в корне вашего хранилища SkyDrive):

Жмем кнопку Создать (Create) и переходим к созданию начинки нашего опроса.

Шаг 2. Создаем вопросы

После создания файла на экране появится форма опроса. В верхней части сразу можно ввести название вашего опроса и его краткое описание, которое увидят участники. Затем можно переходить к созданию вопросов с помощью кнопки Добавить вопрос (Add New Question). Параметры каждого вопроса задаются простой формой с несколькими полями:

  • Вопрос (Question) - собственно, сам вопрос

  • Подзаголовок вопроса (Question Subtitle) - комментарий к вопросу или уточнение (если есть)

  • Тип отклика (Response Type) - ожидаемый тип данных ответа на вопрос. Поддерживаются несколько стандартных вариантов (текст, число, дата, время, выпадающий список, да/нет): 

  • Обязательно (Required) - обязателен ли ответ на этот вопрос (можно ли его пропустить и не отвечать на него)

  • Ответ по-умолчанию (Default Answer) - если пользователь не ввел свой вариант ответа, то будет использован этот вариант

Количество вопросов не ограничено.

Шаг 3. Публикация опроса и сбор данных

Когда ввели все вопросы в форме создания отчета - жмем кнопку Опубликовать опрос (Share Survey) внизу окна и переходим к публикации опроса. Skydrive сформирует ссылку на созданную веб-страницу с отчетом и покажет ее нам:


Эту ссылку мы должны разослать нашим потенциальным участникам опроса. Зайдя по ссылке, они увидят вот такую красоту:

Обратите вниманию, что у участников опроса может не быть регистрации на SkyDrive - это не обязательно.

Собранные результаты мы всегда сможем увидеть в Excel Web App, если откроем наш файл Результаты опроса.xlsx, который был автоматически создан в корне хранилища SkyDrive:

Причем, никто не мешает вам немного "допилить" этот файл, добавив пару формул или диаграмму, чтобы представить собранные данные в более удобной форме:

При необходимости, можно открыть наш файл в полноценной настольной версии Microsoft Excel с помощью кнопки OPEN IN EXCEL в верхней части веб-страницы. Связь с исходным файлом из SkyDrive при этом не теряется.

Посмотреть, как этот работает вживую 

Microsoft Excel Web App позволяет даже встроить живой фрагмент созданного файла в вашу веб-страницу или блог (кнопка Share - Embed на вкладкеHOME). Выглядеть это будет примерно так:

Правда, здорово? :)

Ссылки по теме


Задание. Составить инструкцию - отчет по созданию веб-опросов в виде презентации или с помощью Camtasia Studio, затем конвертирую в анимированный gif.



Лабораторная работа №6

Проектная диаграмма Ганта с помощью условного форматирования

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

Идея состоит в использовании условного форматирование, чтобы заставить Excel заливать ячейку заданным цветом, если она по дате попадает между началом и концом этапа. Для этого выделите весь диапазон, где должна быть диаграмма (в нашем примере - начиная с ячейки D3 и до конца таблицы) и затем:

  • в Excel 2003 и старше: выберите в меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) и введите в появившуюся строку следующую формулу: 

     

  • в Excel 2007 или новее: жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - New Rule), выбираем последний тип Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format) и вводим аналогичную формулу: 

     

По сути, эта формула делает простую вещь - функция И (AND) проверяет обязательное выполнение двух условий, чтобы дата для текущей ячейки была позже, чем дата начала этапа и раньше даты окончания. Если оба эти условия выполняются, то ячейка находится внутри этапа, т.е. должна быть залита. Нажав на кнопку Формат (Format) можно выбрать необходимый цвет.

Просто и красиво, не правда ли?

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

Основа здесь все та же - условное форматирование. Плюс добавлены:

  • задержка перед началом этапа (может быть положительно или отрицательной или =0) 

  • проценты выполнения по каждому этапу с их подсветкой на диаграмме

  • функция РАБДЕНЬ (WORKDAY) для расчета не календарных, а рабочих дней

  • подсветка праздников и выходных все тем же условным форматированием с помощью функции ДЕНЬНЕД (WEEKDAY)

  • координатная подсветка текущей строки и столбца с помощью специального макроса

Microsoft Project нам так, конечно, не переплюнуть, но приблизиться к нему в визуальной части вполне можно :)

Ссылки по теме


  • Задание. Составить инструкцию - отчет по созданию деловой графики в виде презентации или с помощью Camtasia Studio, затем конвертирую в анимированный gif.


Лабораторная работа №7. Деловая графика Интерактивная диаграмма

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

Вот, например, данные по курсам валют за несколько месяцев:

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

  • двигаться по оси времени вперед-назад в будущее-прошлое

  • приближать-удалять отдельные области диаграммы для подробного изучения деталей графика

  • включать-выключать отображение отдельных валют на выбор

Выглядеть это может примерно так:

Шаг 1. Создаем дополнительную таблицу для диаграммы

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

В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу (Format as Table) с вкладки Главная (Home):

Это даст нам следующие преимущества:

  • Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы

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

  • Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)

  • Каждая таблица получает собственное имя (в нашем случае – Таблица1 и Таблица2), которое можно затем использовать в формулах.

Шаг 2. Добавляем флажки (checkboxes) для валют

В  Excel 2007/2010 для этого необходимо отобразить вкладку Разработчик (Developer), а в Excel 2003 и более старших версиях – панель инструментовФормы (Forms). Для этого:

  • В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View – Toolbars – Forms)

  • В Excel 2007: нажать кнопку Офис – Параметры Excel – Отобразить вкладку Разработчик на ленте (Office Button – Excel options – Show DeveloperTab in the Ribbon)

  • В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File – Options – Customize Ribbon – Developer)

На появившейся панели инструментов или вкладке Разработчик (Developer) в раскрывающемся списке Вставить (Insert) выбираем инструмент Флажок(Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:

Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст (Edit text).


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

Наша цель в том, чтобы каждый флажок был привязан  к соответствующей желтой ячейке над столбцом с валютой. При включении флажка в связанную ячейку будет выводиться ИСТИНА (TRUE), при выключении – ЛОЖЬ (FALSE). Это позволит, в дальнейшем, проверять с помощью формул связанные ячейки и выводить в дополнительную таблицу либо значение курса из исходной таблицы для построения графика, либо #Н/Д (#N/A), чтобы график не строился.

Шаг 3. Транслируем данные в дополнительную таблицу

Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):

Заметьте, что при использовании команды Форматировать как таблицу (Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:

=ЕСЛИ(F$1;B4;#Н/Д)

Обратите внимание  на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.

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

Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования

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

Полосу прокрутки (Scroll bar) берем там же, где и флажки – на панели инструментов Формы (Forms) или на вкладке Разработчик (Developer):

Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:

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

Таким образом, после выполнения всего вышеизложенного, у вас должно быть две полосы прокрутки, при перемещении ползунков по которым значения в связанных ячейках должны меняться в интервале от 1 до 307.

Шаг 5. Создаем динамический именованный диапазон