Мерчендайзер, Лида

Описание

1

2

ОГЛАВЛЕНИЕ

Оглавление ................................ ................................ ................................ ................................ .... 2
Знакомство с табличным процессором ................................ ................................ ....................... 4
1. Основные действия в табличном процессоре. ................................ ................................ ....... 6
1. Ввод данных в ячейки ................................ ................................ ................................ .......... 6
2. Техника ввода формул ................................ ................................ ................................ ......... 6
3. Автоматический пересчет адресов при копировании ячеек с формулами. .................... 6
Выполните задания. ................................ ................................ ................................ .............. 7
1. Упражнение ................................ ................................ ................................ ....................... 7
2. Расчѐт учебной нагрузки ................................ ................................ ................................ ...... 8
3. Ремонт помещения ................................ ................................ ................................ ........... 9
2. Абсолютные и относительные адреса ячеек ................................ ................................ ........ 10
Выполните три упражнения на использование абсолютных адресов. .............................. 11
1. Упрощенный расчет зарплаты ................................ ................................ ....................... 11
2. Оплата коммунальных услуг ................................ ................................ ......................... 12
3. Учет расходов на интернет ................................ ................................ ............................ 13
3. Форматы данных ................................ ................................ ................................ ..................... 15
Задачи на форматирование числовых данных ................................ ................................ ..... 15
4. П остроение графиков ................................ ................................ ................................ ............. 19
Задания. ................................ ................................ ................................ ................................ .... 19
5. Конструирование таблиц. сортировка и фильтрация данных ................................ ............ 26
Задание 1 ................................ ................................ ................................ ................................ .. 26
Сортировка данных в таблицах ................................ ................................ ............................ 27
Фильтрация данных ................................ ................................ ................................ ................ 28
Задание на использование фильтра ................................ ................................ ...................... 29
6. Большие таблицы ................................ ................................ ................................ ................ 35
Работа с листом 1 ................................ ................................ ................................ .................... 35
Задание 2 ................................ ................................ ................................ ................................ .. 37
Задание 3 ................................ ................................ ................................ ................................ .. 37
7. Математические и статистические Функции в EXCEL ................................ ...................... 39
Задание 1. Математические функции. ................................ ................................ .................. 41
Задание 2. Статистические функции. ................................ ................................ ................... 41
8. Функции даты и времени в EXCEL ................................ ................................ ...................... 43
Задание ................................ ................................ ................................ ................................ ..... 43
9. Логические Функции в EXCEL ................................ ................................ ............................. 45

3

Логические функции ................................ ................................ ................................ .............. 45
Задание на логические функции ................................ ................................ ....................... 45
10. Задание для самостоятельного выполнения (логические функции) ................................ 47

4

ЗНАКОМСТВО С ТАБЛИЧН ЫМ ПРОЦЕССОРОМ
Согласно учебному плану на изучение табличного процессора отводится 16 занятий. В
том числе

Номер
занятия
Тема
1 Редактор электронных таблиц Microsoft Office Excel.
Интерфейс программы. Элементы окна программы: меню, панели инструментов, строка
формул. Рабочие листы Excel , работа с листами: вставка, удаление, перемещение,
переименование.
Элементы таблицы на листе: ячейки, строки, столбцы, диапазоны. Приемы выделения
строк, столбцов, диапазонов. Множественное выделение.
Основные действия с ячейками, строками, столбцами и диапазонами. Копирование,
перемещение, вставка, удаление, очистка. Объединение ячеек. Работа с буфером
обмена.
2 Редактор электронных таблиц Microsoft Office Excel.
Установка параметров страниц книги. Установка колонтитулов, принудительные
разрывы страни ц при печати. Проверка орфографии в Microsoft Excel .
Отображение и скрытие строк и столбцов книги. Изменение ширины столбцов и высоты
строк. Удаление и добавление строк и столбцов. Закрепление области.
3 Форматы данных. Формулы и математические вычисления в Excel.
Типы данных: текст, число, формула. Ввод, редактирование и форматирование текста.
Числовые данные: ввод, редактирование. Форматы числовых данных (процентный, де -
нежный, финансовый, дробный). Автозаполнение числовых данных, автосуммирова ние.
4 Форматы данных. Формулы и математические вычисления в Excel.
Формула как математическое выражение из констант, операторов, ссылок на адреса
ячеек. Правила записи формул. Адреса ячеек (ссылки). Автоматический пересчет
ссылок при копировании и перем ещении содержимого ячеек.
5 Форматы данных. Формулы и математические вычисления в Excel.
Относительные, абсолютные и смешанные ссылки.
6 Форматы данных. Формулы и математические вычисления в Excel.
Вычисление доли числа в процентах. Вычисление изменени я числовой величины в
процентном соотношении, нахождение доли числа с коэффициентом нормировки.
7 Функции математической обработки данных.
Функции в Excel. Классификация функций. Синтаксис функций, имя, аргументы функций
и возвращаемое значение. Использование Мастера функций.

