Файл: Работа в редакторе электронных таблиц (16 ч).docx

Рисунок 7

Правила выделения ячеек → Меньше...

1. 
   Форматируются ячейки из выделенного диапазона, значение которых меньше заданного. (Рисунок 8)
   

Рисунок 8

Правила выделения ячеек → Между...

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

Рисунок 9

Правила выделения ячеек → Равно... (рисунок 10)

1. 
   Форматируются ячейки, равные заданному значению. Сравнивать можно и числа, и текст. (Рисунок 11)
   

Рисунок 11

Правила выделения ячеек → Текст содержит...

1. 
   Форматируются ячейки, содержащие заданный текст. (Рисунок 12)
   

Рисунок 12

Правила выделения ячеек → Дата...

1. 
   Правила форматирования применяются к датам. Удобно для контроля графика платежей, отгрузок. (Рисунок 13)
   

Рисунок 13

Правила выделения ячеек → Повторяющиеся значения...

Хороший способ найти «задвоенные» позиции.

1. 
   В настройках можно выбрать и обратный вариант - уникальные значения. (Рисунок 14)
   

Рисунок 14

Использование формул для определения правил условного форматирования

1. 
   Выделяем область для применения условий форматирования (столбец "В")
   
2. 
   Переходим на вкладку "Главная", выбираем "Сортировка" и далее "Создать правило"
   
3. 
   В появившемся окне выбираем тип правил (рисунок 15)
   

Рисунок 15

4. 
   В нижней части окна необходимо произвести необходимые настройки
   
5. 
   Вводим формулу, указанную на рисунке 16
   

---

Рисунок 16

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

С правилами условного форматирования все становится намного проще: мы сразу видим позиции, которые требуют заказа. (Рисунок 17)


Рисунок 17

Сохранить задание в файл с именем Фамилия_ПР_5_8.xlsx

---

Задание 9. Создание и работа с диаграммами и графиками

Рассмотрим в качестве примера таблицу. (Файл Кейс7.xlsx) Имеем информацию о продажах каждого менеджера за пять месяцев. (Рисунок 1)


Рисунок 1

Построим и опишем самые полезные графики.

1. 
   Выделяем всю область таблицы
   
2. 
   Инструменты для создания графиков расположены на панели меню "Вставка"
   
3. 
   Выбираем соответствующий тип графика
   
   1. 
      Гистограммы
      

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

• 
  Гистограмма с группировкой
  

Используется для сравнения. Ее вид представлен на рисунке 2.


Рисунок 2

• 
  Гистограммы с накоплением
  

Данные отображаются как часть одного целого. Наглядно видно влияние каждого элемента на общий результат. (Рисунок 3)


Рисунок 3

• 
  Нормированная гистограмма с накоплением
  

Отражает данные всех категорий в одном столбце, принимая весь столбец за 100%. Размер каждой категории определяется как вес в общей сумме категорий. (Рисунок 4)


Рисунок 4

2. 
   Графики
   

Графики используются для отражения колебаний и изменений данных во времени. (Рисунок 5)


Рисунок 5

3. 
   Круговые диаграммы
   

Используются для отображения наглядной структуры и пропорций. (Рисунок 6)


Рисунок 6

Настройка параметров
1. Элементы диаграммы

Добавить подписи данных, название осей, изменить погрешность можно в меню управления элементами диаграммы.

Просто ставьте галочку напротив нужных и снимайте с не нужных. (Рисунок 7)

---

Рисунок 7

2. 
   Стили диаграмм
   

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

Можно найти подходящее оформление в меню "Стили диаграмм". (Рисунок 8)


Рисунок 8

3.Фильтры отображаемых данных

Скрыть ненужные данные из вида и изменить/расширить таблицу, на основе которой построен график, можно в меню "Фильтры диаграмм". (Рисунок 9)


Рисунок 9

Постройте все указанные виды диаграмм и настройте их параметры.

Сохранить задание в файл с именем Фамилия_ПР_5_9.xlsx

Задание 10. Сводные таблицы и расчеты.

Исходные данные представлены на рисунке 1.

Стоимость товара в партии рассчитывается умножением значений в полях: Количество*Цена.



Рисунок 1

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

1. 
   На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».
   
2. 
   Далее на экране открывается диалоговое окошко, где требуется указать соответствующие параметры. На этом этапе создания сводных таблиц в Excel есть несколько важных моментов. Если перед тем, как начать формирование реестра, вы установили значок курсора на листе, то заполнение пустых строчек окна произойдёт автоматически. Иначе адрес диапазона данных нужно обозначить самостоятельно. (Рисунок 2)
   

Рисунок 2

3. 
   Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.
   

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

4. 
   Закрепив все настройки, получаем готовую основу. По левую сторону располагается область, где размещена основа будущей рамки. В правой части есть окно с настройками, которое помогает управлять реестром. (Рисунок 3)
   

---

Рисунок 3

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

Вся структура строится таким образом, чтобы текстовые данные играли роль объединяющих элементов, а числовые показывали консолидированные значения. Например, объединим все поступления по отделам и узнаем на какую сумму получил товары каждый. Для этого поставим галочку у двух заголовков: отдел и стоимость товара в партии. (Рисунок 4)


Рисунок 4

Обратите внимание, как расположились эти данные в нижней области панели настройки.

Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец. (Рисунок 5)



Рисунок 5

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

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

Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов - просто наведите мышь, нажмите левую кнопку и тяните. (Рисунок 6)



Рисунок 6

6. 
   Добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой. (выделить форматированием)
   

Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступления», просто поставив около него галочку. (Рисунок 7)



Рисунок 7

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

---