Практические работы для старшеклассников

22

Практическая работа №11
MS Office 2010- 2016. формирование итогов. Сводные таблицы в ms excel

4 часа + 1(самостоятельная работа)

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

План проведения практической работы

1. Установка проверки вводимых данных

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

3. Итоговые функции для промежуточных итогов

4. Удаление промежуточных итогов

5. Элементы отчета сводной таблицы

6. Структурированный отчет сводной таблицы

7. Построение сводной диаграммы

Самостоятельная работа студентов

Вопросы для самоконтроля

1. Для решения каких задач применяется команда «Проверка»?

2. Как задаются условия проверки с помощью операций сравнения и логических операций?

3. Как задается условие проверки в виде формулы?

4. Какие действия может предпринимать MS Excel при вводе пользователем неправильных данных при применении команды «Проверка» для диапазона ячеек?

5. Что такое промежуточные итоги? Для решения каких управленческих задач применяется подведение промежуточных итогов?

6. Как можно удалить промежуточные итоги?

7. Как можно скрыть итоги для какого-либо значения поля?

8. Какие итоговые операции используются при подведении промежуточных итогов?

9. Что такое сводная таблица?

10. Для решения каких управленческих задач применяются сводные таблицы?

11. Каковы источники данных для сводной таблицы? На каком шаге использования мастера сводной таблицы задается тип источника данных для нее?

12. Сохраняется ли связь между источником данных и созданной на его основе сводной таблицей? Как можно отразить в сводной таблице данные, измененные в источнике?

13. Что такое макет сводной таблицы? На каком шаге использования мастера сводной таблицы задается макет сводной таблицы?

14. Перечислите все элементы сводной таблицы.

15. Что такое поле страниц сводной таблицы?

16. Что такое поля столбцов сводной таблицы?

17. Что такое поля строк сводной таблицы?

18. Что такое область данных сводной таблицы?

19. Что такое элемент сводной таблицы?

20. Что такое стрелка списка поля и индикатор развертывания для сводной таблицы?

21. Какими способами можно изменить структуру макета сводной таблицы после ее создания?

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

23. Что такое список полей и как можно выводить и скрывать его представление для сводной таблицы?

24. Что такое сводная диаграмма? Продемонстрируйте, как сохраняется в MS Excel связь между источником данных и сводной диаграммой?

Индивидуальное задание №11

Задание для ознакомления с основными операциями и командами по практической работе.

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2000 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» (№ варианта х на 10) и «Прибыль» (норма прибыли х Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

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

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

Индивидуальные задания по вариантам.

Вариант №1

1. Создайте таблицу из Приложения №1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 1998 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта + (30+ N п/п))* 1000)) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

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

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

Вариант №2

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Внесите данные в столбцы «Год» (семь последних лет), «Сумма» ((№ варианта + (30+ N п/п)* 100)) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

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

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

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

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