8 Функции математической обработки данных.
Математические и статистические функции. Вычисление минимального, максимального
и средне го значений. Подсчет количества непустых ячеек в заданном диапазоне.
Подсчет количества ячеек, удовлетворяющих критерию.
9 Функции математической обработки данных.
Логические функции. Запись и применение логических выражений.
10 Функции математической обработки данных.
Функции даты и времени. Форматы ввода и вывода даты и времени. Арифметические
действия над датами. Системные даты.

11 Функции математической обработки данных.
Функции работы с текстом
12 Функции математической обработки данных.
Финансовые функции.
13 Функции математической обработки данных.
Вложенные функции.

5

14 Графическое представление числовых массивов в Microsoft Excel.
Назначение диаграмм. Создание диаграмм.
Элементы диаграмм: ряды данных, категории, метки и подписи, легенда.
15 Графическое представление числовых массивов в Microsoft Excel.
Мастер диаграмм. Редактирование диаграмм, добавление и удаление рядов данных.
Форматирование диаграмм.
16 Простые та бличные базы данных в Excel.
Понятие поля и записи. Сортировка записей. Первичная и вторичная сортировка.
Понятие фильтра.

6

1. ОСНОВНЫЕ ДЕЙСТВИЯ В ТАБЛИЧНОМ ПРОЦЕССОРЕ .
1. Ввод данных в ячейки
Данные в любой ячейке могут относиться к одному из следующих типов:
число
текст
формула
Числовой тип. Чтобы содержимое ячейки идентифицировалось программой как число, оно
должно содержать только цифры, запятую (знак десятичной дроби) или знак минус (для
отрицательного числа). Не допускаются посторонние сим волы и пробелы. Над числами могут
производиться математические действия.
Текстовый тип. Последовательность любых символов
Формулы. Вводятся по следующим правилам.
Формула должна начинаться со знака равенства.
Операндами в формулах являются адреса ячеек. ч исла и функции.
Нельзя опускать знаки математических действий, не допускаются пробелы и посторонние
символы.
Порядок действий традиционный (сначала - умножение и деление, затем - сложение и
вычитание) Порядок действий может быть изменен с помощью круглых с кобок. Число
открывающих скобок должно быть равным числу закрывающих.
Пример формулы 1
=А5+2*(В5+С5 ^2)
Содержимое ячейки А5 складывается с удвоенной суммой: содержимое В5 плюс квадрат
содержимого С5 .
.Пример формулы 2
=(А9/В8+(В8+С9)/2)*А10
В данном примере показано, что скобки могут быть вложенными.
Результат вычисления отобразится сразу же после завершения ввода в той ячейке, в
которую введена формула.
2. Техника ввода формул
Адреса ячеек в формулах можно набирать на клавиатуре, а можно у казывать на них
щелчком левой кнопки мыши.
Если адрес набирается на клавиатуре, то раскладка клавиатуры должна быть
латинской. Регистр значения не имеет.
3. Автоматический пересчет адресов при копировании ячеек с
формулами.
Если ячейка с формулой копируется, то по умолчанию программа производит пересчет
адресов в формулах. Приведем пример расчета стоимости закупки.

7


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

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

Выполните задания.
Создайте в вашей папке файл Microsoft Excel и присвойте ему имя, совпадающее с
вашей фамилией. В этом файле вы будете выполнять все задания в этом семестре.
На каждом занятии вы будете работать на отдельном листе.

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

8


Возведение в степень производится с помощью знака ^. Например: возвести в третью
степень содержимое ячейки А5 → (А5) ^3 .
Проследите, как меняется точность представления чисел (количество десятичных знаков) с
помощью кнопок "разрядность" .



2. Расчёт учебной нагрузки
Вам надо рассчитать учебную нагрузку, которая складывается из лекционных,
практических занятий, зачетов и консультаций. Заготовьте таблицу по следующему образцу и
заполните в ней четыре строки. У каждого разное число групп. Лекции каждый преподаватель
читает всему своему потоку.

9


После того, как таблица будет заполнена, выровняйте значения в ячейках по центру. В
клетке с бежевой заливкой подсчитайте итог с помощью автосуммы.
3. Ремонт помещения
Вы планируете проведен ие ремонта помещений. В вашем распоряжении 4 комнаты с
разными размерами. Предполагается покрасить стены и застелить полы линолеумом.
Заготовьте таблицу EXCEL по приведенному образцу. Ячейки желтого цвета должны
содержать размеры комнат в метрах. Вводите и х с точностью до 0,1 м. Ячейки голубого цвета
должны содержать ФОРМУЛЫ.
В столбцы "площадь стен" и "площадь пола" введите формулы, которые выведите
самостоятельно.
Расход краски рассчитайте, исходя из нормы: 0,8 кг краски на 1 кв. м поверхности.
Стоимость краски: 75 руб. за 1 кг .
Стоимость линолеума: 340 руб. за 1 кв. м
В ячейки бежевого цвета введите итоговые значения, которые получите с помощью
автосуммирования.

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

10

2. АБСОЛЮТНЫЕ И ОТНО СИТЕЛЬНЫЕ АДРЕСА ЯЧЕ ЕК
Иногда от автоматического пересчета адресов при копировании формул приходится
отказываться.
Рассм отрим простой пример. Требуется подсчитать общую стоимость трех видов товаров,
которых должно быть закуплено, например, 8 комплектов.


