ПР №2 Числовые форматы. Условное форматирование.

Практическая работа № 2 Числовые форматы. Условное форматирование. Консолидация

“Форматирование.

Форматирование. Для создания пользовательского формата выполните следующее:

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

2. В меню Формат выберите команду Ячейки (через правую кнопку Формат ячеек), а затем откройте вкладку Число.

3. В списке Числовые форматы выберите пункт (все форматы).

4. Введите в поле Тип коды форматов. Образец показывает, как будет выглядеть значение после применения формата.

1 Упражнение

Задать код для номера телефона со знаками чисел до 11 знаков.

1. В меню Формат выберете команду Ячейки (через правую кнопку Формат ячеек), далее выберете вкладку Число.

2. В списке Числовые форматы выберите пункт (все форматы).

3. В поле Тип введите следующий код 8 (000) 000-00-00.

Тогда, например значение 4998356428 будет выведено как 8 (499) 835-6428.

1. В ячейке введена скорость 250 километров в час. Создать пользовательский формат, чтобы число 250 выводилось как 250 км/час.

2. Создать пользовательский формат, который дописывает к дате краткое название месяца (3 буквы).

Условное форматирование. Чтобы применить условное форматирование к ячейке или диапазону, выполните следующие действия:

1. Выделите ячейку или диапазон.

2. В меню Формат выберите команду Условное форматирование.

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

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

5. Щелкните на кнопке Формат и определите форматирование, которое нужно применить, если заданное условие удовлетворяется.

6. Чтобы добавить дополнительные условия, щелкните на кнопке «А также» и повторите пункты 3-5.

В ходе аттестации студентов факультета «Кредит» получены следующие результаты (см. рис.6).

Предусмотреть заливку соответствующим цветом: если студент получил баллы от 51 до 70, то – синим, если от 71 до 85, то – желтым, если от 86 и выше, то красным цветом. В противном случае (если количество баллов от 0 до 51) цвет оставить без изменения.

Решение

1. Выделяем диапазон ячеек D2:N10.

2. В меню Формат выбираем Условное форматирование.

Рис. 6

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

Рис. 7

1. Для заливки необходимым цветом щелкаем на кнопку Формат, далее во вкладке Вид выбираем нужный цвет.

2. После того как щелкните на кнопке ОК, появится результат как показано на рис.6.

1 Упражнение (Консолидация по расположению)

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

1. Заполняем рабочую книгу данными о получаемых сотрудниками зарплате и премиях, поместив информацию о каждом месяце на отдельный лист. Выделив два листа рабочей книги, вводим в 1-ю строку шапку таблицы «Фамилия», «Оклад», «Премия» и в первый столбец любые 3 фамилии.

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

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

4. Поместим курсор под названием 1-го столбца, т.е. «Фамилия».

5. Выбираем в меню Данные команду Консолидация.

6. В поле Функция из списка функций выбираем функцию Сумма.

7. Установив курсор в поле Ссылка, щелкнем мышью на ярлыке первого листа и выделяем на нем все фамилии и числовые значения.

8. Нажимаем кнопку Добавить в окне Консолидация.

9. Вернувшись в поле Ссылка, аналогичным образом добавим данные со 2го листа.

10. В наборе флажков Использовать в качестве имен устанавливаем флажок, соответствующий расположению в исходной части заголовков:

Подписи левого столбца.

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

2 Упражнение (Консолидация по категориям)

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

1. Воспользуемся данными первых 2-х листов из упражнения 1.

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

3. На новом рабочем листе (или в новом месте 3-го листа) формируем итоговую таблицу, скопировав или введя заново названия столбцов «Фамилия» и «Премия».

4. Выделяем названия столбцов, т.е. «Фамилия» и «Премия».

5. Выбираем в меню Данные команду Консолидация.

6. В поле Функция из списка функций выбираем функцию Сумма.

7. С помощью кнопки Удалить удаляем старые ссылки из поля Список Диапазонов.

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

9. Нажимаем кнопку Добавить в окне Консолидация.

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

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

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