Вариант №3

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Внесите данные в столбцы «Год» (десять последних лет), «Сумма» ((№ варианта + (30+ N п/п))* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

3. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 10% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров в различные страны (нас интересует общая сумма, средняя прибыль и количество продаж по направлению по странам). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Иванову.

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

Вариант №4

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Внесите данные в столбцы «Год» (пять последних лет), «Сумма» ((№ варианта + (N п/п+30))* 100) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

3. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 5% от Суммы и не может быть более значения нормы прибыли, умноженной на Сумму.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров в различные годы (нас интересует общая сумма, средняя прибыль и количество продаж разных менеджеров по годам). Для ячеек области данных со значениями общей суммы и средней прибыли установите Финансовый тип со значением $. Скройте данные по менеджеру Иванову.

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

Вариант №5

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Внесите данные в столбцы «Год» (семь последних лет), «Сумма» ((№ варианта + (30+ N п/п))* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

3. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 15% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным типам (нас интересует общая сумма, средняя прибыль и количество продаж разных менеджеров по типам). Для ячеек области данных со значениями общей суммы и средней прибыли установите Финансовый тип со значением $. Скройте данные по менеджеру Петрову.

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

Вариант №6

1. Создайте таблицу из Приложения №3 в Microsoft Excel.

2. Внесите данные в столбцы «Год», «Сумма» ((№ варианта + (30+ N п/п))* 10) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

3. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 1999 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам туров разных типов в различные годы (нас интересует общая сумма, средняя прибыль и количество продаж туров разных типов по годам). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные за 2001 год.

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

Вариант №7

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 8% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (девять последних лет), «Сумма» ((№ варианта + (30+ N п/п))* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных туристических направлений (стран) по типам продаж (нас интересует средняя сумма, общая прибыль по типам поездок и их количество). Для ячеек области данных сводной таблицы со значениями средней суммы и общей прибыли установите тип «Финансовый» со значением $.

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

Вариант №8

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2001 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта + (N п/п +30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных туристических направлений (стран) по типам продаж (нас интересует общая сумма и общая прибыль по направлению по типам продаж). Отразите в сводной таблице только продажи по типам «отдых» и «обучение». Скройте данные по Израилю. Для ячеек области данных сводной таблицы со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $.

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

Вариант №9

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 6% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (восемь последних лет), «Сумма» ((№ варианта+(N п/п + 30)* 100) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным типам продаж (нас интересует общая сумма, средняя прибыль и количество продаж менеджеров по различным типам продаж). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Сидорову.

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

Вариант №10

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 1997 года (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта+(N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по типам продаж в различные годы (нас интересует общая сумма, средняя прибыль и количество продаж разных типов по годам). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные по типу «лечение».

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

Вариант №11

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 6% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (шесть последних лет), «Сумма» ((№ варианта+ (N п/п + 30)* 100) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным странам (нас интересует общая сумма, общая прибыль и количество продаж разных менеджеров по странам). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Иванову.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по сумме прибыли от продаж различных менеджеров по туристическим направлениям (странам). Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Год» в поле страниц диаграммы.

Вариант №12

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2002 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта +( N п/п+ 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам менеджеров в различные годы (нас интересует общая сумма и общая прибыль, полученная от продаж менеджеров по годам). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Отразите в сводной таблице четыре последних года. Скройте данные по менеджеру Петрову.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по количеству проданных туров различных типов по годам. После создания диаграммы переместите поле «Менеджер» в поле страниц диаграммы.

Вариант №13

1. Создайте таблицу из Приложения №1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не должна быть менее 10% от Суммы и, в то же время, не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (семь последних лет), «Сумма» ((№ варианта + (N п/п +30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

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

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

Вариант №14

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2000 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта +( N п/п+ 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам туров разных типов в различные годы (нас интересует общая сумма, средняя прибыль и количество продаж туров разных типов по годам). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные за 2000 год.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по общей сумме продаж различных менеджеров. Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Тип» в поле страниц диаграммы.

Вариант №15

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 8% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (девять последних лет), «Сумма» ((№ варианта + (N п/п +30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным странам (нас интересует общая сумма, общая прибыль и количество продаж разных менеджеров по странам). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Иванову.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные об общей сумме от продаж менеджеров турпредприятия. Для оси значений диаграммы необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Год» в поле страниц диаграммы.

Вариант №16

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2001 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта+(N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам в разные годы по типам туров (нас интересует общая сумма, общая прибыль и количество продаж в разные годы по типам туров). Для ячеек области данных сводной таблицы со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Скройте данные за последний год.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по средней прибыли от продаж туров различных типов. Для оси значений диаграммы необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Год» в поле страниц диаграммы.

Вариант №17

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 7% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (последние девять лет), «Сумма» ((№ варианта+(N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных туристических направлений (стран) по типам продаж (нас интересует общая сумма, средняя прибыль и количество проданных туров по направлению по типам продаж). Отразите в сводной таблице только продажи по типам «отдых» и «обучение». Для ячеек области данных сводной таблицы со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные по Израилю.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по сумме от продаж различных менеджеров по типам туров. Для оси значений необходимо использовать формат «Финансовый» ($).

Вариант №18

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2003 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

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

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по сумме прибыли от поездок различных типов. Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Страна» в поле страниц диаграммы.

Вариант №19

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 5% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (четыре последних года), «Сумма» ((№ варианта+(N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным типам продаж (нас интересует общая сумма, общая прибыль и количество продаж менеджеров по различным типам продаж). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Иванову.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по количеству туров различных типов, проданных менеджерами турпредприятия. После создания диаграммы переместите поле «Год» в поле страниц диаграммы.

Вариант №20

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 1998 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта+(N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по типам продаж в различные годы (нас интересует общая сумма, средняя прибыль и количество продаж разных типов по годам). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные по типу «лечение».

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

Вариант №21

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 9% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (семь последних лет), «Сумма» ((№ варианта + (N п/п +30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным странам (нас интересует общая сумма, общая прибыль и количество продаж разных менеджеров по странам). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Петрову.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по сумме прибыли от продаж по туристическим направлениям (странам). Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Тип» в поле страниц диаграммы.

Вариант №22

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2001 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта+(N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам менеджеров в различные года (нас интересует общая сумма и общая прибыль, полученная от продаж менеджеров по годам). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Отразите в сводной таблице четыре последних года. Скройте данные по менеджеру Петрову.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные о сумме прибыли от продаж туров по годам. Для оси значений диаграммы необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Менеджер» в поле страниц диаграммы.

Вариант №23

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не должна быть менее 15% от Суммы и, в то же время, не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (шесть последних лет), «Сумма» ((№ варианта + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

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

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по сумме прибыли от продаж туров по годам. Для оси значений диаграммы необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Страна» в поле страниц сводной диаграммы.

Вариант №24

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2001 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта+(N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам туров разных типов в различные годы (нас интересует общая сумма, средняя прибыль и количество продаж туров разных типов по годам). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные за 2001 год.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по общей сумме продаж различных менеджеров. Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Тип» в поле страниц диаграммы.

Вариант №25

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 7% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (восемь последних лет), «Сумма» ((№ варианта +( N п/п+ 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным странам (нас интересует общая сумма, общая прибыль и количество продаж разных менеджеров по странам). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Петрову.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные о максимальной сумме от продаж менеджеров турпредприятия. Для оси значений диаграммы необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Страна» в поле страниц диаграммы.

Вариант №26

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2000 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта + (N п/п+ 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам в разные годы по типам туров (нас интересует общая сумма, средняя прибыль и количество продаж в разные годы по типам туров). Для ячеек области данных сводной таблицы со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные о продажах с типом «лечение».

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по общей прибыли от продаж туров различных типов . Для оси значений диаграммы необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Менеджер» в поле страниц диаграммы.

Вариант №27

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 11% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта + (N п/п + 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных туристических направлений (стран) по типам продаж (нас интересует общая сумма, средняя прибыль и количество проданных туров по направлению по типам продаж). Отразите в сводной таблице только продажи по типам «Отдых» и «Лечение». Для ячеек области данных сводной таблицы со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные по Италии.

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по сумме от продаж различных менеджеров по типам туров. Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Страна» в поле страниц диаграммы.

Вариант №28

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 2002 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта +( N п/п+ 30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

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

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по сумме прибыли от поездок различных типов. Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Страна» в поле страниц диаграммы.

Вариант №29

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Прибыль», учитывая, что прибыль не может быть менее 10% от Суммы и не может превышать значение нормы прибыли, умноженной на Сумму.

3. Внесите данные в столбцы «Год» (пять последних лет), «Сумма» ((№ варианта + (N п/п +30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по продажам разных менеджеров по различным типам продаж (нас интересует общая сумма, общая прибыль и количество продаж менеджеров по различным типам продаж). Для ячеек области данных со значениями общей суммы и общей прибыли установите тип «Финансовый» со значением $. Скройте данные по менеджеру Петрову.

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

Вариант №30

1. Создайте таблицу из Приложения 1 в Microsoft Excel.

2. Задайте проверку вносимых данных в столбце «Год», учитывая, что год вносится как полная цифра года (например, 2003) и не может быть больше текущего и меньше 1999 (года введения компьютерного учета выработки менеджеров на Вашем туристическом предприятии).

3. Внесите данные в столбцы «Год», «Сумма» ((№ варианта + (N п/п +30)* 1000) и «Прибыль» (норма прибыли * Сумма). После этого скройте столбец, в котором содержится норма прибыли.

4. Скопируйте лист с полученной таблицей, поместив копию в той же книге.

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

6. Перейдите на лист-копию Вашей таблицы. Создайте сводную таблицу, в которой подведите итоги по типам продаж в различные года (нас интересует общая сумма, средняя прибыль и количество продаж разных типов по годам). Для ячеек области данных со значениями общей суммы и средней прибыли установите тип «Финансовый» со значением $. Скройте данные продаж по типу «лечение».

7. Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены данные по средней прибыли от продаж различных менеджеров по туристическим направлениям (странам). Для оси значений необходимо использовать формат «Финансовый» ($). После создания диаграммы переместите поле «Год» в поле страниц диаграммы.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Установка проверки вводимых данных

1. Выделите ячейки, на которые нужно наложить ограничения.

2. Выберите команду Проверка в меню Данные, а затем откройте вкладку Параметры.

3. Выберите тип данных в списке Тип данных.

Чтобы установить только числа, выберите Целое число или Действительное.

Чтобы установить только даты или время, выберите Дата или Время.

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

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

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

1. Чтобы выводить подсказку, а также давать пояснения по поводу некорректных данных или предотвращать их ввод, укажите типы сообщений на вкладках Сообщение для ввода и Сообщение об ошибке.

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

Примечания

• Определение типа корректных данных не приводит к форматированию ячейки. Чтобы отформатировать числа, даты или время, содержащиеся в ячейке, выберите Ячейки в меню Формат и затем — вкладку Число. В списке Числовые форматы выберите нужный формат, а также требуемые параметры.

• Используемая в качестве предела формула может анализировать данные только на том листе, на котором были установлены ограничения. Чтобы употреблять в формуле данные из других листов или книг, введите ссылку на внешние данные в ячейку активного листа или определите на нем имя внешних данных. Таким образом формула будет содержать ссылку на ячейку или имя, находящиеся на том же листе. Например, если данные, которые требуется использовать в формуле, находятся в ячейке A6 на первом листе книги Бюджет.xls, нужно определить имя «Данные» на активном листе как =[Бюджет.xls]Лист1!$A$6 и затем ввести ссылку =«Данные» во время определения ограничений на данные.

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

Определение правильности вводимых данных с помощью формулы

1. Выделите ячейки, на которые нужно наложить ограничения.

2. Выберите команду Проверка в меню Данные, а затем откройте вкладку Параметры.

3. Выберите тип Другой в окне Тип данных.

4. В поле Формула введите формулу для расчета логического значения (ИСТИНА или ЛОЖЬ). Данные, вводимые в ячейку, считаются некорректными, если результатом вычислений является значение ЛОЖЬ. Перед формулой необходимо поставить знак равенства (=).

Например, на листе производится расчет прибыли путем вычитания суммы вводимых издержек из расчетной суммы дохода. Может возникнуть необходимость поддерживать прибыль на уровне, превышающем определенный процент издержек. В этом случае для ячеек D6:D13, служащих для ввода издержек, следует определить допустимые данные с помощью формулы следующего вида: =Прибыль СУММ(D6:D13) * 15%, где «Прибыль» — это имя ячейки.

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

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

1. Чтобы выводить подсказку, а также давать пояснения по поводу некорректных данных или предотвращать их ввод, укажите типы сообщений на вкладках Сообщение для ввода и Сообщение об ошибке.

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

Примечания

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

• Используемая в качестве ограничения формула может анализировать данные только на том листе, на котором наложены ограничения. Чтобы употреблять в формуле данные из других листов или книг, введите ссылку на внешние данные в ячейку активного листа или определите на нем имя внешних данных. Таким образом формула будет содержать ссылку на ячейку или имя, находящиеся на том же листе. Например, если данные, которые требуется использовать в формуле, находятся в ячейке A6 на первом листе книги Бюджет.xls, можно определить имя «Данные» на активном листе как =[Бюджет.xls]Лист1!$A$6 и затем ввести ссылку на «Данные» в формулу.

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

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

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

1. Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог. Например, чтобы просуммировать единицы продукции, проданные каждым продавцом, в списке продавцов, объемов продаж и количества проданного отсортируйте список по столбцу продавцов.

2. Укажите ячейку в списке.

3. Выберите команду Итоги в меню Данные.

4. Выберите столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

5. Выберите функцию, необходимую для подведения итогов, из списка Операция.

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

Итоговые функции для промежуточных итогов

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

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

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

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

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

2. Выберите команду Итоги в меню Данные.

Нажмите кнопку Убрать все.

Подведение «вложенных» или многоуровневых итогов

Чтобы «вложить» или вставить итог для групп, находящихся внутри уже существующих групп, необходимо отсортировать список.

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

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

3. После создания автоматических итогов в первом столбце, повторите процедуру для следующего столбца.

4. Укажите ячейку в этом списке.

5. Выберите команду Итоги в меню Данные.

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

7. Снимите флажок Заменить текущие итоги, а затем нажмите кнопку OK.

8. Повторите шаги с 4 по 7 для каждого столбца, по которому необходимо подвести итоги.

Сводная таблица. Назначение, способ создания и изменения

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

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

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

Элементы отчета сводной таблицы

Поля строк – поля из базового источника данных, которым в отчете сводной таблицы задана ориентация по строкам. В приведенном примере полями строк являются поля «Товар» и «Продавец». Если в отчете сводной таблицы содержится несколько полей строк, одно из них, ближайшее к области данных, является внутренним (в данном случае поле «Продавец»). Все остальные поля строк называются внешними. Внутренние и внешние поля строк имеют различные атрибуты. Элементы в самом крайнем внешнем поле отображаются только один раз, но в остальных полях элементы повторяются необходимое число раз.

Поле столбца – поле, которому в отчете сводной таблицы задана ориентация столбца. В приведенном примере поле «Кварталы» является полем столбца с двумя элементами, «Кв.2» и «Кв.3». Отчет сводной таблицы может содержать несколько полей столбцов, как и полей строк. Большинство структурированных отчетов сводных таблиц не содержит полей столбцов.

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

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

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

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

Поле данных обычно обобщает числовые данные, например статистику или размеры продаж, но базовые данные также могут быть текстовыми. По умолчанию в Microsoft Excel для обобщения текстовых данных в отчетах сводных таблиц используется итоговая функция СЧЕТ, а для обобщения числовых данных – функция СУММ. В отчетах сводных таблиц, основанных на исходных данных OLAP, изменить итоговую функцию для поля нельзя: итоговые функции выбираются системным администратором OLAP и вычисляются на сервере OLAP.

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

В приведенном примере значение в ячейке C6 является сводкой продаж молока за второй квартал для Батурина — то есть, суммой продаж по всем записям или строкам исходных данных, содержащих элементы «Молоко», «Батурин» и «Кв.2». В примере структурированного отчета эти сведения отображаются в ячейке D7.

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

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

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

Структурированный отчет сводной таблицы

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

Отчеты сводных диаграмм

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

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

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

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

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

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

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

Создание отчета сводной диаграммы

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

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

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

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

Использование полей страниц   Использование полей страниц является удобным способом обобщения и выделения подмножества данных без необходимости изменения сведений о рядах и категориях. Например, чтобы во время презентации показать продажи за все годы, следует в поле страницы «Год» выбрать (Все); выбирая затем определенные годы можно сфокусироваться на информации по отдельным годам. Каждое представление или страница показывают одну и ту же категорию и ряд данных (например, объем продаж по продавцам в каждом регионе) для каждого года, поэтому представления легко сравнимы. Кроме того, обеспечивая доступ только к подмножеству большого набора данных, поля страниц экономят память при использовании диаграммой внешних источников данных.

Литература

1. Семичастный И.Л. Информационно-коммуникационные технологии: Электронный учебник. Лекция №10. – ДонГУУ, 2016.

2. Степанов А. Н. Информатика: Учебник для ВУЗов. 6-е изд.Питер:.2010. 720с.

3. Додж М., Стинсон К. Эффективная работа: Microsoft Office Excel 2003. –СПб.: Питер, 2005. –1088 с.

4. http://msexcel.ru/component/option,com_frontpage/Itemid,1/limit,20/limitstart, 20/ Профессиональные приѐмы работы в Microsoft Excel

5. http://msexcel.ru

6. http://planetaexcel.ru

7. http://cie.ifmo.ru/doc/effective_microsoft_office.pdf Эффективная работа с Office 2007

8. Гельман В.Я. Решение математических задач средствами Excel : Практикум/.—СПб: Питер, 2003.—240 с.

9. Информатика. Задачник-практикум в 2 т. / Л.А.Залогова и др. Под ред. И.Г.Семакина, Е.К.Хеннера: Том 1-2. – 3-е изд., испр. – М.: БИНОМ, Лаборатория знаний, 2006. – 309 с.

10. Семакин И.Г. Информатика и ИКТ. Базовый уровень: учебник для 10-11 классов/ И.Г.Семакин, Е.К.Хеннер.– 5-е изд. – М.: БИНОМ, Лаборатория знаний, 2009. – 246 с.

11. Угринович Н.Д. Информатика и ИКТ. Профильный уровень: учебник для 11 класса / Н.Д.Угринович. – 2 -е изд., испр. и доп. – М.: БИНОМ. Лаборатория знаний, 2009. – 308 с.

12. Cимонович С.В. Информатика. Базовый курс: [учеб. пособие для высш. техн. учеб. заведений] Под ред. С.В.Симоновича.СПб, Питер, 2009. – 639 с.

Приложение №1

N п/п	Менеджер	Страна	Тип	Год	Сумма	Прибыль				Норма прибыли
1	Иванов	Турция	Отдых							20%
2	Сидоров	Израиль	Лечение
3	Петров	Италия	Обучение
4	Иванов	Италия	Обучение
5	Иванов	Израиль	Отдых
6	Сидоров	Турция	Отдых
7	Сидоров	Турция	Обучение
8	Петров	Италия	Лечение
9	Петров	Израиль	Обучение
10	Сидоров	Италия	Лечение
11	Петров	Италия	Обучение
12	Сидоров	Израиль	Отдых
13	Петров	Турция	Обучение
14	Иванов	Италия	Лечение
15	Иванов	Турция	Обучение
16	Петров	Италия	Отдых
17	Петров	Израиль	Отдых
18	Сидоров	Турция	Отдых
19	Сидоров	Израиль	Обучение
20	Сидоров	Италия	Отдых
21	Иванов	Израиль	Лечение
22	Петров	Израиль	Обучение
23	Петров	Италия	Отдых
24	Сидоров	Турция	Отдых
25	Петров	Израиль	Отдых
26	Иванов	Италия	Лечение
27	Иванов	Турция	Отдых

24

Содержательный модуль 2

Создание, редактирование и форматирование диаграмм в среде МS Ехсеl

Практическая работа №12
изучение возможностей ms excel 2010- 2016. Анализ данных

4 часа + 1(самостоятельная работа)

Цель – изучить новые возможности работы объектами в электронных таблицах MS Excel 2010 - 2016

План проведения практической работы

1. Спарклайны

2. Условное форматирование

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

4. Итоговые функции для промежуточных итогов

5. Удаление промежуточных итогов

6. Элементы отчета сводной таблицы

7. Структурированный отчет сводной таблицы

8. Построение сводной диаграммы

Индивидуальное задание №12

1. Загрузить файл с таблицей, созданной Вами в Практической работе № 6.

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

3. В Excel 2016 построить спарклайны для каждого города по всем 30 дням месяца. Расположить их в пустом столбце справа от списка.

4. Отсортировать список по столбцу «Всего за месяц» по возрастанию.

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

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

7. Открыть файл «Практическая раб 12. Кондитерская.xls». Выполнить задания для вложенных промежуточных итогов

Вариант	Задание 1	Задание 2
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Статус	Подвести Итоги по полям Заказчик и Статус, подсчитав Сумму по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Товар	Подвести Итоги по полям Заказчик и Товар, подсчитав Сумму по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Статус	Подвести Итоги по полям Заказчик и Статус, подсчитав Среднее по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Товар	Подвести Итоги по полям Заказчик и Товар, подсчитав Сумму по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Статус	Подвести Итоги по полям Заказчик и Статус, подсчитав Среднее по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Менеджер продаж	Подвести Итоги по полям Заказчик и Менеджер продаж, подсчитав Сумму по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Регион	Подвести Итоги по полям Заказчик и Регион, подсчитав Сумму по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Менеджер продаж	Подвести Итоги по полям Заказчик и Менеджер продаж, подсчитав Среднее по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Регион	Подвести Итоги по полям Заказчик и Регион, подсчитав Сумму по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Менеджер продаж	Подвести Итоги по полям Заказчик и Менеджер продаж, подсчитав Максимум по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Регион	Подвести Итоги по полям Заказчик и Регион, подсчитав Среднее по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Менеджер продаж	Подвести Итоги по полям Заказчик и Менеджер продаж, подсчитав Минимум по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Регион	Подвести Итоги по полям Заказчик и Регион, подсчитав Максимум по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Менеджер продаж	Подвести Итоги по полям Заказчик и Менеджер продаж, подсчитав Минимум по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Регион	Подвести Итоги по полям Заказчик и Регион, подсчитав Максимум по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Регион	Подвести Итоги по полям Заказчик и Регион, подсчитав Минимум по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Категория	Подвести Итоги по полям Заказчик и Категория, подсчитав Минимум по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Категория	Подвести Итоги по полям Заказчик и Категория, подсчитав Максимум по полю Продажа
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Категория	Подвести Итоги по полям Заказчик и Категория, подсчитав Максимум по полю Закупка
	Отсортировать данные по возрастанию последовательно по полям Заказчик и Категория	Подвести Итоги по полям Заказчик и Категория, подсчитав Минимум по полю Продажа

1. Используя Сводные таблицы и Сводные диаграммы, построить их по вариантам заданий.

Вариант	Задание 1	Задание 2
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам Заказчиками разных Товаров (нас интересует Сумма Продаж по Категориям). Фильтрацию данных осуществить по Датам.	Создайте сводную диаграмму на отдельном листе на основе созданной сводной таблицы.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам Заказчиками разных Товаров (нас интересует Сумма Продаж по Менеджерам продаж). Фильтрацию данных осуществить по Датам.	Создайте сводную диаграмму на отдельном листе на основе созданной сводной таблицы.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам Заказчиками разных Товаров (нас интересует Сумма Закупок по Категориям). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены Продажам различных Товаров по Категориям с фильтрацией по Датам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам Заказчиками разных Товаров (нас интересует Минимум Продаж по Менеджерам продаж). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены Закупки различных Товаров Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам Заказчиками разных Товаров (нас интересует Среднее Закупок по Категориям). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражена Сумма Продаж различных Товаров по Регионам с фильтрацией по Датам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам Заказчиками разных Товаров (нас интересует Среднее Закупок по Менеджерам продаж). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены Среднее Закупок различных Товаров Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий Менеджерами продаж (нас интересует Сумма Продаж по Статусу). Фильтрацию данных осуществить по Регионам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Максимум Продаж различных Товаров Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Закупкам Заказчиками разных Товаров (нас интересует Среднее Закупок по Статусу). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должны быть отражены Минимум Закупок различных Товаров Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий по Регионам (нас интересует Среднее Продаж по Статусу). Фильтрацию данных осуществить по Регионам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Максимум Продаж различных Категорий Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий Менеджерами продаж (нас интересует Максимум Продаж по Статусу). Фильтрацию данных осуществить по Регионам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Максимум Продаж различных Товаров Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий по Регионам (нас интересует Сумма Закупок по Статусу). Фильтрацию данных осуществить по Регионам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Минимум Продаж различных Категорий Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий Менеджерами продаж (нас интересует Минимум Закупок по Статусу). Фильтрацию данных осуществить по Регионам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Среднее Продаж различных Товаров Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий по Регионам (нас интересует Сумма Продаж по Статусу). Фильтрацию данных осуществить по Регионам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должна быть отражена Сумма закупок различных Категорий Менеджерами продаж с фильтрацией по Датам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий Менеджерами продаж (нас интересует Среднее Продаж по Статусу). Фильтрацию данных осуществить по Регионам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Минимум Закупок различных Товаров Менеджерами продаж с фильтрацией по Статусу.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий по Регионам (нас интересует Среднее закупок по Регионам). Фильтрацию данных осуществить по Статусу.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должна быть отражена Среднее Закупок различных Категорий Менеджерами продаж с фильтрацией по Регионам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий Менеджерами продаж (нас интересует Среднее Закупок по Регионам). Фильтрацию данных осуществить по Статусу.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Минимум Закупок различных Товаров Менеджерами продаж с фильтрацией по Датам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий по Товарам в Регионах (нас интересует Сумма Продаж по Статусу). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Максимум Продаж различных Товаров Менеджерами продаж с фильтрацией по Регионам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий в Регионах по Товарам (нас интересует Минимум Закупок по Статусу). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должна быть отражена Сумма Продаж различных Товаров Менеджерами продаж с фильтрацией по Регионам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий по Товарам в Регионах (нас интересует Среднее закупок по Статусу). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Минимум Закупок различных Товаров Менеджерами продаж с фильтрацией по Регионам.
	Создайте сводную таблицу в Excel 2016, в которой подведите итоги по Продажам различных Категорий по Товарам в Регионах (нас интересует Максимум Продаж по Статусу). Фильтрацию данных осуществить по Датам.	Создайте новую сводную диаграмму на отдельном листе на основе данных из исходной таблицы. На диаграмме должен быть отражен Максимум Продаж различных Товаров Менеджерами продаж с фильтрацией по Датам.

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

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

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

12.1 Спарклайны в Excel — создание микрографиков

C появлением Excel 2010-2016 появилась новая возможность создавать спарклайн-графики. Спарклайн – это небольшая диаграмма, помещенная в одну ячейку. Она позволяет быстро отслеживать динамически изменяющиеся данные. Поскольку спарклайны умещаются в одну ячейку, их принято использовать группами.

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

На рисунке отображены три различных вида спарклайнов, столбец H:

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

График – схож с диаграммой в виде графика. Для данного типа спарклайнов возможен вариант отображения маркера для каждой точки данных. Первая группа показывает спарклайн с маркерами.

Гистограмма – как и обычная гистограмма. Вторая группа показывает те же данные, в самом правом столбце.

Выигрыш/проигрыш – делит все данные на два типа: отрицательные и положительные и отображает их в виде верхнего и нижнего блока. Данный вид спарклайнов реализован в третьей группе.

Чтобы создать спарклайн-графику, выберите данные, которые вы хотите визуализировать. Затем перейдите во вкладку Вставка в группу Спарклайны и выберите один из трех типов спарклайнов: гистограмма, график или выигрыш/проигрыш.

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

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

Excel предоставляет достаточный набор инструментов для изменения внешнего вида спарклайна. Для этого вам необходимо перейти по вкладке Работа со спаркайнами - Конструктор (данная вкладка появляется, когда вы выбираете ячейку со спарклайном).

12.2 Условное форматирование в Excel

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

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

Условное форматирование – это простой способ определить ячейки с ошибочными записями или значениями определённого типа. Вы можете использовать формат (например, красная заливка), чтобы легко идентифицировать определенные ячейки.

Виды условного форматирования

Когда вы нажимаете на кнопку Условное форматирование, которая находится в группе Стили вкладки Главная, вы увидите выпадающее меню со следующими опциями:

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

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

Наборы значков отображает значок в ячейке. Какой именно значок отображается, зависит от значения ячейки относительно других ячеек. Excel 2013 предоставляет 20 наборов значков на выбор (при этом вы можете смешивать и сочетать значки из разных наборов). Количество значков в наборах колеблется от трех до пяти.

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

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

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

Графическое условное форматирование

Вероятно, самое крутое (и конечно, простое) условное форматирование, которое можно применить к диапазону ячеек – это форматирование с применением графических элементов – Гистограммы, Цветовые шкалы и Наборы значков.

На рисунке изображено применение двух различных правил для форматирования для диапазона от 6 до 1 и наоборот. В первом случае применялись Цветовые шкалы, где мы видим, как изменяется формат при изменении значения от 6 до 1, во втором – 3 цветные стрелки.

Определение конкретных значений в диапазоне ячеек

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

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

Примеры условного форматирования

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

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование - Правила выделения ячеек - Текст содержит.

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

Щелкаем ОК, чтобы наше правило вступило в силу.

Несколько условных форматирований для одного диапазона

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

Выделяем диапазон ячеек, к которому мы хотим применить три различных правила условного форматирования. Начнем с создания правила для ячеек, содержащих значение равное 95. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование - Правила выделения ячеек - Равно. Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желая заливка с темно-желтым текстом.

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование - Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат.

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

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

Формулы в условном форматировании

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

Выделяем таблицу с данными, к которой мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование - Создать правило.  В появившемся диалоговом окне Создание правила форматирования в поле Выберите тип правила выбираем  Использовать формулу для определения форматируемых ячеек.

В поле Измените описание правила задаем условия и формат для нашего правила. В нашем случае, условием будет формула =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7). В качестве формата я выбрал темно красную заливку.

Общие сведения о сортировке и фильтрации данных по цвету и набору значков

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

• Чьи продажи услуг в этом месяце составили более 50 000 рублей?

• Для каких продуктов прирост выручки по сравнению с прошлым годом превысил 10 %?

• Кто из студентов является лучшим и худшим на младшем курсе?

• Каковы исключения в отчетах по прибыли за последние пять лет?

• Каково общее распределение сотрудников по возрасту?

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

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

На этом рисунке показана фильтрации и сортировки на основе цвета или значка на категории, % М/М Δ и разметки столбцов.

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

Каждый значок соответствует диапазону значений. Например, в таблице наборов значков, представленной ниже, в наборе значков «Три стрелки»:

• зеленая стрелка, указывающая вверх, соответствует высоким значениям,

• желтая стрелка, направленная в сторону, соответствует средним значениям,

• а красная стрелка вниз — низким значениям.

Таблица наборов значков

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

Эффективное использование цвета при анализе данных

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

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

Использование стандартных цветов и ограничение числа цветов

При фильтрации и сортировке по цвету можно выбрать предпочитаемые цвета, и результат покажется приемлемым для автора. Однако сначала нужно задать себе главный вопрос: «Подойдут ли эти цвета для остальных пользователей?» Современный компьютер способен отображать 16 777 216 цветов в 24-битном режиме. Однако большинство пользователей способны различить лишь незначительную их часть. Кроме того, качество цветопередачи может отличаться от компьютера к компьютеру. Освещение в комнате, качество бумаги, разрешение экрана и принтера и параметры браузера — все эти параметры могут различаться. До 10 % людей испытывают некоторые трудности с различением и восприятием определенных цветов. Это важные переменные факторы, которые вряд ли возможно контролировать.

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

Контраст цвета и фон

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

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

1. Похожие цвета находятся на цветовом круге рядом друг с другом (например, фиолетовый и оранжевый цвета похожи на красный).

2. Контрастный цвет смещен на три цвета от текущего цвета (например, синий и зеленый цвета являются контрастными по отношению к красному).

3. Дополняющие цвета находятся на цветовом круге друг напротив друга (например, сине-зеленый цвет является дополнительным для красного).

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

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

Выбор наиболее подходящих цветов

Краткие рекомендации. Используйте красный, желтый, зеленый или синий цвет на белом или сером фоне.

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

Лучшие 10 цветов

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

Эти цвета можно легко применить к ячейкам и шрифтам с помощью кнопок Цвет заливки и Цвет текста в группе Шрифт на вкладке Главная.

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

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

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

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

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

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

Несколько примеров

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

Образец данных

В примерах используется приведенный ниже образец данных.

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

Сохранение образца данных в XLSX-файле

1. Запустите программу Блокнот.

2. Выделите текст образца, а затем скопируйте его и вставьте в Блокнот.

3. Сохраните файл, указав его имя и расширение, например Products.csv.

4. Выйдите из Блокнота.

5. Запустите Excel.

6. Откройте файл, сохраненный в Блокноте.

7. Сохраните файл в формате XLSX.

Образец данных

Category,Product Name,Cost,Price,Markup,Reorder At,Amount,Quantity Per Unit,Reorder?

Dried Fruit/Nuts,Almonds,$7.50,$10.00,"=(D2-C2)/C2",5,7,5 kg pkg.,"=IF(G2

Canned Fruit,Apricot,$1.00,$1.20,"=(D3-C3)/C3",10,82,14.5 OZ,"=IF(G3

Beverages,Beer,$10.50,$14.00,"=(D4-C4)/C4",15,11,24 - 12 oz bottles,"=IF(G4

Jams/Preserves,Boysenberry,$18.75,$25.00,"=(D5-C5)/C5",25,28,12 - 8 oz jars,"=IF(G5

Condiments,Cajun,$16.50,$22.00,"=(D6-C6)/C6",10,10,48 - 6 oz jars,"=IF(G6

Baked Goods,Cake Mix,$10.50,$15.99,"=(D7-C7)/C7",10,23,4 boxes,"=IF(G7Canned Fruit,Cherry Pie Filling,$1.00,$2.00,"=(D8-C8)/C8",10,37,15.25 OZ,"=IF(G8Soups,Chicken Soup,$1.00,$1.95,"=(D9-C9)/C9",100,123,,"=IF(G9Baked Goods,Chocolate Mix,$6.90,$9.20,"=(D10-C10)/C10",5,18,10 boxes x 12 pieces,"=IF(G10Soups,Clam Chowder,$7.24,$9.65,"=(D11-C11)/C11",10,15,12 - 12 oz cans,"=IF(G11Beverages,Coffee,$34.50,$46.00,"=(D12-C12)/C12",25,56,16 - 500 g tins,"=IF(G12Canned Meat,Crab Meat,$13.80,$18.40,"=(D13-C13)/C13",30,23,24 - 4 oz tins,"=IF(G13Sauces,Curry Sauce,$30.00,$40.00,"=(D14-C14)/C14",10,15,12 - 12 oz jars,"=IF(G14Pasta,Gnocchi,$28.50,$38.00,"=(D15-C15)/C15",30,38,24 - 250 g pkgs.,"=IF(G15Cereal,Granola,$2.00,$4.00,"=(D16-C16)/C16",20,49,,"=IF(G16Beverages,Green Tea,$2.00,$2.99,"=(D17-C17)/C17",100,145,20 bags per box,"=IF(G17Cereal,Hot Cereal,$3.00,$5.00,"=(D18-C18)/C18",50,68,,"=IF(G18Jams/Preserves,Marmalade,$60.75,$81.00,"=(D19-C19)/C19",10,13,30 gift boxes,"=IF(G19Dairy,Mozzarella,$26.10,$34.80,"=(D20-C20)/C20",10,82,24 - 200 g pkgs.,"=IF(G20Condiments,Mustard,$9.75,$13.00,"=(D21-C21)/C21",15,23,12 boxes,"=IF(G21Canned Fruit,Pears,$1.00,$1.30,"=(D22-C22)/C22",10,25,15.25 OZ,"=IF(G22Pasta, Ravioli,$14.63,$19.50,"=(D23-C23)/C23",20,27,24 - 250 g pkgs.,"=IF(G23Canned Meat,Smoked Salmon,$2.00,$4.00,"=(D24-C24)/C24",30,35,5 oz,"=IF(G24Sauces,Tomato Sauce,$12.75,$17.00,"=(D25-C25)/C25",20,19,24 - 8 oz jars,"=IF(G25Dried Fruit/Nuts,Walnuts,$17.44,$23.25,"=(D26-C26)/C26",10,34,40 - 100 g pkgs.,"=IF(G26

Какие виды упаковки используются?

Проблема

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

Результаты

Решение

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

Совет   Кнопка Формат по образцу в группе Буфер обмена на вкладке Главная позволяет быстро применить выбранный цвет к другой ячейке.

1. Щелкните ячейку в столбце Количество на единицу, на вкладке Главная в группе Правка нажмите Сортировка и фильтр и выберите элемент Настраиваемая сортировка.

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

3. В разделе Порядок в первой строке выберите красный цвет, во второй — синий, а в третьей — желтый.

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

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

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

Условные обозначения
Красный	Упаковки и коробки
Синий	Жестяные банки
Зеленый	Стеклянные банки и бутылки
Белый	(Не определено)

У каких продуктов наценка выше 67 % или ниже 34 %?

Проблема

Необходимо быстро вывести наибольшее и наименьшее значения наценки в верхней части отчета.

Решение

1. Выделите ячейки E2:E26, а затем на вкладке Главная в группе Стиль щелкните стрелку рядом с кнопкой Условное форматирование, выберите пункт Набор значков и выберите набор значков Три стрелки (цветные).

2. Щелкните правой кнопкой мыши ячейку в столбце Разметка, выберите пункт Сортировка, а затем — Настраиваемая сортировка.

3. В диалоговом окне Сортировка в разделе Столбец выберите элемент Разметка, в группе Сортировка выберите пункт Значок ячейки и выберите команду Копировать уровень.

4. В разделе Порядок в первой строке выберите зеленую стрелку, указывающую вверх, а во второй строке — красную стрелку, указывающую вверх.

Результаты

Какой продукт нужно немедленно дозаказать?

Проблема

Необходимо быстро создать отчет по продуктам, которые нужно немедленно дозаказать, а затем отправить отчет по почте своим сотрудникам.

Результаты

Решение

1. Выделите диапазон ячеек I2:I26 и на вкладке Главная в группе Стиль щелкните стрелку рядом с кнопкой Условное форматирование, выберите пункт Правила выделения ячеек, а затем — пункт равно.

2. В первом поле введите Да и выберите во втором поле пункт Светло-красная заливка и темно-красный текст.

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

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

У каких продуктов наибольшие и наименьшие значения цены и стоимости?

Проблема

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

Результаты

Решение

1. Выполните для ячеек C2:C26 и D2:D26 действия, описанные ниже.

   • На вкладке Главная в группе Стиль щелкните стрелку рядом с кнопкой Условное форматирование, выберите пункт Правила отбора первых и последних значений, а затем — пункт Первые 10 элементов.

   • В первом поле введите 1, во втором выберите пункт Желтая заливка и темно-желтый текст.

   • На вкладке Главная в группе Стиль щелкните стрелку рядом с кнопкой Условное форматирование, выберите пункт Правила отбора первых и последних значений, а затем — пункт Последние 10 элементов.

   • В первом поле введите 1, во втором выберите пункт Зеленая заливка и темно-зеленый текст.

2. Для столбцов "Затраты" и "Цена" выполните указанные ниже действия.

   • Щелкните правой кнопкой мыши наименьшее значение, выберите пункт Сортировка, а затем — пункт Сортировка по цвету выбранной ячейки.

   • Щелкните правой кнопкой мыши наибольшее значение, выберите пункт Сортировка, а затем — пункт Сортировка по цвету выбранной ячейки.

Создание «умной» таблицы в MS Excel 2010

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

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):

 

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

В результате после такого преобразования диапазона в "умную" Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

1. Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).

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

 

1. В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).

2. При добавлении новых строк в них автоматически копируются все формулы.

1. При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец - не надо тянуть формулу черным крестом автозаполнения.

1. При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

1. Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:
   

1. К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):

• =Таблица1[#Все] - ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов

• =Таблица1[#Данные] - ссылка только на данные (без строки заголовка)

• =Таблица1[#Заголовки] - ссылка только на первую строку таблицы с заголовками столбцов

• =Таблица1[#Итоги] - ссылка на строку итогов (если она включена)

• =Таблица1[#Эта строка] - ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] - будет ссылаться на значение НДС из текущей строки таблицы.

Использование срезов в сводных таблицах и диаграммах.

Начиная с Excel 2010, сводные таблицы обзавелись новой возможностью фильтрации данных, этот инструмент называется Срезы.

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

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

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

Создание среза в существующей сводной таблице

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

Появится раздел Работа со сводными таблицами со вкладками Параметры и Конструктор.

1. На вкладке Параметры в группе Сортировка и фильтр нажмите кнопку Вставить срез.

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

2. Нажмите кнопку ОК .

Для каждого из выбранных полей будет отображен срез.

1. В каждом срезе выберите элементы, по которым нужно выполнить фильтрацию.

Чтобы выбрать несколько элементов, щелкните их по очереди, удерживая нажатой клавишу CTRL.

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

Кнопка Очистить фильтр снимает фильтр, выделяя все элементы в срезе.

Литература

1. Семичастный И.Л. Информационно-коммуникационные технологии: Электронный учебник. Лекция №12. – ДонАУиГС, 2017.

2. Степанов А. Н. Информатика: Учебник для ВУЗов. 6-е изд.Питер:.2010. 720с.

3. Додж М., Стинсон К. Эффективная работа: Microsoft Office Excel 2003. –СПб.: Питер, 2005. –1088 с.

4. http://msexcel.ru/component/option,com_frontpage/Itemid,1/limit,20/limitstart, 20/ Профессиональные приѐмы работы в Microsoft Excel

5. http://msexcel.ru

6. http://planetaexcel.ru

7. http://cie.ifmo.ru/doc/effective_microsoft_office.pdf Эффективная работа с Office 2007

8. Гельман В.Я. Решение математических задач средствами Excel : Практикум/.—СПб: Питер, 2003.—240 с.

9. Информатика. Задачник-практикум в 2 т. / Л.А.Залогова и др. Под ред. И.Г.Семакина, Е.К.Хеннера: Том 1-2. – 3-е изд., испр. – М.: БИНОМ, Лаборатория знаний, 2006. – 309 с.

10. Семакин И.Г. Информатика и ИКТ. Базовый уровень: учебник для 10-11 классов/ И.Г.Семакин, Е.К.Хеннер.– 5-е изд. – М.: БИНОМ, Лаборатория знаний, 2009. – 246 с.

11. Угринович Н.Д. Информатика и ИКТ. Профильный уровень: учебник для 11 класса / Н.Д.Угринович. – 2 -е изд., испр. и доп. – М.: БИНОМ. Лаборатория знаний, 2009. – 308 с.

12. Cимонович С.В. Информатика. Базовый курс: [учеб. пособие для высш. техн. учеб. заведений] Под ред. С.В.Симоновича.СПб, Питер, 2009. – 639 с.

13. http://exceltip.ruспарклайны-в-excel-создание-микрографико/

14. http://exceltip.ruусловное-форматирование-в-excel/

15. http://www.planetaexcel.ru/techniques/2/136/

20

Содержательный модуль 2

Проектирование и использование баз данных на основе МS ACCESS

Практическая работа №14
Технология работы с запросами и отчетами в MS ACCESS 2010- 2016

4 часа + 1(самостоятельная работа)

Цель – приобретение навыков создания запросов и отчетов различных типов, а также макросов в СУБД MS Access 2010 - 2016

План проведения практической работы

1. Анализ предметной области

2. Создание форм в MS Access

3. Создание запросов в MS Access.

4. Создание отчетов в MS Access.

5. Создание кнопочных форм

6. Построение схемы данных

7. Подготовка отчета по варианту задания

Индивидуальное задание №14

1. Загрузить файл с базой данных, созданной в Практической работе № 13.

2. Создать все объекты базы данных в соответствии с заданием по варианту.

3. Сделать анализ предметной области.

4. Подготовить отчет по заданию.

Вариант 1

1. Создать Главную кнопочную форму базы данных «Библиотека».

2. Создать простые запросы Кто брал книгу, Возвращенные книги, Невозвращенные книги.

3. С помощью запросов на выборку

• выбрать книгу, для которой наибольшее количество экземпляров находится "на полках" (не выданы читателям);

• выбрать читателей, которые имеют задолженность более 4 месяцев;

• определить книгу, которая была наиболее популярной весной 2011 года;

• определить читателей, у которых на руках находятся книги на общую сумму более 1000 руб.

1. Создать запрос с вычисляемым полем на основе таблицы «Выдача книг» для подсчета количества дней, которые находится на руках выданная книга.

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

3. Создать отчет с группировкой за последний год, в котором отразить обращение книг в течение этого периода.

4. Пользуясь Конструктором, создать схему данных базы Библиотека при помощи двух отношений один-ко-многим.

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

6. Имеется ли базе данных Библиотека отношение многие-ко-многим? Объясните логику этого отношения.

7. Создать предложенную схему данных.

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

2. Сохранить итоговую схему данных с учетом добавленной таблицы.

3. Создать отчет с группировкой «Группировка по месяцам» для таблицы «Выдача книг», создав группы записей по Дате выдачи по месяцам.

4. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

• анализ предметной области,

• структуру спроектированных таблиц,

• схему данных со связями между таблицами,

• примеры форм, обеспечивающих интерфейс пользователя,

• запросы (в режиме Конструктора),

• отчеты (в режиме отчета и в режиме Конструктора),

• главную кнопочную форму.

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

Вариант №2

1. Выбрать клиентов, занимающихся в Бассейне 25 м с помощью запроса «Какие клиенты работают в зале»:

SELECT Клиенты.Код_клиента, Клиенты.Фамилия, Клиенты.Имя, Залы.Код_зала
FROM Клиенты INNER JOIN (Залы INNER JOIN (Абонементы INNER JOIN Учет ON Абонементы.Код_абонемента=Учет.Код_абонемента) ON Залы.Код_зала=Абонементы.Код_зала) ON Клиенты.Код_клиента=Учет.Код_клиента
GROUP BY Клиенты.Код_клиента, Клиенты.Фамилия, Клиенты.Имя, Залы.Код_зала
HAVING (((Залы.Код_зала)=1001));

1. Подсчитать количество человек, занимающихся у каждого тренера с помощью запроса «Количество клиентов у тренера»:

SELECT Тренеры.Код_тренера, Тренеры.Фамилия_имя_тренера, Count(Клиенты.Код_клиента) AS Количество FROM Тренеры INNER JOIN Клиенты ON Тренеры.Код_тренера = Клиенты.Код_тренераGROUP BY Тренеры.Код_тренера, Тренеры.Фамилия_имя_тренера;

1. Определить клиента по его коду с помощью запроса «Определение клиента по коду».

2. Проверить оплату за месяц по коду клиента с помощью запроса «Определение оплаты за месяц».

3. Вычислить премию для тренеров с больше чем одним клиентом в виде 40% от оклада с помощью запроса «Расчет премии для тренера»:

SELECT Тренеры.Код_тренера, Тренеры.Фамилия_имя_тренера, Count(Клиенты.Код_клиента) AS Количество, [Оклад]*0.4 AS Премия
FROM Тренеры INNER JOIN Клиенты ON Тренеры.Код_тренера=Клиенты.Код_тренера
GROUP BY Тренеры.Код_тренера, Тренеры.Фамилия_имя_тренера, [Оклад]*0.4
HAVING (((Count(Клиенты.Код_клиента))1));

1. Выбрать клиентов-должников с помощью запроса «Определить клиентов-должников»:

SELECT Клиенты.Код_клиента, Учет.Произведена_оплата FROM Клиенты INNER JOIN Учет ON Клиенты.Код_клиента = Учет.Код_клиента WHERE (((Учет.Произведена_оплата)=No));

1. На основании предыдущего запроса создать таблицу «Клиенты-должники» с помощью запроса на создание таблицы.

2. На основании двух связанных таблиц «Клиенты» и «Учет» проверить оплату клиентом по его коду с помощью запроса «Учет оплаты клиентом».

3. Создать запрос для добавления записей в таблицу. Добавить с его помощью в таблицу «Залы» запись «Зал для занятий фитнесом».

4. Создать копию таблицы «Абонементы». Создать запрос на изменение данных в таблице «Абонементы_Копия», с помощью которого, увеличить стоимость абонементов на 15%.

5. Создать копию таблицы «Учет». Создать запрос на удаление данных в таблице «Учет-Копия», с помощью которого удалить данные за январь.

6. Создать отчет с группировкой по месяцам на основании таблицы «Учет».

7. Создать макросы для открытия всех таблиц и отчета с группировкой.

8. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

   • анализ предметной области,

   • структуру спроектированных таблиц,

   • схему данных со связями между таблицами,

   • примеры форм, обеспечивающих интерфейс пользователя,

   • запросы (в режиме Конструктора),

   • отчеты (в режиме отчета и в режиме Конструктора),

   • главную кнопочную форму.

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

Вариант №3

1. Установить связь между таблицами базы данных «Аэропорт», предусмотрев обеспечение целостности данных, каскадное обновление связанных полей и каскадное удаление связанных записей.

2. Создать следующие запросы, задав для них смысловые имена.

• Запрос на выборку. Для отображения информации о пассажирах заказавших билет на Вашингтон. На экран вывести следующие поля: Фамилия, Имя, Отчество, № Паспорта, Гражданство.

• Запрос на групповые операции. Для отображения общего количества пассажиров из трех произвольных стран. На экран вывести следующие поля: Гражданство, Общее количество пассажиров.

• Параметрический запрос. Для отображения Фамилии, Имени, № Паспорта, Гражданства пассажиров на заданный параметром “названии” аэропорта.

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

• Запрос на создание таблицы. Для создания таблицы «Самолеты». Создайте таблицу «Самолеты» с полями Код самолета (Счетчик), Тип самолета (Текстовое), Вместимость (Числовое). Добавьте в созданную таблицу 15 записей с характеристиками самолетов.

• Запрос на удаление. Для удаления информации из таблицы «Пассажиры» пассажиров из Японии.

1. Создать следующие формы, задав для них смысловые имена.

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

• Создать форму, отображающую следующую информацию: Фамилия, Имя, Отчество, № паспорта пассажиров и Типа самолета. В область примечаний добавить Время вылета.

1. Создать отчет, отображающий следующую информацию о рейсах: Фамилия, Имя, Отчество, № паспорта пассажиров, и Название Города аэропорта. В нижний колонтитул добавить свою фамилию, номер группы и дату создания базы данных.

2. Создать отчет с группировкой по полю «Тип самолета» на основании данных из таблицы «Рейсы». Подсчитать количество рейсов для каждого типа самолета. Выполнить сортировку по полю «Тип самолета» в алфавитном порядке.

3. Создать макросы для открытия всех таблиц базы данных. Задать смысловые имена макросам.

4. В режиме Конструктора создать форму «Пользовательский интерфейс БД «Аэропорт»» (Главную кнопочную форму базы данных Аэропорт), позволяющую работать с созданной базой данных. На форме отобразить информацию о названии базы данных и об авторе. Поместить на форме командные кнопки, позволяющие открывать все таблицы, запросы, формы и отчеты, задать для них смысловые имена. Отредактировать форму с помощью элементов рисования панели элементов. Поместить на нее свою фотографию.

5. Создать предложенную схему данных.

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

2. Сохранить итоговую схему данных с учетом добавленной таблицы.

3. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

• анализ предметной области ( в виде ER-диаграммы),

• структуру спроектированных таблиц,

• схему данных со связями между таблицами,

• примеры форм, обеспечивающих интерфейс пользователя,

• запросы (в режиме Конструктора),

• отчеты (в режиме отчета и в режиме Конструктора),

• главную кнопочную форму.

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

Вариант №4

1. С помощью SQL-запроса изменить структуру таблицы Залы. Добавим атрибут Площадь, тип данных – числовой. ALTER TABLE Залы ADD COLUMN Площадь FLOAT;

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

3. Создайте запрос на выборку «Экскурсии стоимостью свыше 100 руб». Включить в макет запроса поля «Код экскурсии», «Время проведения», «График экскурсии», «Срок действия экскурсии», «Стоимость экскурсии». Выполнить запрос.

4. На основании предыдущего запроса создать таблицу «Экскурсии со стоимостью свыше 100 руб» с помощью запроса на создание таблицы.

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

6. На основании данных из таблиц «Сотрудники» и «Экскурсии», а также запроса с групповой операцией определить, сколько экскурсий закреплено за каждым экскурсоводом с помощью групповой операции Count:.

7. На основании данных из таблиц «Сотрудники» и «Экскурсии», а также многотабличного запроса определить, кто из сотрудников ответственен за проведение экскурсий до 12:00. В макет запроса включить поля «ФИО», «График экскурсии», «Стоимость экскурсии», «Код экскурсии».

8. На основании данных из таблицы «Сотрудники» осуществить выборку данных о зале музея по коду ответственного за него. В макет запроса включить поля «Код сотрудника», «ФИО», «Должность»

9. На основании таблиц Сотрудники и Залы создать отчет с группировкой по полю Код ответственного, включив в макет отчета поля «Код ответственного». Включить в макет отчета поля «Код ответственного», «ФИО», «Должность», «Код зала» «Наименование», «Площадь».

10. На основании таблиц Залы и Экспонаты создать отчет с группировкой по двум уровням: сначала по полю «ФИО», а затем по полю «Код ответственного». Включить в макет отчета поля «Код ответственного», «ФИО», «Должность», «Код зала» «Наименование», «Площадь».

11. В режиме конструктора создать форму «Пользовательский интерфейс БД «Деятельность музея»» (Главную кнопочную форму базы данных Аэропорт), позволяющую работать с созданной базой данных. На форме отобразить информацию о названии базы данных и об авторе. Поместить на форме командные кнопки, позволяющие открывать все таблицы, запросы, формы и отчеты, задать для них смысловые имена. Отредактировать форму с помощью элементов рисования панели элементов. Поместить на нее свою фотографию.

12. Создать макросы для открытия всех таблиц и отчета с группировкой.

13. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

    • анализ предметной области,

    • структуру спроектированных таблиц,

    • схему данных со связями между таблицами,

    • примеры форм, обеспечивающих интерфейс пользователя,

    • запросы (в режиме Конструктора),

    • отчеты (в режиме отчета и в режиме Конструктора),

    • главную кнопочную форму.

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

Вариант №5

1. Создать новую таблицу «Парки» и простую одноименную форму для ввода в нее данных:

1. Внести в таблицу 15 записей.

2. Создать следующие запросы, задав для них смысловые имена:

• Запрос на выборку, для отображения информации о клиентах взявших в прокат автомобили марки Nissan. На экран вывести следующие поля: Фамилия, Имя, Отчество, Марка автомобиля, Госномер.

• Запрос на групповые операции, для отображения общего количества клиентов каждого парка. На экран вывести следующие поля: Номер парка, Телефон парка, Адрес парка, Количество клиентов.

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

• Перекрестный запрос, отображающий информацию о залогах, оставленных за пользование автомобилем определенной марки («Ниссан» , «Форд», «Ситроен») в каждом из автопарков.

• Запрос на создание таблицы, для создания таблицы «Автомобили Nissan», содержащую информацию обо всех имеющихся автомобилях марки Nissan. Таблица должна содержать следующие поля: Марка автомобиля, Цена проката, Номер парка, Описание.

• Запрос на обновление, для снижения цен в таблице «Автомобили Nissan» за прокат автомобилей на 10% в связи с рекламной акцией, проводимой компанией Nissan.

1. Создать следующие формы, задав для них смысловые имена:

• Подчиненную форму, отображающую данные из таблиц «Прокат» и «Автомобили». В созданную форму добавить кнопки для перехода между записями.

• Форму с вычисляемым полем, отображающую следующую информацию: Дата начала проката, Дата окончания проката, Фамилия, Имя, Отчество, Марка автомобиля, Цена проката. В область примечаний добавить цену со скидкой на 10% на прокат данного автомобиля.

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

2. Создать макросы (шесть макросов) для открытия таблиц и форм базы данных. Задать смысловые имена макросам.

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

4. Создать макрос для автоматического открытия формы «Пользовательский интерфейс».

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

6. Сохранить итоговую схему данных с учетом добавленной таблицы.

7. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

• анализ предметной области,

• структуру спроектированных таблиц,

• схему данных со связями между таблицами,

• примеры форм, обеспечивающих интерфейс пользователя,

• запросы (в режиме Конструктора),

• отчеты (в режиме отчета и в режиме Конструктора),

• главную кнопочную форму.

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

Вариант №6

1. На основе созданной БД «Сведения о клиентах и заказах» создать следующие запросы и отчеты. Перед этими операциями создать копии всех таблиц Вашей БД и запросы на изменение выполнять над копиями своей БД.

2. С помощью запроса на выборку и таблиц «Заказы» и «Клиенты» получить информацию о клиентах и заказах на телевизоры 2010 года выпуска. Результирующая таблица должна включать поля :

Номер заказа

Дата заказа

Название

Серийный номер

Присвоить запросу соответствующее имя – «Информация о телевизорах 2010 года выпуска».

1. На основе предыдущего запроса и запроса на создание таблицы создать новую таблицу « Заказанные телевизоры 2010 года выпуска» и сохранить ее в базе данных.

2. С помощью запроса на выборку и таблиц «Заказы» и «Клиенты» подсчитать количество заказанных товаров по клиентам и отсортировать эти данные в порядке возрастания количества. Результирующая таблица должна включать поля :

Код клиента

Название

Количество

Присвоить запросу соответствующее имя – «Данные о заказанных телевизорах по клиентам».

1. Выбрать из таблицы «Заказы» номера заказов и серийные номера товаров, а так же вычислить количество дней исполнения заказа:

SELECT Заказы.[Номер заказа], Заказы.[Серийный номер], Заказы![Дата завершения]-Заказы![Дата заказа] AS [Количество дней]

FROM Заказы

ORDER BY Заказы.[Номер заказа];

1. На основе запроса на добавление добавьте в Таблицу «Заказы» запись со следующими значениями полей:

INSERT INTO Заказы ( [Номер заказа], [Дата заказа], [Код сотрудника], [Код клиента], [Серийный номер], [Дата завершения], [Ставка налога] )
VALUES (11, #02/22/2009#, 8, 9, '31548722 ', #02/22/2009#, 19);.

1. На основе запроса на удаление удалите из Таблицы «Сотрудники», запись со следующими значениями:

DELETE [Код сотрудника]

FROM Сотрудники

WHERE [Код сотрудника]=7;.

Скопируйте лист с полученной таблицей, поместив копию в той же книге.

1. Создать запрос на создание таблицы «Сотрудники и заказанные модели» на основе имеющихся таблиц» «Сотрудники» и «Модели»:

SELECT Сотрудники.Фамилия, Сотрудники.Имя, Товары.Модель, Модели.Описание INTO [Сотрудники и заказанные модели]

FROM (Модели INNER JOIN Товары ON Модели.Модель = Товары.Модель) INNER JOIN (Сотрудники INNER JOIN Заказы ON Сотрудники.[Код сотрудника] = Заказы.[Код клиента]) ON Товары.[Серийный номер] = Заказы.[Серийный номер];

Макет создаваемой по запросу таблицы:

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

Запрос на обновление:

UPDATE Заказы SET Заказы.[Ставка налога] = [Заказы]![Ставка налога]+1;

Присвоить запросу соответствующее его смыслу имя.

1. С помощью однотабличного запроса выбрать из таблицы «Заказы» данные о заказах, сделанных клиентами из города Тверь. Выбор кодов клиента по городу осуществить в виде подзапроса.

Результирующая таблица должна включать поля:

Номер заказа

Дата заказа

Код клиента

1. С помощью многотабличного запроса выбрать серийные номера и модели товаров из таблицы «Товары», с последней датой заказа.

Результирующая таблица должна включать поля:

Серийный номер

Модель

Дата заказа

1. Создать отчет с группировкой по городам по данным таблицы «Клиенты», подсчитав при этом количество заказанных телевизоров по городам. Названия городов отсортировать в алфавитном порядке.

2. Создать отчет с группировкой по датам (годам) по данным таблицы «Заказы», подсчитав при этом среднее количество заказов по городам в течение года. Заказы внутри групп отсортировать по номеру заказа по возрастанию.

3. Создать макросы для открытия всех таблиц и отчета с группировкой.

4. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

   1. анализ предметной области,

   2. структуру спроектированных таблиц,

   3. схему данных со связями между таблицами,

   4. примеры форм, обеспечивающих интерфейс пользователя,

   5. запросы (в режиме Конструктора),

   6. отчеты (в режиме отчета и в режиме Конструктора),

   7. главную кнопочную форму.

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

Вариант №7

1. Использовать базу данных в среде MS ACCESS  с именем Фамилия студента.accdb, состоящую из 6-и таблиц: SPSTUD, FACLT, OSENKI, DISCIPLIN, SPECIAL, SPREP.

2. Создать запрос на выборку, отобрав из таблицы OSENKI все записи с оценками «4» «5».

3. Создать запрос на выборку, отобрав из таблицы OSENKI все записи с оценками «3».

4. Создать запрос для отбора оценок студентов каждой из групп специальности по результатам последней сессии.

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

6. Создать запрос к базе данных Фамилия студента.accdb, содержащий информацию о студентах Дальневосточного института управления, сдавших предмет «Информационные технологии в юридической деятельности», на отлично. Данные в запросе отсортировать по полю FIO. Запрос создаём на основе данных из таблиц  SPSTUD, OSENKI, DISCIPLIN под управлением Мастера запросов.

7. Для создания запроса  на вкладке Создание, в  группы Другие, нажимаем кнопку . В Окне Новый запрос    выбираем Простой запрос.

8. На первом шаге создания запроса под управлением Мастера запросов выбираем таблицы и поля, которые нужно включить в запрос. Поля для формирования запроса могут размещаться в нескольких таблицах. В нашем случае выбираем из таблицы SPSTUD все поля, за исключением DATA_POST,     в таблице OSENKI выбираем первые 4 поля  , в  таблице DISCIPLIN все поля   Нажимаем «Далее».

9. На втором шаге выбрать вид отчёта: Подробный или Итоговый отчет. Мы хотим создать Подробный отчет. Нажимаем «Далее».

10. На шаге 3 задать название запроса Информационные технологии в юридической деятельности (5) и выбираем возможность просмотра данных в сформированном запросе или же сделать изменения в макете запроса: Изменить макет запроса. Выбрать последний вариант  и нажимаем кнопку Далее. Запрос открывается в конструкторе запросов.

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

• В колонке поля FIO задать сортировку по возрастанию,

• в поле SEMESTR строки Условие отбора ввести 1 (первый семестр),

• в поле OSENKA строки Условие отбора задать условие 5 (отлично),

• в поле NAME_PRED строки Условие отбора ввести «Информационные технологии в юридической деятельности».

1. Закрыть окно конструктора и выполнить запрос командой Открыть или двойным щелчком мышью по имени запроса. Результат отбора данных будет показан на экране в виде таблицы

2. Самостоятельно: создать запрос с именем Информационные технологии (4 или 5), содержащий информацию о студентах факультета экономики и права, сдавших предмет «Информационные технологии в юридической деятельности» на хорошо и отлично. В условии отбора оценок использовать конструкцию OR, ввести ограничение на поле NOM_FCT – для вывода списка студентов Факультета экономики и права .

3. На основе таблиц OSENKI и SPSTUD создать отчет с группировкой по полям NOM_GR, подсчитав среднюю оценку по последней сессии для каждой из групп.

4. Создать макросы для открытия всех таблиц и отчета с группировкой.

5. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

   • анализ предметной области,

   • структуру спроектированных таблиц,

   • схему данных со связями между таблицами,

   • примеры форм, обеспечивающих интерфейс пользователя,

   • запросы (в режиме Конструктора),

   • отчеты (в режиме отчета и в режиме Конструктора),

   • главную кнопочную форму.

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

Вариант №8

1. На основе созданной БД «Газета объявлений» создать следующие запросы и отчеты. Перед этими операциями создать копии всех таблиц Вашей БД и запросы на изменение выполнять над копиями таблиц своей БД.

2. Создайте многотабличный запрос «Все газеты за январь» для выборки всех газет, изданных в январе 2013 года. В бланк запроса включить связанные таблицы «Подача объявлений», «Выход объявлений», «Выпуски газет». Использовать поля, Номер, Код объявления, Код подрубрики, Категория, Текст. В Бланк запроса добавить вычисляемое поле, в которое вставить выражение со встроенной функцией Year([Дата]) для чего воспользоваться построителем выражений. В строке условия отбора задать 2013. Для корректного выполнения запроса в таблицах «Выпуски газет» и «Подача объявлений» необходимо иметь записи с датами выпусков газет и подачей объявлений в 2013 году.

3. Создайте многотабличный запрос «Подача бесплатных объявлений» для выборки всех бесплатных объявлений. В бланк запроса включить связанные таблицы «Выход объявлений» и «Подача объявлений», Использовать поля, Номер газеты, Тип (условие отбора – 0), Код объявления, Код подрубрики, Категория, Текст.

4. Создайте многотабличный запрос «Количество объявлений в месяце» для выборки всех бесплатных объявлений. В бланк запроса включить связанные таблицы «Выход объявлений» и «Подача объявлений». В бланк запроса добавить вычисляемые поля Год (функция Year(Дата)), Месяц (функция MonthName(Дата)), Количество объявлений с групповой операцией Sum для таблицы «Выход объявлений». Расположить месяца по возрастанию, задав сортировку для поля Month(Дата).

1. Создайте многотабличный запрос «Количество объявлений каждого типа в месяце» для выборки всех бесплатных объявлений. В бланк запроса включить связанные таблицы «Выход объявлений» и «Подача объявлений». В бланк запроса включить связанные таблицы «Выход объявлений» и «Подача объявлений». В бланк запроса добавить вычисляемые поля Год (функция Year(Дата)), Номер Месяца, Месяц (функция MonthName(Дата)), Тип, Количество объявлений с групповой операцией Sum для таблицы «Выход объявлений».

1. Создайте многотабличный запрос «Процент объявлений каждого типа» на основе двух созданных запросов: «Количество объявлений в месяце» и «Количество объявлений каждого типа в месяце».

2. Создайте многотабличный запрос «Стоимость объявлений в каждом номере». В бланк запроса включить связанные таблицы «Выход объявлений» и «Тарифы на объявления». Использовать групповую операцию Sum по полю «Стоимость объявлений « для второй таблицы.

3. Создать перекрестный запрос «Выход объявлений» на основе таблицы «Выход объявлений», посчитав с его помощью количество объявлений каждого типа для каждого номера газеты. В строках итоговой таблицы расположить тип объявлений и Итоговое значение (Сount) по полю Код объявления, в столбцах – номер газеты, в поле значений на пересечении – количество объявлений каждого типа (операция Count по полю Код объявления).

4. Создать запрос на выборку «Длина текста» на основе таблицы «Подача объявлений» и полей «Код объявления», «Текст», посчитав с его помощью количество символов в тексте объявления. Добавить в бланк запроса вычисляемое поле с функцией Len([Текст]).

5. Создать отчет с группировкой «Типы объявлений» на основе таблиц «Выход объявлений» и «Тарифы на объявления», сгруппировав с его помощью все объявления одинаковых типов.

6. Создать отчет с группировкой «Стоимость объявлений» по цене и дате ввода тарифов.

7. Создать отчет с группировкой по «Выпуски газеты годам» сгруппировав с его помощью все выпуски газет по годам.

8. Создать макросы для открытия всех таблиц и отчетов с группировкой.

9. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

   • анализ предметной области,

   • структуру спроектированных таблиц,

   • схему данных со связями между таблицами,

   • примеры форм, обеспечивающих интерфейс пользователя,

   • запросы (в режиме Конструктора),

   • отчеты (в режиме отчета и в режиме Конструктора),

   • главную кнопочную форму.

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

Вариант №9

1. На основе созданной БД «Деканат» создать следующие запросы и отчеты. Перед этими операциями создать копии всех таблиц Вашей БД и запросы на изменение выполнять над копиями таблиц своей БД.

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

3. Создать многотабличный простой запрос «Хорошисты» для отбора всех слушателей по всем специальностям, которые являются сдали все сессии на «4» и «5» .

4. Создать многотабличный простой запрос «Отличники» для отбора всех слушателей по всем специальностям, которые являются отличниками .

5. Создать многотабличный простой запрос «Специальности_Экзамены» на основе таблиц «Специальности», «Предметы», «Распределение часов» для отбора предметов по специальностям, по которым сдаются экзамены.

6. Создать многотабличный простой запрос «Специальности_Экзамены» на основе таблиц «Специальности», «Предметы», «Распределение часов» для отбора предметов по специальностям, по которым сдаются зачеты.

7. Создать многотабличный простой запрос «Специальности_Количество часов» на основе таблиц «Специальности», «Предметы», «Распределение часов» для отбора предметов по специальностям, по которым количество часов превышает или равно 108.

8. Создайте перекрестный запрос «Перекрестный_ Спец_Предметы_Экзамен», у которого в поле строк находятся специальности, в поле столбцов предметы, в поле значений – экзамен (вид контроля). Используйте таблицы «Специальности», «Предметы», «Распределение часов»

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

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

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

12. Создать отчет с группировкой «Предметы в сессию», создав группы слушателей, которые сдают определенные предметы в сессию .

13. Создать отчет с группировкой «Стоимость объявлений» по цене и дате ввода тарифов.

14. Создать отчет с группировкой по «Хорошо успевающие слушатели» сгруппировав с его помощью всех слушателей, которые сдали предметы на «4».

15. Создать макросы для открытия всех таблиц и отчетов с группировкой.

16. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

    • анализ предметной области,

    • структуру спроектированных таблиц,

    • схему данных со связями между таблицами,

    • примеры форм, обеспечивающих интерфейс пользователя,

    • запросы (в режиме Конструктора),

    • отчеты (в режиме отчета и в режиме Конструктора),

    • главную кнопочную форму.

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

Вариант №10

1. На основе созданной БД «Аренда помещений» создать следующие запросы и отчеты. Перед этими операциями создать копии всех таблиц Вашей БД и запросы на изменение выполнять над копиями своей БД.

2. Создать запрос с повторяющимися записями на основе таблицы «Помещения», посчитав с его помощью количество повторов для районов Донецка, в которых сдаются помещения. Включить в результирующую таблицу поля Тип помещения и Цена.

3. С помощью запросов добавить в таблицу «Страны» две новые страны, в таблицу «Города» - по два города для каждой новой страны, в таблицу «Договора» - новые договора, а в таблицу «Помещения» по два новых помещения для каждого добавленного договора.

4. С помощью запросов добавить в таблицу «Помещения» все необходимые данные по арендуемым помещения для всех девяти районов города Донецка. В каждом районе города должно быть не менее 4-х арендуемых помещений.

5. Создать запрос «Запрос с параметром» для отбора помещений Калининского района. В итогом запросе необходимо показать Код помещения, Площадь, Адрес и Стоимость 1 кв. м.

6. С помощью запроса создать таблицу на основе предыдущего запроса и сохранить ее БД с именем «Помещения Калининского района».

7. Создать простой запрос «Помещения для клубов» для отбора помещений с площадью не менее 100 кв. м. и Стоимостью 1 кв. м. не более 150€.

8. Создать многотабличный запрос «Общая стоимость аренды по городам», на основе таблиц Страны, Города, Справочник арендатора, Договоры, Помещения. В бланк запроса включить поля Код страны, Страна, Город, Арендатор, Код помещения, Тип помещения, Площадь, Цена, а также вычисляемое поле Стоимость аренды, для которого посчитать эту стоимость как произведение Площади на Цену.

9. На основании предыдущего запрос создать перекрестный запрос «Общая стоимость аренды по городам», у которого в строках находятся страны и арендаторы, в столбцах - города, в поле значений - стоимость аренды помещения.

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

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

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

13. Создать отчет с группировкой «Помещения по районам», создав группы помещений по районам .

14. Создать отчет с вложенной группировкой «Помещения по странам и городам», создав группы по странам и городам, подсчитав суммарное количество площадей по каждой группе.

15. Создать макросы для открытия всех таблиц и отчетов с группировкой.

16. Результат выполнения работы представляется в виде итоговой базы данных и документа Word, который должен содержать:

    1. анализ предметной области,

    2. структуру спроектированных таблиц,

    3. схему данных со связями между таблицами,

    4. примеры форм, обеспечивающих интерфейс пользователя,

    5. запросы (в режиме Конструктора),

    6. отчеты (в режиме отчета и в режиме Конструктора),

    7. главную кнопочную форму.

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

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Необходимо отметить, что запросы являются самым главным, мощным и гибким инструментом обработки данных в MS Access. Рассмотренный нами процесс создания базы данных с оптимальной структурой предполагает разбиение множества данных на таблице при максимальной безызбыточности данных. Как отмечалось в предыдущей лекции, структура таблицы должна полно, однозначно и непротиворечиво описывать определенный тип объектов. А каждая строка таблицы является описанием одного конкретного объекта данного типа. Эти таблицы должны быть связаны между собой множеством разнообразных логических связей, которые корректно отображаются в схеме связи данных. Однако особенностью работы с базами данных заключается в том, что в процессе работы пользователю бывает необходимо устанавливать между данными новые (временные) связи или обрабатывать несвязанные данные из разных таблиц, получать и сохранять промежуточные результаты, создавать новые таблицы, автоматически обновлять массивы информации в таблицах, задавать условия отбора данных для обработки как в самом запросе, так и диалоговом режиме, сортировать и упорядочивать выборку - и все эти функции выполняются в MS Access с помощью запросов.

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

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

Назначение, виды и режимы запросов.

Средствами запроса можно:

• просматривать содержимое таблицы или формы без ее открытия;

• выбрать записи, удовлетворяющие условиям отбора;

• объединить на экране (не меняя содержимого таблиц) данные из одной или нескольких таблиц;

• включить в результирующую таблицу запроса нужные поля;

• произвести вычисления в каждой из полученных записей;

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

• произвести обновление полей в выбранном подмножестве записей;

• создать новую таблицу базы данных, используя данные из существующих таблиц;

• удалить выбранное подмножество записей из таблицы базы данных;

• добавить выбранное подмножество записей в другую таблицу.

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

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

Последовательное выполнение ряда запросов позволяет решать достаточно сложные задачи, не прибегая к программированию. В MS Access может быть создано несколько видов запросов.

• Запрос на выборку - выбирает данные из взаимосвязанных таблиц и других запросов. Результатом его является таблица, которая существует до закрытия запроса.

• Запрос на создание таблицы - основан на запросе выборки, но, в отличие от него, результат запроса сохраняется в новой таблице.

• Запросы на обновление, добавление, удаление - это запросы-действия, в результате выполнения которых изменяются данные в таблицах.

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

Создание запроса.

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

Чтобы начать создание запроса, нужно перейти на вкладку Создание и выбрать или Мастер запросов или Конструктор запросов.

При выборе команды Мастер запросов открывается окно Новый запрос (рис.5.5), где можно выбрать вид создаваемого запроса. Рассмотрим эти виды.

• Простой запрос. Запускается мастер, помогающий создать запрос.

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

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

• Записи без подчинения. Данный запрос используется для сравнения двух таблиц и поиска всех записей в первой таблице, для которых не имеется подчинённых записей во второй таблице.

Простой запрос.

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

Литература

1. http://cie.ifmo.ru/doc/effective_microsoft_office.pdf Эффективная работа с Office 2007

2. Информатика. Базовый курс: учебник для вузов/ под ред. С. В. Симоновича. -3-е изд. - Санкт-Петербург [и др.] : Питер, 2012. -637 с.

3. Семакин И.Г. Информатика и ИКТ. Базовый уровень: учебник для 10-11 классов/ И.Г.Семакин, Е.К.Хеннер.– 5-е изд. – М.: БИНОМ, Лаборатория знаний, 2009. – 246 с.

4. Угринович Н.Д. Информатика и ИКТ. Профильный уровень: учебник для 11 класса / Н.Д.Угринович. – 2 -е изд., испр. и доп. – М.: БИНОМ. Лаборатория знаний, 2009. – 308 с.

5. Информатика. Задачник-практикум в 2 т. / Л.А.Залогова и др. Под ред. И.Г.Семакина, Е.К.Хеннера: Том 1-2. – 3-е изд., испр. – М.: БИНОМ, Лаборатория знаний, 2006. – 309 с.

6. http://www.ict.edu.ru/