Формулы для решения этой задачи показаны ниже.

В формулах в столбце D присутствуют три сомножителя, у двух из которых адреса должны
пересчитаться, а у третьего - остаться неизменным. Это означает, что в таком виде формула не
может быть скопирована на соседние ячейки, т.к. в этом случае пересчитается и адрес B3.
Чтобы иметь возможность копировать формулы такого рода, необходимо сообщить
программе, что некоторый адрес должен оставаться неизменным. Это делается с помощью
использования абсолютной адресации. Если в ячейку D7 ввести адрес в виде $B$3 , то знаки
доллара покажут, чт о данный адрес остается неизменным, и теперь эту формулу можно
копировать.

11


Используется также смешанная адресация: например, $B3 или B$3 .
Чтобы превратить относительный адрес в абсолютный, необходимо при ссылке на ячейку
нажать клавишу F4 ,
Выполнит е три упражнения на использование абсолютных адресов.

1. Упрощенный расчет зарплаты
Вам предстоит составить ведомость на получение зарплаты.
Создайте таблицу EXCEL по приведенному образцу. Данные в ячейках вашей таблицы
могут отличаться.
"К -во рабочих дней" - данные в ячейке равны числу рабочих дней в месяце, при
пятидневной рабочей неделе.
Столбец "Дней" содержит число рабочих дней, которые отработал сотрудник в текущем
месяце. У некоторых сотрудников оно может быть меньше числа рабочих дней в месяце и з-за
болезней и пр.
Всего в данном месяце был, например, 21 рабочий день (это значение занесено в ячейку
Е9 ).

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

12

начислен о = (оклад + оклад×надбавка)×(дней)/(к -во рабочих дней).
Обратите внимание: ссылка на ячейку Е9 в знаменателе должна быть абсолютной.
налог = начислено× подоходный налог
(ставка подоходного налога содержится в ячейке Е10 ; ссылка на нее также абсолютная).
к выдаче = начислено – налог
В ячейке I19 должна быть общая сумма к выдаче по данной ведомости (находится
автосуммой).
После того, как таблица будет заполнена, выровняйте значения в ячейках по центру, в
столбце "фамилия" по левому краю. В столбцах с расч етными формулами установите точность до
2 знаков после запятой.

2. Оплата коммунальных услуг
Стоимость оплаты жилья и коммунальных услуг имеет стойкую тенденцию к повышению.
Вам предоставляется возможность проконтролировать сумму оплаты услуг ЖКХ, внес енную в
2012 году. Все цифры подлинные.
Заготовьте таблицу EXCEL по приведенному образцу.

В шапке таблицы используется объединение ячеек. Чтобы объединить несколько ячеек, их
предварительно надо выделить и щелкнуть по кнопке

13

на вкладке Главная.
В ячейки с голубой заливкой должны быть введены формулы. Ссылки в формулах на
ячейки H5 (к-во проживающих) и H6 (площадь в кв. м) должны быть АБСОЛЮТНЫМИ .
Вводите формулы, исходя из следующих соображений.
1. Содержание жилья . Тариф умножить на площадь кв артиры ( ссылка на площадь -
абсолютная ).
2. Горячая вода . Тариф за 1 кв.м. умножить на норматив на 1 чел. и умножить на
количество проживающих ( последняя ссылка абсолютная)
3. Отопление . Тариф умножить на площадь квартиры ( последняя ссылка - абсолютная ).
4. Электроэнергия . Тариф умножить на количество киловатт. Значения киловатт примите
произвольными в интервале 100 - 200.
5. Газоснабжение . Тариф умножить на норматив и количество проживающих ( последняя
ссылка - абсолютная ).
При создании таблицы не забывайте про копирование формул и автозаполнение ячеек.
Исходные данные будут зависеть от номера вашего компьютера.

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

14


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

15

3. ФОРМАТЫ ДАННЫХ
Форматирование текста в ячейках таблицы EXCEL многом сходно с форматированием в
текстовом проце ссоре WORD . Одинаково выполняются в этих двух программах: выравнивание
текста, смена шрифта, его стиля, размера и цвета.
Познакомимся с форматированием числовых данных . Напомним, что основное отличие
между текстом и числовыми данными в EXCEL заключается в том, что над последними можно
выполнять математические действия.
Формат числовых данных для конкретной ячейки или для диапазона можно установить в
диалоговом окне Формат ячеек на вкладке Число . В это окно можно попасть через горизонтальное
меню ( Формат - Ячейки) или контекстное меню (щелчок правой кнопкой мыши и Формат ячеек ).
Среди предлагаемых числовых форматов следующие: общий, числовой денежный,
финансовый, дата, время, процентный, дробный, экспоненциальный, текстовый и др. В окне
Фор мат ячеек приведены особенности этих форматов и область их применения.


Задачи на форматирование числовых данных
Набирайте по приведенным образцам небольшие таблицы.
1. Числовой формат устанавливается автоматически, если при вводе числа не сделано
ошибок. При вводе чисел должны использоваться только: цифры, запятая как разделитель целой и
дробной части, знак минус (или плюс). Других символов быть не должно!
Изменять формат числовых дан ных можно с помощью следующих кнопок:

