Методические указания по выполнению контрольной работы 3 Образец оформления титульного листа 4 Задание на контрольную работу 5

Правила создания списков в MS Excel

• 
  Каждый столбец должен содержать информацию одного типа. В списке товаров, например, один столбец стоит отвести под название товара, другой - под его стоимость, третий - под дату продажи и т.д., а не "валить все в одну кучу" как это часто бывает.
  
• 
  Лучше делить информацию на как можно большее количество столбцов (полей). Например, ФИО лучше делить на три отдельных столбца с фамилией, именем и отчеством, соответственно, чтобы впоследствии легко найти всех людей с нужным именем (а если завтра "Татьянин День"?)
  
• 
  Верхняя строка списка должна быть "шапкой", т.е. содержать заголовки столбцов. К сожалению, Excel в большинстве случаев не умеет корректно работать с многоуровневыми "шапками", поэтому красивую многострочную "шапку" с кучей объединенных ячеек лучше приберечь для отчета или презентации, а в реальном списке ограничиться однострочной без пустых и объединенных ячеек.
  
• 
  Список не должен содержать внутри пустых строк и столбцов - это может вызывать большое количество сложностей в будущем, поскольку  Excel считает пустые строки/столбцы окончанием текущего списка.
  
• 
  Не стоит размещать какие-либо другие данные слева или справа от списка - они могут быть скрыты во время фильтрации списка, например, с использованием Автофильтра.
  

Вариант № 1

1. 
   В ячейке Е2 создать выпадающий список, состоящий из пяти менеджеров. Для этого:
   

• 
  Выделить список менеджеров (в нашем случае это ячейки Н7:Н11) и в меню Вставка – Имя – Присвоить присвоить имя Менеджер;
  
• 
  Находясь в ячейке Е2, Данные - Проверка
  

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

• 
  Выделить всю таблицу, кроме шапки и в меню Формат выбрать команду Условное форматирование
  

3. 
   Рассчитать на какую сумму отгружено товара для соответствующего менеджера. Для этого:
   

• 
  В свободную ячейку ввести следующую формулу:
  

Если всё выполнено верно, должны получиться следующие результаты:

Вариант № 2

• 
  "Ночной тариф". 1 час работы в Интернет с 1.30 до 8.30 утра стоит 150 рублей;
  
• 
  "Дневной тариф". 1 час работы в Интернет с 15 до 19 часов в будние дни стоит 350 рублей;
  
• 
  "Обычный тариф". 1 час работы в Интернет в остальное время (будние дни - с 8.30 до 15 часов и с 19 до 1.30; выходные дни - с 8.30 утра до 1.30 ночи) стоит 250 рублей.
  

Входная информация таблицы следующая:

1. 
   Список интервалов времени, проведенных в Интернет. На каждый день недели – одна строчка.
   
2. 
   Список тарифов с указанием стоимости и границ времени.
   

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

1. 
   Считать время, проведенное в сети, для каждого дня в часах и минутах (ячейки I6:I12)
   
2. 
   Считать время, проведенное в сети, для каждого времени в часах (ячейки Е16:Н22)
   
3. 
   Считать время, проведенное в сети, для каждого дня в часах (ячейки I16:I22)
   
4. 
   Считать время, проведенное в сети, за всю неделю (ячейка I23)
   
5. 
   Рассчитывать стоимость услуг по каждому дню (ячейки J16:J22)
   
6. 
   Рассчитывать сумму расходов за неделю (ячейка J23)
   
7. 
   Автоматически выделять галочкой (  )день с наибольшими расходами
   

Чтобы поставить галочку напротив дня с наибольшими расходами, вначале необходимо найти наибольшее расходы за день (ячейка В15), затем используя функции ЕСЛИ и СИМВОЛ в столбец D, начиная с ячейки D6 занести соответствующую формулу, ставящую галочку в ячейке. Галочке соответствует шрифт Wingdings с кодом 252. Поэтому после написания формулы в диапазоне ячеек с D6 по D12 выбрать шрифт Wingdings.

