Типы ссылок и копирование данных
Excel в решении задач
Управленческой деятельности
Учебно-методическое пособие
для студентов дневной и заочной форм обучения
специальности 100101 «Cервис»
по дисциплине «Программно-техническое обеспечение
управленческой деятельности»
Шахты 2006
УДК 004.9(075.8)
ББК 32.973.26–018.2я73
Е97
Составитель:
к.э.н., доцент кафедры «Информатика»
И.Б. Кушнир
Рецензенты:
к.т.н., доцент кафедры «Информатика»
И.В. Барилов
к.т.н., доцент кафедры «Информатика»
Д.Н. Галушкин
Е97 Excel для решения задач по управленческой деятельности: учебно-методическое пособие / составитель И.Б. Кушнир. – Шахты: Изд-во ЮРГУЭС, 2006. – 49 с.
В настоящем учебно-методическом пособии приведены разнообразные примеры, демонстрирующие возможности программного обеспечения Excel таких функций управления, как планирование, календарно-плановое руководство, оперативное управление и функции контроля.
Пособие предназначено для студентов дневной и заочной форм обучения специальности 100101 «Cервис» по дисциплине «Программно-техническое обеспечение управленческой деятельности».
УДК 004.9(075.8)
ББК 32.973.26–018.2я73
© Южно-Российский государственный
университет экономики и сервиса, 2006
© И.Б. Кушнир, 2006
Содержание
Введение. 4
1 Составление документа Excel 5
1.1 Основы работы в Excel 5
1.2 Типы ссылок и копирование данных. 6
1.2.1 Относительные ссылки. 6
1.2.2 Абсолютные ссылки. 8
1.2.3 Смешанные ссылки. 8
1.3 Форматирование и оформление данных. 9
1.4 Пример задания. 11
1.5 Задания для самостоятельной работы.. 15
2 Итоговые функции. 18
2.1 Краткие сведения. 18
2.2 Пример задания. 19
2.3 Задания для самостоятельной работы.. 24
3 Дата и время. 28
3.1 Общие сведения о Датах. 28
3.2 Общие сведения о Времени. 29
3.3 Задания для самостоятельной работы.. 30
4 Логические формулы.. 33
4.1 Общие сведения о логических выражениях. 33
4.2 Задания для самостоятельной работы.. 35
5 Условное форматирование. 38
5.1 Краткие сведения. 38
5.2 Задания для самостоятельной работы.. 41
6 Ссылки и массивы.. 42
6.1 Краткие сведения. 42
6.2 Задания для самостоятельной работы.. 44
Библиографический список. 48
Введение
Успех в современном бизнесе во многом опирается на оперативный анализ экономической ситуации. Одним из современных компьютерных средств для решения подобных задач является программа Microsoft Excel. Однако большинство книг, посвященных Excel, описывают лишь инструментальные возможности этой программы, не объясняя, как ими пользоваться при решении конкретных задач.
В настоящем пособии приведены разнообразные примеры, демонстрирующие возможности программного обеспечения Excel таких функций управления, как планирование, календарно-плановое руководство, оперативное управление и функции контроля.
Ввиду того, что таблицы являются наиболее простым способом структурирования и хранения данных, для их компьютерной обработки используются электронные таблицы, в частности Microsoft Excel. Примерами простых таблиц являются различные бланки, ведомости, накладные, результаты измерений, прайс-листы и проч.
Электронная таблица – это интерактивная программа, состоящая из набора строк и столбцов, изображенных на экране в специальном окне. Область, находящаяся на пересечении строки и столбца, называется ячейкой. В ячейке могут храниться число, текст или формула, с помощью которой осуществляются вычисления, относящиеся к одной или нескольким ячейкам. Ячейки можно копировать, перемещать, а также изменять их содержимое. При изменении содержимого ячейки производится автоматический пересчет содержимого всех ячеек, использующих в формулах измененную ячейку. Электронную таблицу можно сохранять в отдельном файле для дальнейшего использования. Создаваемые в Excel файлы называются рабочими книгами, которые состоят из рабочих листов: электронных таблиц, диаграмм и проч. Каждая книга должна содержать рабочие листы с табличными данными, посвященные одной проблеме. Например, книга с финансовыми отчетами за год или книга, содержащая сведения о сбыте продукции и его анализе.
Каждое практическое занятие, рассмотренное в данном пособии, предполагает создание отдельной рабочей книги, на листах которой вы должны решить все предлагаемые задачи.
Работа над пособием предполагает пошаговое выполнение примеров. Этим достигается овладение инструментом и способность его практического применения. У студентов формируется система применения тех или иных инструментальных средств для различных ситуаций.
Составление документа Excel
Цель: овладение навыками форматирования ячеек электронной таблицы и способами их адресации.
Основы работы в Excel
Если пакет Microsoft Office установлен, то запустите Excel: Пуск → Программы → Microsoft Excel. Окно Excel, приведенное на рисунке 1, содержит много элементов, присущих большинству программ в среде Windows.
Рис. 1. Вид окна рабочей книги Excel
Принципы работы с данными в электронной таблице отличаются от подготовки текстовых документов.
Строки электронной таблицы пронумерованы в порядке возрастания, а столбцы поименованы буквами латинского алфавита. Ячейки таблицы организованы в рабочие листы. На рисунке 1 выбран рабочий лист с именем Лист 1. Рабочие листы можно переименовывать, добавлять и удалять. Для чего следует щелкнуть правой кнопкой по закладке листа.
Ячейки таблицы адресуются (именуются) по номеру строки и столбца (как в игре морской бой), например, А1, С7 и т.д. При ссылке на другой лист дополнительно следует указать имя листа, например, Лист2!В5 – это ссылка на ячейку В5 листа 2. При ссылке на другую рабочую книгу, кроме листа, указывается и имя книги, например, [Книга2]Лист1!А3 – это ссылка на ячейку А3 листа 1 книги 2.
Окно Excel (рис. 1) содержит специфические поля и кнопки. На активном рабочем листе (в нашем случае это Лист1) одна ячейка является активной или, другими словами, выделенной. Эта ячейка обрамлена черной рамкой. Поле имени содержит ссылку на выбранную ячейку (здесь В2). Рядом расположена строка формул, помеченная знаком =, которая отображает содержащиеся в текущей ячейке формулы и данные, здесь отображается текст из выбранной ячейки В2.
Приведенный в строке формул знак равно является подсказкой-напоминанием пользователям ВСЕ ФОРМУЛЫ ЗАПИСЫВАЮТСЯ СО ЗНАКОМ = ВНАЧАЛЕ. При этом формулы могут содержать имена (адреса, ссылки) других ячеек. При этом результат вычисления будет показан в ячейке, а сама формула – в строке формул.
Относительные ссылки
Рассмотрим пример. В ячейках столбца С требуется найти сумму ячеек столбцов А и В. Для этого введите в ячейку С1 формулу, как показано на рисунке 2, и нажмите клавишу [Enter].
Рис. 2. Ввод формулы с относительными ссылками
Введенная формула показывает, что необходимо относительнотекущей ячейки (ячейки С1) сложить значения, содержащиеся в двух соседних слева ячейках (ячейки А1 и В1).
Для того чтобы в ячейке С2 получить сумму ячеек А2 и В2, достаточно просто скопировать формулу из ячейки С1 в ячейку С2. Копирование можно выполнить двумя способами: через буфер обмена и перетаскиванием за правый нижний угол ячейки С1 (указатель мыши при этом должен измениться на ┼). Поскольку в ячейке С1 формула с относительными адресами, то в ячейку С2 скопируется формула =А2+В2 (рис. 3).
Рис. 3. Копирование относительной ссылки по столбцу вниз
Итак, на рисунке 3 показано, что относительные адреса при копировании корректируются.
Для закрепления материала скопируйте формулу из ячейки С1 в ячейку D1 (рис. 4).
Рис. 4. Копирование относительной ссылки по строке влево
Как видно из рисунка 4, в ячейке D1 при копировании получена формула =В1+С1, т.е. сумма двух соседних слева ячеек относительно активной ячейки D1.
Абсолютные ссылки
Теперь введите в ячейку С1 формулу с абсолютными адресами. Для автоматизации ввода знаков $ воспользуйтесь клавишей [F4]. Затем скопируйте формулу из ячейки С1 в ячейку С2, а также в любую другую ячейку рабочего листа (рис. 5).
Рис. 5. Абсолютная ссылка
Как видно из рисунка 5, при копировании во всех ячейках будет содержаться одна и та же формула, что и в ячейке С1, т.е. адреса не изменятся, они будут абсолютно закреплены за одними и теми же ячейками.
Смешанные ссылки
Вернемся к расчетам, приведенным на рисунке 4. Для того чтобы при копировании формулы вправо по строке в последующих ячейках появлялась сумма первых двух столбцов, необходимо в формуле использовать смешанные ссылки. Для этого следует ответить на вопрос: «Что не будет меняться при копировании: строка или столбец?» В нашем примере – столбец. Перед именем столбца поставьте знак $. Теперь формула примет вид =$A1+$B1. Затем скопируйте формулу из ячейки С1 в ячейку С,2 а также в любую другую ячейку рабочего листа (рис. 6).
Рис. 6. Смешанная ссылка
Следует отметить, что рассмотренный пример (рис. 6) носит учебный характер, демонстрируя поведение смешанных ссылок при копировании.
1.3 Форматирование и оформление данных
Форматирование ячейки – установка правил ввода и отображения данных. Форматировать ячейку, выделенную группу ячеек, группу строк, столбцов или целый лист можно через меню Формат и командой Формат ячеек… выпадающего контекстного меню. В любом случае откроется окно Формат ячеек (рис. 7), в котором можно настроить варианты отображения содержимого ячейки.
Рис. 7. Формат типов данных ячеек
В Excel существует понятие типа данных ячейки. Формат данных распознается автоматически при вводе данных. Например, если вы введете дату 15.02.2006, то по умолчанию установится формат Дата, если проценты 34 %, то установится процентный формат и т.д. Если формат не удается установить, то устанавливается Общий формат. Для большинства числовых форматов можно выбрать тип и число десятичных знаков дробной части. Здесь следует отметить, что дробные числа вводятся с десятичной запятой, а не точкой.
Например, при вводе числа 1,25 вместо десятичной запятой была введена точка – 1.25. В этом случае Excel автоматически назначит ячейке формат Дата и отобразит введенную информацию как дату - Янв.25. И даже в случае исправления точки на запятую – Excel все равно будет отображать дату, но только Янв.00,что связано с внутренним представлением дат в виде чисел. Для того чтобы избавиться от автоматически «распознанного» формата, следует «вручную» назначить ячейке формат Числовой на вкладке Число диалогового окна Формат ячеек.
По умолчанию текст, введенный в ячейку, выравнивается по ее левому краю, а числа – по правому. Изменить способ выравнивания в ячейке или диапазоне можно с помощью вкладки Выравнивание окна Формат ячеек (рис. 8). В ней располагаются часто необходимые возможности: перенос по словам, объединение ячеек и наклонную ориентацию текста.
Рис. 8. Форматирование выравнивания ячеек
Изменить тип и размер шрифта можно с помощью кнопок, расположенных на панели инструментов Форматирование. Кроме того, вкладка Шрифт диалогового окна Формат ячеек (рис. 9) позволяет установить дополнительные форматы, например верхний или нижний индексы.
Рис. 9. Форматирование шрифтов ячейки
Рамки вокруг выделенной ячейки или диапазона ячеек создаются с помощью кнопки Границы 
на панели инструментов Форматирование. Вкладка Граница диалогового окна Формат ячеек позволяет дополнительно задавать тип и цвет линий оформления (рис. 10).
Рис. 10. Форматирование типов границ
Вкладка Вид позволяет изменить цвет фона активной ячейки или диапазона ячеек.
На вкладке Защита можно запретить/разрешить изменение конкретных ячеек с помощью флага Защищаемая ячейка. После установки/снятия флагов защиты нужных ячеек следует выполнить защиту листа: пункт меню Сервис→Защита→Защитить лист… По умолчанию на листе все флажки Защищаемая ячейка включены, т.е. выполнение пункта меню Сервис→Защита→Защитить лист… приведет к защите всех ячеек.
1.4 Пример задания
Необходимо составить отчетную ведомость о результате работы сети торговых точек за IV квартал, в которой подсчитать:
- суммарную выручку по каждой торговой точке за три месяца;
- суммарную выручку за каждый месяц по всеем торговым точкам;
- долю реализации каждой торговой точки в общем объеме.
Исходные данные приведены в таблице 1.
Таблица 1
Выручка сети торговых точек
| Торговая точка | Октябрь | Ноябрь | Декабрь | Суммарная выручка | Доля |
| Итого |
Введите в ячейку А1 заголовок таблицы «Выручка сети торговых точек».
Выделите диапазон ячеек А2:F2 и выполните команду Формат→Формат ячейки… В появившемся диалоговом окне Формат ячеек перейдите на вкладку Выравнивание и установите в разделе Выравнивание по горизонтали и по вертикали по центру, а также в разделе Отображение отметьте флажком переносить по словам (рис. 11).
Рис. 11. Параметры выравнивания
Последовательно в ячейки А2, В2 … F2 введите заголовки каждого столбца. Обратите внимание, что содержимое ячеек автоматически выравнивается по центру, а для длинных заголовков разбиваются на несколько строк.
Заполните исходными данными диапазон ячеек А3:D6. Затем выделите диапазон и выровняйте содержимое ячеек по центру. Для этого достаточно просто нажать кнопку 
(По центру) на панели инструментов Форматирование.
В ячейку Е3 введите формулу
=СУММ(B3:D3).
Формулу можно ввести «вручную», а можно воспользоваться кнопкой 
(Автосуммирование), расположенной на панели инструментов Стандартная (рис. 12).
Рис. 12. Ввод формулы с помощью кнопки Автосуммирование
Итак, установите табличный курсор на ячейку Е3 и нажмите кнопку . По умолчанию выделится смежный диапазон ячеек, содержащий числовую информацию. В нашем случае это диапазон А3:D3, т.е. суммирование затронет и номер торговой точки. Выделите «вручную» диапазон B3:D3, как показано на рисунке 12, и нажмите клавишу [Enter].
Введенную в ячейку Е3 формулу с помощью маркера заполнения протащите на диапазон Е4:Е6. Маркер заполнения располагается в правом нижнем углу активной ячейки и при «наведении» на него указателя мыши указатель изменяет свой вид с U на É. Итак, вы навели указатель мыши на правый нижний угол ячейки, в которой располагается формула. Указатель изменил свой вид: теперь он выглядит É. Удерживая нажатой левую кнопку мыши, протягивайте пунктирный диапазон до ячейки Е6. Отпустите левую кнопку мыши, и диапазон сразу заполнится «скопированными» формулами.
По аналогии введите в ячейку В7 формулу
=СУММ(B3:B6),
которую протащите на диапазон С7:Е7.
В ячейку F3 введите формулу (рис. 13)
=E3/$E$7,
которую протащите на диапазон F4:F6.
Рис. 13. Ввод формулы, содержащей абсолютную ссылку
Для ввода в формулу абсолютного адреса ячейки достаточно после ввода его относительного адреса нажать клавишу [F4]. Если бы в ячейку F3 была введена формула =E3/E7, то ее протаскивание на ячейки F4:F6 дали бы ошибочный результат.
Присвойте диапазону F3:F6 процентный формат с помощью кнопки 
. Сумма диапазона ячеек F3:F6 должна быть равной 100 %. Введите в ячейку F7 формулу
=СУММ(F3:F6)
Для того чтобы прорисовать границы таблицы, ее следует выделить (диапазон А2: F7) и нажать на панели инструментов Форматирование кнопку 
(Границы), как показано на рисунке 14.
Рис. 14. Форматирование границ таблицы
Для того чтобы поместить заголовок таблицы по центру, следует выделить диапазон ячеек А1:F1 и нажать на панели инструментов Форматирование кнопку 
(Объединить и поместить в центре).
Итак, мы получили расчетную таблицу, представленную на рисунке 15.
Рис. 15. Расчетная таблица
Для того чтобы просмотреть расчетные формулы, следует выполнить команду Сервис→Параметры. На вкладке Общие диалогового окна Параметры в разделе Параметры окна отметьте поле Формулы флажком. Окно рабочего листа примет вид, показанный на рисунке 16.
Рис. 16. Расчетная таблица с «включенными» формулами
Для возврата в прежний режим снимите флажок Формулы на вкладке Общие диалогового окна Параметры.
1.5 Задания для самостоятельной работы
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 10.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
На рабочем листе приведены данные о составе учебной группы. Определить, какую долю (в %) составляют юноши и девушки.
| Количество, чел | Доля, % | |
| Юноши | ||
| Девушки | ||
| Всего |
Задание 2
Имеется список сотрудников фирмы и их окладов. Подготовить лист для расчета премии каждого сотрудника, если премия выплачивается в % от оклада (процент премии указан в ячейке С2).
| % премии | 60 % | |
| Фамилия И.О. | Оклад | Премия |
| Иванов И.И. | ||
| Сидоров А.Р. | ||
| Федоров В.Л. | ||
| Соколов М.Д. |
Задание 3
Известна раскладка на одну порцию плова. Подготовить лист для расчета массы продуктов, необходимых для приготовления заказанного числа порций, которое будет задаваться в отдельной ячейке – В2.
| Число порций | ||
| Продукт | Раскладка на 1 порцию | Необходимое количество продуктов |
| Мясо | ||
| Лук репчатый | ||
| Морковь | ||
| Рис | ||
| Масло растительное |
Задание 4
Подготовить лист для расчета цены каждого из 5 наименований товара с учетом скидки, величина которой указывается в ячейке С2.
| Размер скидки, % | 15 % | ||
| № п/п | Наименование товара | Цена | Цена со скидкой |
| Шуба норковая | |||
| Дубленка женская | |||
| Куртка кожаная | |||
| Костюм кожаный | |||
| Полушубок норковый |
Задание 5
Оклад работников организаций бюджетной сферы определяется по Единой тарифной сетке (ЕТС) следующим образом. Каждому работнику присваивается разряд от 1-го до 18-го, а для каждого разряда устанавливается коэффициент, по которому определяется оклад работника путем умножения коэффициента на минимальный размер оплаты труда (МРОТ). Оформить лист для расчета оклада работника каждого разряда.
| МРОТ: | ||
| Разряд ЕТС | Коэффициент | Оклад |
| 1,11 | ||
| 1,23 | ||
| 1,36 | ||
| 1,51 |
Задание 6
Подготовить лист, с помощью которого можно, задавая показания счетчика электроэнергии, определять ее расход и сумму оплаты. Тариф (стоимость 1 кВт·ч электроэнергии) задается в ячейке С1.
| Тариф | 1,25 | коп/кВт ч | ||
| Месяц | Дата | Показания счетчика | Расход, кВТ ч | Сумма, руб. |
| Январь | 12.01 | |||
| Февраль | 14.02 | |||
| Март | 10.03 | |||
| Апрель | 15.04 |
Задание 7
В таблице приведена цена нескольких наименований товаров. При покупке двух-пяти штук товара цена единицы товара уменьшается на величину (в %), значение которой будет указано в ячейке С24, при покупке более пяти штук – на величину (в %), значение которой будет указано в ячейке D4. Подготовить лист для определения новых цен.
| Цена товаров, со скидкой и без | |||
| Наименование товара | Объем покупок, шт | ||
| 2-5 | Больше 5 | ||
| Скидка, % | нет | 3 % | 5 % |
| Холодильник | |||
| Телевизор | |||
| Пылесос |
Задание 8
Подготовить лист для определения стоимости подписки на 1, 2, … 6 месяцев различных газет и журналов. Исходными данными для расчета являются стоимости подписки каждого издания на 1 месяц, которые будут указываться в ячейках В4:В9.
| Стоимость подписки | ||||||
| Название издания | Количество месяцев | |||||
| Аргументы и факты | ||||||
| Собеседник | ||||||
| Известия | ||||||
| Вокруг света |
Задание 9
Подготовить лист для определения стоимости квартир по заданной цене каждого в условных единицах и в зависимости от курса, который будет указываться в ячейке С1.
| Курс | ||
| Наименование | Цена, у.е. | Цена, руб. |
| 3-х комн., центр | ||
| 2-х комн., евроремонт | ||
| 3-х комн., без ремонта | ||
| 1-комн., перепланир. | ||
| 2-комн., 1-й этаж |
Задание 10
При подаче налоговой декларации необходимо заполнить форму следующего содержания. Значения в последней колонке получить путем копирования (распространения) формулы, введенной в одну ячейку.
| Месяц | Доход (зарплата за месяц) | Общий доход (зарплата с начала года) |
| Январь | ||
| Февраль | ||
| Март | ||
| Апрель | ||
| Май | ||
| Июнь | ||
| Июль | ||
| Август | ||
| Сентябрь | ||
| Октябрь | ||
| Ноябрь | ||
| Декабрь |
Итоговые функции
Цель: практические навыки использования функций Excel для подведения итогов.
Краткие сведения
Некоторые функции Excel возвращают одно значение, в то время как аргументов имеют блок или несколько блоков. Будем называть такие функции ИТОГОВЫМИ. Наиболее часто используемой из таких функций является СУММ. Эта функция как бы подводит итог колонке чисел – отсюда и название для всей группы. Эти функции входят в категории «Статистические» и «Математические».
В таблице 2 перечислены некоторые итоговые функции, которые используются при решении нижеприведенных задач. Подробную информацию о работе функций смотрите в справке Excel.
Таблица 2
Итоговые функции
| Функция | Назначение |
| СУММ | Суммирует все числа в интервале ячеек |
| СЧЕТ | Подсчитывает количество чисел в списке аргументов |
| СУММЕСЛИ | Суммирует ячейки, заданные критерием |
| СЧЕТЕСЛИ | Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию |
| СРЗНАЧ | Возвращает среднее (арифметическое) своих аргументов |
| МАКС | Возвращает наибольшее значение из набора значений |
| МИН | Возвращает наименьшее значение в списке аргументов |
| НАИБОЛЬШИЙ | Возвращает k-ое по величине значение из множества данных. Эта функция позволяет выбрать значение по его относительному местоположению, например, для определения наилучшего, второго или третьего результатов тестирования в баллах |
| НАИМЕНЬШИЙ | Возвращает k-ое наименьшее значение в множестве данных. Эта функция используется для определения значения, занимающего определенное относительное положение в множестве данных |
| СУММПРОИЗВ | Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений |
| РАНГ | Возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.) |
2.2 Пример задания
Необходимо из отчетной ведомости, рассчитанной на первом практическом занятии, по результатам работы сети торговых точек за IV квартал, подсчитать:
- минимальную и максимальную выручку по всем торговым точкам;
- найти тройку лучших и тройку худших реализаций по всем торговым точкам;
- среднюю суммарную выручку по каждой торговой точке за три месяца и распределить их по местам (проранжировать);
- количество торговых точек, в которых сумма реализации превышала среднюю суммарную выручку, по всем торговым точкам.
Минимальная и максимальная выручка
Введите в ячейку А9 и В9 соответственно заголовки: минимум и максимум. Для того чтобы подсчитать минимальное значение в указанном диапазоне, воспользуйтесь мастером функций, который вызывается по нажатию кнопки 
, расположенной в строке формул. В появившемся диалоговом окне, представленном на рисунке 17, в поле Категория укажите соответствующую категорию, в нашем случае Статистические, и выберите нужную функцию из списка, представленного в поле Выберите функцию. Нажмите кнопку ОК.
Рис. 17. Первый шаг мастера функций
На втором шаге мастера задайте аргументы функции. На рисунке 18 в поле Число1 задайте интервал ячеек В3:D6. В нашем случае этот диапазон содержит сведения о всех реализациях за квартал, кроме итоговых значений.
Рис. 18. Второй шаг мастера функций
Нажмите кнопку ОК.
Действуя по аналогии, самостоятельно найдите максимальное значение в том же диапазоне, для чего воспользуйтесь функцией МАКС.
Тройка лучших результатов
В диапазон ячеек С9:С11 проставьте места 1, 2 и 3 соответственно, а в ячейку С12 введите Лучшие. Результаты будем подсчитывать в диапазон ячеек D9:D11.
Для подсчета результатов вызовите мастер функций и выберите в категории Статистические функцию НАИБОЛЬШИЙ и нажмите кнопку ОК На втором шаге задайте аргументы функции (рис. 19).
Рис. 19. Аргументы функции НАИБОЛЬШИЙ
В качестве массива задайте тот же диапазон В3:D6, однако ссылку на него сделаете абсолютную, т.е. нажмите клавишу [F4], для появления абсолютной адресации $В$3:$D$6.
В поле К укажите на ячейку С9, которая содержит искомое место. В нашем примере ячейка С9 содержит 1.
Нажмите кнопку ОК.
Введенную в ячейку D9 функцию с помощью маркера заполнения протащите на диапазон D10:D11. Обратите внимание, что ссылка на диапазон не изменилась, а значение мест «перенастраивалось». Теперь, если Вы введете, например, в ячейку С11 значение 5 и нажмете клавишу [Enter], то значение в ячейке D11 изменится.
Аналогично найдите три самых худших результата, для чего воспользуйтесь функцией НАИМЕНЬШИЙ.
Среднее значение
Введите в ячейку G2 заголовок столбца: Среднее. Для подсчета среднего значения по строке вызовите мастер функций и выберите в категории Статистические функцию СРЗНАЧ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 20).
Рис. 20. Аргументы функции СРЗНАЧ
На рисунке 20 в поле Число1 задайте интервал ячеек В3:D3. В нашем случае этот диапазон содержит сведения о реализациях за квартал по одной торговой точке. Нажмите кнопку ОК.
Введенную в ячейку G3 функцию с помощью маркера заполнения протащите на диапазон G4: G7.
Ранжирование результатов
Введите в ячейку Н2 заголовок столбца: Ранг. Для ранжирования результатов работы торговых точек за квартал вызовите мастер функций и выберите в категории Статистические функцию РАНГ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 21).
Рис. 21. Аргументы функции РАНГ
В поле Число задайте адрес ячейки, для которой определяется ранг. Обязательно ячейка должна быть первой в диапазоне. В нашем случае это ячейка Е3, содержащая суммарную выручку по первой торговой точке.
В поле Ссылка задайте ссылку на диапазон ячеек, внутри которого будем проводить ранжирование. В нашем случае это диапазон $Е$3:$Е$6.
В поле Порядок - число, определяющее способ упорядочения. Введите 0, так как упорядочение будет по убыванию. Для сортировки по возрастанию следует ввести любое ненулевое число.
Нажмите кнопку ОК.
Введенную в ячейку Н3 функцию с помощью маркера заполнения протащите на диапазон Н4:Н6.
Количество реализаций, превышающих среднюю
Сначала в отдельной ячейке подсчитайте среднее значение всех реализаций, воспользовавшись функцией
=СРЗНАЧ(B3:D6).
Затем в мастере функций выберите в категории Статистические функцию СЧЕТЕСЛИ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 22).
Рис. 22. Аргументы функции СЧЕТЕСЛИ
В поле Диапазон задайте диапазон ячеек, в котором нужно подсчитать ячейки. В нашем случае это диапазон B3:D6.
В поле Критерий — критерий отбора. Критерий отбора может содержать ссылку на ячейку, но в этом случае будет проверяться условие равенства. В нашем случае введите критерий >360, где 360 – это среднее значение реализаций.
Нажмите кнопку ОК.
В результате всех расчетов Вы должны получить таблицу, представленную на рисунке 23.
Рис. 23. Результаты расчетов
2.3 Задания для самостоятельной работы
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 15.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
В таблице приведены данные о количестве легковых автомобилей, выпущенных отечественными автомобильными заводами в первом полугодии 2001 года.
| Автозавод | Январь | Февраль | Март | Апрель | Май | Июнь |
| АвтоГАЗ | ||||||
| КамАЗ | ||||||
| АвтоВАЗ | ||||||
| УАЗ | ||||||
| Москвич | ||||||
| Рослада | ||||||
| ТагАЗ |
Определить:
- сколько автомобилей выпускал каждый завод в среднем за 1 месяц и распределить их по местам (проранжировать);
- сколько автомобилей выпускалось в среднем на одном заводе за каждый месяц и распределить их по местам (проранжировать);
- три наибольших и три наименьших значения выпуска автомобилей в первом полугодии.
Задание 2
Заработная плата каждого из 12 работников фирмы за каждый месяц первого квартала записана в таблице
| № п/п | Ф.И.О. | 1 квартал | ||
| Январь | Февраль | Март | ||
| Иванов М.И. | 4050,23 | 4100,33 | 4090,55 | |
| Сидоров К.А. | 5020,60 | 5070,88 | 5103,55 | |
| … | ||||
| Вешкин А.А. | 4068,80 | 4104,88 | 4203,85 |
Определить:
- сколько в среднем получал каждый работник за один месяц;
- среднюю заработную плату на одного работника;
- среднемесячную зарплату на одного сотрудника в месяц;
- максимальную и минимальную зарплату.
Задание 3
В автомобиль загружаются грузы пяти видов. О каждом виде груза известны масса 1 штуки и количество штук. Подготовить таблицу следующего вида
| Груз | Количество мешков | Масса 1 шт |
| Сахар | ||
| Соль | ||
| Мука | ||
| ИТОГО |
Определить общий вес перевозимых грузов. При решении задачи использовать функцию СУММПРОИЗВ.
Задание 4
На предприятии зарплата каждому сотруднику рассчитывается пропорционально количеству отработанных за месяц часов. Подготовить таблицу следующего вида
| Ф.И.О. сотрудника | Отработано часов | Стоимость 1 часа |
| Иванов И.И. | 2,33 руб. | |
Петров С.П. Наши рекомендации |