16


На рисунке показан пример: результат применения этих команд к числу 23450,45 .
2. Процентный формат. Если в ячейке присутствует знак % , он устанавливается
автоматически.
Действия с процентами
1. Найти заданное значе ние процентов.
Пример . Сколько процентов составляет 25 от 80?
25 делим на 80 и результату назначаем процентный формат.

2. Найти заданный процент от числа.
Пример. Найти 30% от 80.
80 умножаем на 30%
Никаких делений и умн ожений на 100 не делать!

17


3. Дробный формат. Используется для действий с простыми дробями. При вводе простой
дроби вводят целую часть. затем пробел затем числитель и знаменатель через / . Примеры: 2 1/2
120 3/8 . Если дробь меньше единицы (например, одна треть), то вводить следует так: 0 1/3.

4. Денежный и финансовый формат предусматривает число и знак денежной единицы.
Самостоятельно выясните разницу между денежным и финансовым форматами.
5. Экспоненциальный (научный) формат используется для очень больших или очень малых
чисел. Примеры экспоненциального формата:
В Excel - 2,45Е -08 . Это число - 2,45х10 -8 = 0,0000000245
В Excel - 1,59Е+12 Это число - 1,59х1012 = 1590000000000
6. Дата и время . Это специфический тип данных в электронных таблицах. Над датами и
временем можно производить арифметические действия: их можно складывать и вычитать. Для
дат следует различать формат ввода и формат вывода.
Например, дату 31 декабря 2004 г. можно ввести одним из следующих способов:
31/12/04 или 31/12 или 31/12/2004 или
31.12.04 или 31.12 или 31.12.2004 или 31 -12 -04 или 31 -12 или 31 -12 -2004
Если год опущен, то он берется из внутреннего календаря компьютера. Если год
обозначается двумя последними цифрами, то цифры от 00 до 29 относятся к годам с 2000 по 2029.
Формат вывода даты выбирается в диалоговом окне Формат ячей ки .
При вводе времени часы и минуты разделяются двоеточием. Например, 22:45

18

Совместный ввод даты и времени: 15/06/04 12:30 (Дата и время разделяются пробелами).
Даты можно складывать и вычитать потому, что в табличных процессорах каждой дате
соответст вует системная дата (фактически номер дня). Нумерация ведѐтся от 1 января 1900 года.
Например, 1 января 2000 года соответствует системная дата 36526. Время описывается дробной
частью системной даты. попробуем прочитать системную дату 36536,5. Это 12 часов 00 минут 11
января 2000 года.
При математических действиях с датами помните следующее. Если первый и второй
операнд имеют формат даты, то и результат будет выведем в виде даты.. Чтобы перевести
результат в количество дней, необходимо результату зада ть числовой формат.

19

4. ПОСТРОЕНИЕ ГРАФИК ОВ
Для построения диаграммы проще всего сначала выделить диапазон таблицы, в котором
находятся исходные данные, а затем выбрать тип диаграммы на вкладке Вставка .
.
После этого диаграмма появится, но ее не обходимо отредактировать. Познакомьтесь с
терминами, используемыми табличным процессором.

Редактировать элементы диаграммы удобно, пользуясь кнопками на вкладке Макет ..


Задания.
1. Создайте и заполните в своѐм файле следующую таблицу.
Табл.1.

20


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



2. Заготовьте в файле следующую таблицу
Табл.2.

Вставьте в неѐ следующие данные через буфер обмена.

21

207 229 300 302 315 444 314 387
281 282 321 320 318 406 323 357
251 336 476 479 447 568 493 524
228 219 209 218 226 322 226 216
357 342 360 366 365 422 343 359
314 329 352 379 381 455 369 362
По данным из таблицы постройте график, как показано на рисунке.


3. По данным табл.1 постройте следующий график.

4. Постройте линейчатую диаграмму по данным табл.2.

22


Данные взяты из сборника: Образование в России – 2008. Статистический
бюллетень. – М.: МГУПИ, 2009. – 436 с.

5. В предыдущем задании (" Форматы данных" ) у вас была таблица цены на нефть.
Скопируйте два первых столбца вашей таблицы с предыдущего листа и постройте по этим
данным график.

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

23


Из "Википедии":
Тренд — основная тенденция изменения временного ряда. Тренды могут быть описаны
различными уравнениями — линейными, логарифмическими, степенными и т. д. Фактический тип
тренда устанавливают на основе подбора его функциональной модели статистическими методами
ли бо сглаживанием исходного временного ряда.
Тренд в экономике — направление преимущественного движения показателей.
Добавьте на ваш график два тренда: линейный и экспоненциальный . Для этого на вкладке
Макет в группе Анализ выберите Линия тренда . Выведите на графике уравнения трендов, а
также коэффициент достоверности аппроксимации. Ответьте на вопрос: какой тренд описывает
зависимости более корректно?


6. Постройте график математической функции (на выбор из приведѐнного списка).
Изменяйте х в пределах от 0,5 до 4,5 с шагом 0,5. .