Вариант № 3

1. 
   На бланке составьте статистический отчет "Осень" о температуре в осенние месяцы в нашем городе следующей формы:
   

• 
  к столбцам "Ср", "Нб", "Нм" установите следующие примечания: "средняя температура месяца", "наибольшая температура месяца", минимальная температура месяца" соответственно;
  

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

(подсказка: функция СЛУЧМЕЖДУ возвращает случайное число между двумя заданными числами. При каждом вычислении рабочего листа возвращается новое случайное число.

• 
  средствами EXCEL вычислите среднемесячную, максимальную и минимальную температуры по месяцам;
  
• 
  используя соответствующую функцию строки "выше средней" заполните словами "да" или "нет" в зависимости от значения среднемесячной температуры и температуры дня;
  
• 
  подсчитайте количество дней, температура которых выше и ниже среднемесячной;
  

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

3. 
   Переименуйте Лист1 в "Отчет";
   

• 
  на Листе2 постройте график "Температура в осенние месяцы", характеризующий изменение температуры по дням для каждого месяца;
  

4. 
   Переименуйте Лист2 в "Температура";
   

• 
  на Листе3 постройте диаграммы, сравнивающие значения среднемесячной, максимальной и минимальной температуры по месяцам;
  

5. 
   Переименуйте Лист3 в "Диаграмма";
   

• 
  результаты работы сохраните в файле "Метеослужба".
  

Вариант № 4

Сохранить её под именем Заказы.

Присвоить Листу 1 имя – Клиенты.

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

Встать на ячейку А1.

В меню Данные выбрать команду Форма.

Появится окно формы Клиенты:

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

2. 
   Отформатируйте таблицу так, чтобы на экране была видна вся информация.
   
3. 
   Отсортируйте данные по наименованиям организаций, а затем по городу в алфавитном порядке.
   
4. 
   Расположите данные в первой строке по центру столбцов и выделите их «полужирным» шрифтом.
   
5. 
   Установите для первой строки голубой фон заливки и синий цвет шрифта.
   
6. 
   Перейдите на второй лист книги Заказы.
   
7. 
   Присвойте листу имя Товары.
   

8. 
   Введите следующую информацию:
   
9. 
   Расположите данные в первой строке по центру столбцов и выделите их «полужирным» шрифтом.
   
10. 
    Установите для первой строки голубой фон заливки и синий цвет шрифта.
    
11. 
    Перейдите на третий лист книги Заказы.
    
12. 
    Присвойте листу имя Заказы.
    

13. 
    Введите заголовки столбцов в таблицу и отформатируйте их по образцу.
    
14. 
    Установите для первой строки голубой фон заливки и синий цвет шрифта.
    
15. 
    Присвойте столбцам А-G следующие имена:
    

    A	Дата
    В	Заказ
    С	Номер2
    D	Товар2
    E	Количество
    F	Цена2
    G	Сумма

16. 
    Перейдите на лист Товары.
    
17. 
    Присвойте столбцам А-С следующие имена:
    

    А	Номер
    В	Товар
    С	Цена

18. 
    Перейдите на лист Клиенты.
    
19. 
    Присвойте столбцу А имя Фирма.
    
20. 
    Перейдите на лист Заказы.
    
21. 
    Ввести в ячейку D2 формулу, обеспечивающую автоматическое заполнение поля Наименование товара при вводе его кода в поле Номер товара.
    

Используйте следующую формулу:

=ЕСЛИ($С2=””;””;ПРОСМОТР($С2;Номер;Товар))

22. 
    Ввести в ячейку F2 формулу, обеспечивающую автоматическое заполнение поля Цена при вводе его кода в поле Номер товара.
    

Используйте следующую формулу:

=ЕСЛИ($С2=””;””;ПРОСМОТР($С2;Номер;Цена))

23. 
    Ввести в ячейку G2 формулу, обеспечивающую автоматическое заполнение поля Сумма при вводе количества в поле Количество.
    

Используйте следующую формулу:

=ЕСЛИ($Е2=””;””;Е2*F2)

24. 
    Скопируйте формулы в ячейках D2, F2 и G2 вниз.
    

25. 
    Заполнить таблицу следующей информацией, вводя данные только о дате, номере заказа, номере товара и его количестве. Если формулы были введены правильно, то информация на экране должна соответствовать приведённой информации в таблице.
    
26. 
    Перейдите на следующий чистый лист и присвойте ему имя Таблица. На этом листе мы создадим сводную таблицу для анализа данных заказов.
    
27. 
    Создайте сводную таблицу, используя Мастер сводных таблиц.
    

В меню Данные выбрать команду Сводная таблица.

1-й шаг. В диалоговом окне установите В списке или базе данных и нажмите кнопку Далее.

2-й шаг. Указать диапазон ячеек для анализа. Так как данные находятся на листе Заказы, то перейти на него и выделить ячейки с А1 по G11. Должно быть примерно следующее:

Нажать Далее.

3-й шаг. Создание структуры сводной таблицы. Перетащите кнопку Номер заказа в поле Столбец, кнопки Наименование товара и Количество в поле Строка, а кнопку Сумма в поле Данные.

Должно получиться приблизительно так, как показано выше, нажмите кнопку Далее.

4-й шаг. Выбор ячейки рабочего листа, откуда будет начинаться сводная таблица. По умолчанию устанавливается первая ячейка листа. Если это не устраивает, то щёлкните на листе ячейку, откуда должна начинаться таблица.

Нажать кнопку Готово.

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

Скрыть первую строку, так как там нет никакой полезной информации, выделив её и выбрав из контекстного меню команду Скрыть.

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

Скрыть строку с Общим итогом, выделив её и выбрав из контекстного меню команду Скрыть.

Скрыть все строки со словом Всего.

Должно получиться приблизительно следующее:

29. 
    Создать диаграмму реализации товаров по заказам.
    

Вариант № 5

1. 
   Создать таблицу по продажам следующего вида:
   

Если бы в нашей задаче было только одно условие (все заказы Григорьева или все заказы в "Копейку"), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ из категории Математические. Но в нашем случае имеются два условия, а не одно, поэтому этот способ не подходит...!!!!!!

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:

В нашем случае задача решается одной формулой:

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

В категории Базы данных можно найти функцию БДСУММ, которая также может помочь нам решить нашу задачу:

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

Так называется надстройка Excel, которая помогает создавать сложные формулы для многокритериального суммирования. Подключить эту бесплатную надстройку можно через меню Сервис - Надстройки - Мастер суммирования. После этого в меню Сервис должна появится команда Частичная сумма, запускающая Мастер суммирования:

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

И, наконец, на 3-м и 4-м шагах указываем ячейку, куда необходимо вывести результат. И получаем в итоге следующее:

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

Вариант № 6

1. 
   Задайте первым трем листам рабочей книги имена: Математические, Статистика+Логика, Текстовые.
   
2. 
   Перейдите на лист Математические и постройте таблицу, приведенную на рисунке 1.
   

Рисунок 1 – Лист Математические

3. 
   В ячейке А2 поставьте цифру 0, и заполните столбец до ячейки А30 с помощью арифметической прогрессии с шагом 0,5. Для этого поставьте курсор на ячейку А2. Выберите пункт меню Правка – Заполнить – Прогрессия. В появившемся окне поставьте флажок Расположение – по столбцам. Задайте шаг – 0,5, и предельное значение – 14.
   
4. 
   Рассчитайте функции, указанные в таблице для всего диапазона значений Х от 0 до 14,0, используя математические функции Excel (пункт меню Вставка – Функция, категория – Математические).
   
5. 
   На листе Статистические постройте таблицы, представленные на рисунке 2:
   

Рисунок 2 – Лист Статистика+Логика

6. 
   Столбец Баллы заполните с помощью функции случайных чисел (диапазон от 0 до 100).
   

7. 
   В столбце Зачет необходимо проставить зачет по тем дисциплинам, количество баллов по которым превышает 61 балл, используя функцию ЕСЛИ (Вставка – Функция – Логические, =ЕСЛИ(B2>=61;"Зачтено";"Не зачтено")).
   
8. 
   К столбцу Зачет применить условное форматирование. Если по дисциплине проставлен зачет, то шрифт ячейки должен стать Синим, Полужирным курсивом; если не проставлен зачет, то Красным, Полужирным курсивом. Для этого выделите столбец Зачет. Выберите пункт меню Формат – Условное форматирование. В графе Условие1 выберите значение – равно – зачтено, далее нажмите кнопку Формат и выберите необходимые критерии форматирования. Затем нажмите кнопку А также и введите необходимые параметры для условия – не зачтено, и нажмите кнопку ОК.
   
9. 
   Произведите расчет среднего, максимального и минимального балла по дисциплине, а также рассчитайте количество дисциплин, используя функции СРЗНАЧ, МИН, МАХ, СЧЕТ (Вставка – Функция, категория – Статистические).
   
10. 
    На листе Текстовые постройте таблицу, приведенную на рисунке 3:
    

Рисунок 3 – Лист Текстовые

11. 
    Заполните столбцы Фамилия, Имя, Отчество произвольными данными (5 человек). Фамилия, Имя, Отчество должны начинаться с большой буквы.
    
12. 
    Вставьте в соответствующие столбцы следующие функции (Вставка – Функция, Категория – Текстовые): ЛЕВСИМВ для столбца Имя, ЛЕВСИМВ для столбца Отчество, СЦЕПИТЬ(B2;" ";E2;".";" ";F2;"."), ДЛСТР (для столбца Фамилия), ПРОПИСН(для столбца Фамилия), СТРОЧН(для столбца Имя).
    

Вариант № 7

В кинотеатре три зала: «Красный», «Зелёный», «Синий». Залы одинаковые и представляют собой квадраты 1111 кресел. В результате маркетинговых исследований были установлены показатели по заполнению зала в среднем:

Вариант № 8

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

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

Т.е. все что надо сделать - это ввести названия товаров в ячейках E3:E8 и названия заказчиков в ячейки F2:H2, а потом ввести в ячейку F3 формулу с нашей функцией:

=СУММПРОИЗВ(($A$2:$A$20=$E3)*($B$2:$B$20=F$2)*($C$2:$C$20))

Говоря простым языком, эта функция суммирует значения стоимости из диапазона $C$2:$C$20, если значения диапазона $A$2:$A$20 (наименования) равны $E3 (грейпфрут), а значения диапазона $B$2:$B$20 (заказчики) равны F$2 (Ланит). Все, что осталось сделать - это скопировать эту формулу на все ячейки отчета (F2:H8).

Другими словами синтаксис этой функции можно представить так:

=СУММПРОИЗВ((условие1)*(условие2)*(что_суммировать))

Причем, никто не мешает Вам добавить третье, четвертое и т.д. условия, расширяя свой отчет.

Если всё сделать правильно, должно получиться следующее

Вариант № 9

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

В столбце G создать два выпадающих списка: 1) список для выбора заказчика и 2) список товаров.

Создать первый выпадающий список для выбора Заказчика

• 
  Выделите G4 и откройте меню Данные - Проверка, затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник - выделите ячейки J2:J12. После нажатия на ОК первый выпадающий список готов.
  
• 
  Второй выпадающий список делается аналогично.
  

Вариант № 10

1. 
   Создать список моделей автомобилей Toyota, Ford и Nissan:
   

2. 
   Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota в меню Вставка - Имя - Присвоить. Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.
   
3. 
   Создать первый выпадающий список для выбора марки автомобиля
   

4. 
   Создать второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Точно так же, как в предыдущем случае, выделите пустую ячейку и откройте меню Данные - Проверка - далее Список. В поле Источник нужно будет ввести вот такую формулу:
   

где F3 - адрес ячейки с первым выпадающим списком - замените на свой.

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