24


Пример графиков функций приведѐн ниже.


7. Во всех рассмотренных выше примерах точки на оси категорий были расположены
равномерно. На практике это выполняется не всегда. Рассмотрим пример. Ведутся нерегулярные
наблюдения за температурой воздуха. Обратите внимание: интервал наблюдений неодинаковый.
Дата =Температура =
12.02 11
14.02 9
15.02 11
18.02 14
20.02 8
21.02 6
22.02 7
Скопируйте данные из таблицы в свою книгу. При построении графика выберем тип Точечная с
гладкими кривыми и маркерами.

25

26

5. КОНСТРУИРОВАНИЕ Т АБЛИЦ. СОРТИРОВКА И
ФИЛЬТРАЦИЯ ДАННЫХ
Основные действия с содержимым ячеек
1. Копирование
2. Перемещение
3. Вставка
4. Удаление
5. Очистка
Удаление - аналог вырезания.
Очистка - аналог стирания резинкой.
Вставка выполняется над столбцами или строками.

Копирование Перемещение
Область применения
команды
1
Выделить → Правка →

Вставить
или Выделить → CTRL+C → CTRL+
V
Выделить → Правка → Вырезать
→ Правка Вставить
или Выделить → CTRL+ X → CTRL+ V
Ячейка в ячейку, ячейка в
диапазон, в несмежные
диапазоны, с листа на
лист, из книги в книгу
2
Выделить → Перетащить левой
кнопкой мыши за границу
выделения с нажатой CTRL
Выделить → Перетащить левой
кнопкой мыши за границу
выделения.
В пределах листа
3
Выделить → Перетащить правой
кнопкой мыши за границу
выделения, затем КОПИРОВАТЬ
Выделить → Перетащить правой
кнопкой мыши за границу
выделения, затем ПЕРЕМЕСТИТЬ
В пределах листа
4
Перетащить за маркер выделения

Копирование на смежные
диапазоны в пределах
листа

Задание 1
Наберите календарь на апрель 2013 г. по образцу.

27

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

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




Таблицу, расположенную ниже, скопируйте целиком и вставьте в ячейки вашей таблицы
без заливки.
60,2 243,6 2270,9
82,4 357 2714,5
59,1 301,2 1888,5
32,9 9 976 1217,1
142,5 17 075,40 2076
302,5 9363,3 13543,3
64,1 551,6 2117
127,7 378 4346

28

В ячейки с голубой заливкой введите формулы для расчета плотности населения и
величины внутреннего валового продукта (ВВП) на душу населения. Формулы получите
самостоятельно. Обращайте внимание на единицы измерения!
Скопируйте таблицу вниз четыре раза. У вас окажется пять копий таблицы.

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

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

29


После включения фильтра в верхней строке таблицы появляются кнопки:

Если критерий сортировки находится, н апример, в столбце "Фамилия" , то щелкаем по
кнопке в соответствующем столбце.

Как видно из рисунка, снимая или устанавливая флажки, можно применить фильтр,
основанный на строгом совпадении или фильтр. использующий операторы "равно" , "не равно" и
т.д.
За дание на использование фильтра
Будем обрабатывать данные о подержанных автомобилях, выставляемых на продажу.
Таблицу, приведенную ниже, выделите, скопируйте и вставьте в ваш файл.
номер Марка цена год объем пробег

30

дв.
1 SUZUKI Гран Витар 640 000 2007 2,00 84 000
2 ВАЗ 21093 124 000 2004 1,50 100 000
3 VOLKSWAGEN Touareg 1 120 000 2008 3,00 67 000
4 CITROEN DS3 486 000 2011 1,40 31 000
5 CHEVROLET Niva 440 000 2012 1,00 5 000
6 BMW X5 620 000 2001 3,00 205 000
7 SKODA Fabia 340 000 2009 1,40 36 600
8 ВАЗ 2114 193 000 2008 1,00 89 500
9 RENAULT Symbol 270 000 2008 1,00 67 000
10 VOLVO S40 428 000 2007 1,60 126 000
11 SUZUKI Grand Vita 675 000 2007 2,00 75 000
12 OPEL Antara 850 000 2012 3,20 41 000
13 ГАЗ 31105 265 000 2009 2,43 49 000
14 CHEVROLET ЛАНОС 165 000 2006 1,00 75 000
15 VOLKSWAGEN Passat CC 980 000 2010 1,80 25 000
16 ВАЗ 2114, 231 000 2010 1,60 28 000
17 RENAULT Logan 295 000 2008 1,00 63 000
18 ВАЗ 2121 122 000 2000 1,00
19 FORD Fiesta, 245 000 2002 1,60 136 700
20 SUBARU Impreza 600 000 2008 1,00 6 000
21 SUZUKI Grand Vita 75 000 2008 2,00 63 000
22 ВАЗ Kalina Sport 330 000 2011 1,60 22 000
23 KIA Spectra, 265 000 2013 1,60 93 000
24 HYUNDAI Accent 275 000 2007 1,50 107 000
25 BMW 318i, 300 000 1999 1,80 226 818
26 ВАЗ 2113, 145 000 2006 1,50 74 682
27 ВАЗ 2115 136 000 2005 1,50 83 000
28 OPEL Vectra 395 000 2007 1,80 90 000
29 ВАЗ 21213 110 000 1999 1,70
30 FORD Focus 390 000 2008 1,40 58 000
31 SUZUKI SX4 525 000 2010 1,60 22 500
32 CHEVROLET Niva 285 000 2007 1,70 59 900
33 CHEVROLET Niva 323 000 2007 1,70 85 000
34 CHEVROLET Niva 370 000 2010 1,70 29 300
35 VOLKSWAGEN Tiguan 820 000 2010 11 000
36 MITSUBISHI Lancer 9 370 000 2006 1,00 71 000
37 FORD Focus 308 000 2006 2,00 99 000
38 OPEL astra 490 000 2008 1,60 40 000
39 ВАЗ 21213 165 000 2005 1,70
40 OPEL Astra 699 000 2010 1,60 33 540
41 HYUNDAI Getz 315 000 2007 1,00 84 000
42 FORD Mondeo 335 000 2006 1,00 75 000
43 RENAULT Megane 2, ... 430 000 2008 1,60 69 650
44 ВАЗ 21099 99 999 2001 1,50 115 000
45 NISSAN Qashqai 695 000 2009 2,00 44 500
46 ВАЗ 2109, 128 000 2002 1,50 110 000
47 ВАЗ 2110, 163 000 2004 1,50 77 000
48 DAEWOO Нексия 141 000 2002 1,50 145 000
49 SKODA ОКТАВИЯ 365 000 2008 1,00 63 000
50 RENAULT Laguna, 369 000 2007 1,90 84 807
51 RENAULT Fluence 598 000 2013 1,60 35 000
52 TOYOTA Land Cruiser 1 280 000 2008 2,70 79 000
53 NISSAN Patrol VI 2 500 000 2011 5,55 39 000
54 MITSUBISHI PAJERO 900 000 2006 3,20 94 000
55 ВАЗ Приора 283 000 2010 47 000
56 ВАЗ 2114 245 000 2011 1,60 20 000

31

57 DAEWOO Matiz 169 000 2008 8,00 56 000
58 MITSUBISHI Lancer 315 000 2005 1,60 135 000
59 VOLKSWAGEN Passat B6 485 000 2006 2,00 151 000
60 AUDI А 6 210 000 2002 2,50 200 000
61 DAEWOO Matiz 180 000 2008 70 000
62 VOLKSWAGEN Crafter 750 000 2006 2,50 86 000
63 MERCEDES B180 830 000 2010 1,70 20 000
64 TOYOTA Land Cruiser 1 850 000 2012 2,70 500
65 VOLKSWAGEN Polo, 340 000 2009 1,40 52 249
66 OPEL Antara 810 000 2011 2,40 41 000
67 ВАЗ Приора 305 000 2011 1,00 26 000
68 OPEL Astra H 335 000 2008 1,00 140 000
69 ВАЗ Калина 170 000 2006 1,60 97 000
70 MITSUBISHI PAJERO 570 000 2004 3,00 150 000
71 FORD Focus 308 000 2006 2,00 99 000
72 ВАЗ 2108 89 000 2001 1,00 1 000
73 FORD Fusion 330 000 2008 100 000
74 PEUGEOT 307 1.6 МТ 215 000 2001 1,60 146 800
75 LAND ROVER Discovery ... 850 000 2006 2,72 75 800
76 KIA Picanto, 330 000 2009 1,10 45 000
77 TOYOTA WISH 4х4 700 000 2007 1,80 109 000
78 HYUNDAI SantaFe 500 000 2008 2,00 108 000
79 RENAULT LAGUNA 2 380 000 2004 2,00 100 000
80 RENAULT Renault Si... 21 000 2004 1,40 140 000
81 FORD Focus 2 375 000 2007 1,80 95 000
82 FORD Focus 2 390 000 2008 1,40 58 000
83 MAZDA CX -7, 750 000 2008 2,30 128 400
84 FORD Focus I 230 000 2003 1,00 190 000
85 TOYOTA Highlander 1 500 000 2011 3,50 54 000
86 MITSUBISHI PAJERO 560 000 2004 3,00 146 000
87 BMW 525i 680 000 2006 2,50 139 000
88 PEUGEOT 407 325 000 2006 2,40 120 000
89 HONDA CIVIC 525 000 2008 1,80 58 000
90 OPEL Astra 685 000 2011 1,60 13 400
91 TOYOTA Camry 495 000 2005 2,40 51 000
92 ВАЗ 2115, 119 000 2001 1,50 89 000
93 NISSAN Micra 390 000 2008 1,20 25 000
94 BMW 320iA 750 000 2007 2,00 49 016
95 FORD Mondeo 375 000 2006
96 SSANG -YONG REXTON 2 730 000 2009 2,70 45 000
97 SKODA YETI 700 000 2010 1,20 40 000
98 ВАЗ 21093 111 000 2003 1,50 125 000
99 TOYOTA AVENSIS 675 000 2009 1,60 66 000
100 TOYOTA Corolla 480 000 2007 1,60 19 000
101 CHERY Фора 248 000 2007 2,00 71 000
102 TOYOTA CAMRY 695 000 2007 2,36 79 000
103 CHEVROLET AVEO 385 000 2010 1,00 46 200
104 MERCEDES С180 AMG S... 1 250 000 2012 1,80 8 000
105 MITSUBISHI Colt 295 000 2006 1,00 59 600
106 PEUGEOT 107 АКПП 320 000 2008 1,00 80 000
107 TOYOTA RAV4 780 000 2008 2,00 48 000
108 MERCEDES E -200, КРЕ... 310 000 1998 2,00 334 486
109 ВАЗ 2114 139 000 2004 1,50 89 000
110 ВАЗ 2114 164 000 2007 1,50 63 000
111 ВАЗ 2115 153 000 2005 84 000
112 DAEWOO Нексия 189 000 2008 83 000
113 ВАЗ 2114 135 000 2004 1,50 111 000

32

114 OPEL Astra 505 000 2009 1,80 50 000
115 FORD fiesta 350 000 2008 1,39 50 000
116 FORD Mondeo 400 000 2007 1,80 63 000
117 TOYOTA Camry 1 075 000 2012 2,50 12 000
118 TOYOTA AVENSIS 460 000 2005 1,80 135 000
119 MITSUBISHI Lancer 315 000 2005 1,00 85 000
120 VOLKSWAGEN Passat CC 875 000 2009 1,80 72 000
121 OPEL Aстра 520 000 2010 1,60 35 000
122 FIAT albea 290 000 2008 1,40 74 000
123 MERCEDES c 220 cdi 420 000 2000 2,20 268 000
124 FORD Focus 3 530 000 2009 2,00 53 000
125 BMW X3 770 000 2004 170 000
126 ВАЗ 2115, 125 000 2001 1,50 89 000
127 TOYOTA IQ 390 000 2009 1,00 31 400
128 SSANG -YONG REXTON 630 000 2006 2,70 101 000
129 PEUGEOT 407 2.4AT 830 000 2009 2,40 41 000
130 PEUGEOT 107 АКПП 310 000 2008 1,00 80 000
131 FORD 375 000 2006
132 ВАЗ 2110 155 000 2005 1,60 58 000
133 MITSUBISHI PAJERO 355 000 1996 3,00 250 000
134 HYUNDAI Accent 299 000 2009 1,00 80 000
135 VOLKSWAGEN Passat B6 ... 720 000 2010 1,80 56 000
136 HYUNDAI Getz 280 000 2006 1,40 90 000
137 ГАЗ 3110 108 000 2003 91 000
138 BMW X6 xDrive ... 2 230 000 2008 3,00 43 000
139 ВАЗ Калина 200 000 2010
140 MITSUBISHI LANCER X 447 000 2008 1,50 55 000
141 SUZUKI Grand Vita 640 000 2007 2,00 90 000
142 SUZUKI Гран Витар 620 000 2007 2,00 84 000
143 CHERY фора 250 000 2007 73 000
144 HYUNDAI SantaFe 595 000 2008 2,70 92 000
145 ВАЗ 2110, 88 000 1999 1,50 132 000
146 MITSUBISHI Lancer 450 000 2007 1,60 56 000
147 ВАЗ 2112 193 000 2006 74 000
148 HYUNDAI i30 385 000 2008 1,40 56 000
149 MITSUBISHI LANCER X 485 000 2008 1,80 56 700
150 ISUZU трупер 135 000 1985 2,00 241 000
151 KIA SOUL 620 000 2009 1,60
152 TOYOTA Land Cruiser 915 000 2004 2,70 153 000
153 DAEWOO Matiz 160 000 2010 43 000
154 TOYOTA Land Cruiser 2 675 000 2011 4,46 13 100
155 ГАЗ 3110 120 000 2002 2,28 98 000
156 KIA РИО седан 380 000 2010 1,00 28 000
157 RENAULT SANDERO 410 000 2010 1,00 7 500
158 CHEVROLET Niva 254 000 2005 1,00 66 800
159 SKODA Fabia 341 2008 1,20 97 000
160 FORD Transit 380 000 2001 208 000
161 HYUNDAI Accent 270 000 2008 1,60 78 000
162 MITSUBISHI Оутлендер ... 820 000 2011 2,00 28 000
163 DAEWOO Matiz 225 000 2012 8,00 3 000
164 MITSUBISHI Outlander ... 575 000 2006 2,40 82 500
165 SUZUKI Liana 390 000 2005 1,60 102 000
166 TAGER Camry 495 000 2005 2,40 51 000
167 SUZUKI Jimny 690 000 2011 1,33 4 500
168 ВАЗ 2110 118 000 2002 1,50 120 000
169 ВАЗ 2112 125 000 2001 1,60 98 000
170 HYUNDAI SOLARIS 480 000 2011 1,59 44 000

33

171 ВАЗ Priora 200 000 2008 66 000
172 ВАЗ 21214 278 000 2011 1,70 14 500
173 CHEVROLET Niva 230 000 2004 1,70 83 000
174 SUZUKI SX4 GL 2wd 475 000 2010 1,60 77 000
175 ВАЗ Приора 310 000 2010 1,00 27 000
176 VOLKSWAGEN Passat 530 000 2006 2,00 85 000
177 CHEVROLET Niva 247 000 2005 1,70 76 000
178 CHEVROLET Captiva 760 000 2008 3,00 65 000
179 NISSAN Qashqai 680 000 2008 2,00 80 000
180 NISSAN X -TRAIL 570 000 2007 2,00 106 000
181 ВАЗ 21093 75 000 2003 1,00 56 000
182 VOLKSWAGEN Golf V 525 000 2008 1,60 127 000
183 BMW X6 3.5i xD... 2 150 000 2009 3,00 87 000
184 ВАЗ Калина 210 000 2008 1,60 74 000
185 RENAULT MEGANE 3 420 000 2010 1,50 50 000
186 MITSUBISHI Outlander,... 790 000 2008 3,00 89 000
187 HYUNDAI TUCSON 650 000 2006 2,40 94 350
188 TOYOTA Yaris(P2)1... 395 000 2007 1,30 46 200
189 ВАЗ Калина 280 000 2011 1,60 41 000
190 DAEWOO Matiz 230 000 2010 36 000
191 ВАЗ Приора 278 000 2011 1,60 24 000
192 CHEVROLET Niva 325 000 2006
193 RENAULT Logan 315 000 2009 1,60 60 600
194 ВАЗ 2121 118 000 2000
195 NISSAN ALMERA 255 000 2005 1,50 96 000
196 ВАЗ Приора 260 000 2010 1,60
197 CHERY АМУЛЕТ 177 000 2008 1,00 55 000
198 AUDI 100 Avant 185 000 1992 2,00 1 700 000
199 ВАЗ Калина 225 000 2009 1,60 26 250
200 VOLKSWAGEN Golf V 470 000 2008 99 000
201 SKODA Fabia 365 000 2007 1,00 48 000
202 OPEL Vectra 395 000 2007 1,80 90 000
203 KIA Ceed 440 000 2009 1,60 34 800
204 TOYOTA Corolla, 438 000 2007 1,60 78 000
205 TOYOTA Prius 555 000 2008 1,50 74 000
206 ВАЗ 21214 НИВА 175 000 2005 1,70 63 000
207 HYUNDAI SOLARIS 560 000 2012 1,60
208 ВАЗ 2110 135 000 2002 169 000
209 TOYOTA Land Cruiser 1 720 000 2007 4,70 130 000
Задание выполнить следующим образом. Применяя к этой таблице фильтры, ответить на
приведенные ниже вопросы. После каждого применения фильтра, делайте цветную заливку в
столбце справа.

34



1. Выберите автомобили выпуска не ранее 2010 года.
2. Выберите 10 самых до рогих автомобилей, скопируйте этот список на другое место листа
и отсортируйте в порядке убывания стоимости.
3. Выберите автомобили с объемом двигателя 1,70 - 2,00 л.
4. Выберите автомобили ВАЗ, скопируйте их в другое место и отсортируйте по году
выпуска ( сначала новые - затем более старые).
5. Выберите автомобили стоимостью от 400 000 до 600 000 руб.
6. Выберите все автомобили Mitsubishi , а среди них автомобили 2005 и 2006 года выпуска.
7. Выберите автомобиль выпуска 2010 года с минимальным пробегом.
8. Вы берите все автомобили Германии: VOLKSWAGEN, AUDI, OPEL, MERCEDES, BMW.

35

6. БОЛЬШИЕ ТАБЛИЦЫ
В этом задании вам предстоит обработать несколько больших таблиц. Материалом для
них послужили некоторые итоги последней переписи населения.
Откройте файл R: \Fedorov \К заданиям \Excel \Большие таблицы .xlsx . В нем содержится 3
листа. Скопируйте их в ваш файл (команда Правка -Переместить/скопировать лист ).
Работа с листом 1
1. Выделите всю таблицу и примените к ней формат с разделителями, установите также
формат целых чисел (без дробной части). Перед каждым названием федеральных округов вставьте
по две пустых строки. Поместите курсор в пустую ячейку B6 и выполните команду Окно -
Закрепить области . Этим вы закрепили шапку таблицы и первый столбец. Теперь просматривать
таблицу стало удобнее.
2. В столбце А под списком областей каждого округа в пустые ячейки введите Всего по
округу

3. В строках Всего по окр угу просуммируйте данные по всем столбцам, относящиеся к
данному округу.
4. Добавьте к таблице справа 2 столбца: % детей и % пенсионеров . В них введите
оотношение значений в столбцах L и N к значениям в столбце B. Скопируйте формулу на все
строки (кроме пустых строк и строк с названиями округов). Результат выразите в процентах с
точностью до десятых.
5. Внизу таблицы через несколько пустых строк сделайте заголовок По округам и
скопируйте туда суммарные данные по ок ругам.
Обратите внимание! Копировать надо с помощью опции Специальная вставка -
Значения. В противном случае скопируются формулы, произойдет пересчѐт адресов и
результат будет неверным.

36


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