Размещено на http://www.allbest.ru/

Министерство сельского хозяйства Российской федерации

фгбоу во иркутскИЙ государственнЫЙАГРАРНЫЙ УНИВЕРСИТЕТ ИМЕНИ А.А. ЕЖЕВСКОГО

ИНСТИТУТ ЭКОНОМИКИ, УПРАВЛЕНИЯ И ПРИКЛАДНОЙ ИНФОРМАТИКИ

Кафедра информатики и математического моделирования

Практическое ПОСОБИЕ

для выполнения лабораторных и контрольных работ по дисциплине «Информационные системы в экономике» для УГС направления подготовки 380000- Экономика и управление для студентов заочного обучения

Иркутск 2017

Печатается по решения научно-методического совета Иркутского ГАУ

Рецензенты: д.т.н., профессор, зав. Лабораторией № ИСЭМ СО РАН Зоркальцев В.И.

Основы обработки экономической информации в Excel 2010. Практическое пособие и задания для выполнения лабораторных и контрольных работ для студентов направления 38.04.01 - Экономика / сост. Н.И. Федурина. ИркутскийГАУ. - 2017. - 66 с.

Практическое пособие предназначены для студентов, изучающих работу табличного процессора Excel в рамках курсов «Информационные системы в экономике», «Основы обработки экономической информации», также могут использоваться студентами различных направлений и специальностей, изучающими работу в Office 2010. Пособие содержит методические материалы по работе с табличным процессором Excel пакета MS Office 2010,а также контрольные задания для выполнения лабораторных работ для студентов заочного обучения. Работа подготовлена на кафедре информатики и математического моделирования.

Содержание

• Введение
• Общие указания
• Требования к оформлению работ
• Лабораторная работа 1 Создание и форматирование таблиц
• Лабораторная работа 2 Организация вычислений в табличном процессоре MS Excel 2010
• Лабораторная работа 3 Логические выражения и функции Excel 2010. Условные вычисления
• Лабораторная работа 4 Работа с диаграммами
• Лабораторная работа 5 Текстовые функции, функции даты и времени
• Лабораторная работа 6 Финансовые функции
• Варианты заданий для контрольной работы
• Введение

В связи с увеличением объемов обрабатываемой информации и развитием компьютерного рынка особенно актуальным становится применение пакетов прикладных программ для обработки и анализа экономической информации. Табличный процессор Microsoft Excel 2010 является одним из программно-инструментальных средств, которое может быть применено при решении широкого класса задач финансово-экономического характера. Его используют в своей деятельности экономисты, финансисты, маркетологи, менеджеры и др., поскольку множество задач, стоящих перед ними, носят учетно-аналитический характер и требуют табличной компоновки данных с последующим их анализом, сортировкой, группировкой, подведением итогов, построением диаграмм и графиков.

Целью данного практического пособия является формирование практических навыков использования инструментальных средств табличного процессора Excel для решения экономических задач. Пособие явился основой для выполнения лабораторных занятий и контрольных работ для студентов заочного обучения. Оно состоит их 6 лабораторных работ, которые содержат как подробно разобранные примеры, так и задачи для самостоятельной работы студентов, а также правила оформления для выполнения контрольной работы. Пособие предназначено для студентов, обучающихся по направлениям 38.00.00 - Экономика и управление.

Общие указания

Для более полного, уверенного освоения студентами навыков решения задач по дисциплине «Информационные системы в экономике» предлагаются данное практическое пособие. Автор руководствовался общими целеполагающими принципами изучения данной дисциплины, а также принципом повышения уровня подготовки студентов для понимания значимости использования информационных систем и технологий в экономике.

Приведённое практическое пособие может быть использовано при проведении самостоятельных и контрольных работ, собеседований при сдаче экзамена.

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

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

- решение всех задач и пояснения к ним должны быть достаточно подробными; вычисления и чертежи - полными и аккуратными.

- для удобства рецензирования рекомендуется оставлять поля;

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

Контрольная работа предоставляется методисту заочного обучения не позднее 10 дней до начала сессии. При сдаче экзамена студент должен дать пояснения к выполненным заданиям.

Требования к оформлению работ

Контрольная работа структурно должен состоять из следующих разделов:

- титульный лист (см. приложение 1и 2);

- содержание (оглавление);

- основная часть;

- список использованных источников и литературы;

- приложение (при необходимости).

Основная часть работы обычно состоит из выполненных и описанных лабораторных работ. При этом каждая лабораторная должна быть оформлена отдельным разделом (файлом). Кроме того, контрольная должна содержать варианты и задания для контрольной работы.

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

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

Общие требования к оформлению контрольной работы (реферата)

Работа может быть выполнена на компьютере (размер шрифта 14 пунктов с полуторным междустрочным интервалом) и напечатана на принтере в режиме качественной печати, либо четко написана от руки на одной стороне листа белой бумаги формата А4 (297211 мм) со следующими полями: левое - 30 мм, правое - 15 мм, верхнее - 20 мм, нижнее - 20 мм.

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

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

Заголовки структурных элементов работы («СОДЕРЖАНИЕ», «ВВЕДЕНИЕ», «СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ И ЛИТЕРАТУРЫ») и разделов основной части следует располагать в середине строки, печатать прописными буквами и отделять от текста пустой строкой. Названия подразделов следует начинать с абзацного отступа и печатать строчными буквами (кроме первой прописной).

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

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

Иллюстрации обозначаются словом «Рис.» и нумеруются последовательно арабскими цифрами в пределах главы иливсей работы за исключением иллюстраций, приведенных в приложении. Иллюстрации должны быть расположены так, чтобы их было удобно рассматривать без поворота работы или с поворотом по часовой стрелке. Иллюстрации располагаются после первой ссылки на них. Ссылки на иллюстрации указывают порядковым номером иллюстрации, например, рис. 1.2.

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

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

На все таблицы должны быть ссылки в тексте. При этом слово «Таблица» в тексте пишут полностью, если таблица не имеет номера, и сокращенно - если имеет номер, например,«... в табл.1.2».

В повторных ссылках на таблицы и иллюстрации следует указывать сокращенно слово «смотри», например, см. табл.1.3.

При переносе части таблицы на другую страницу слово «Таблица» и номер ее указывают один раз над первой частью таблицы; над другими частями пишут слово «Продолжение» и указывают номер таблицы, например, Продолжение табл.1.2.

Если цифровые или иные данные в какой-либо строке таблицы не приводят, то в ней ставят прочерк.

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

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

Формулы следует выделять из текста пустыми строками. Если уравнение не помещается в одну строку, оно должно быть перенесено после знака равенства (=) или после знаков плюс (+), минус (-), умножения () и деления (:).

Ссылки на формулы указывают порядковым номером формулы в скобках, например,«...в формуле (2.1)».

Примечания к тексту и таблицам, в которых указывают справочные и поясняющие данные, нумеруют последовательно арабскими цифрами. Если примечаний несколько, то после слова «Примечания» ставят двоеточие.

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

Лабораторная работа 1 Создание и форматирование таблиц

Цель работы:

1. Знакомство с различными способами организации данных

2. Освоение навыков практической работы по созданию, редактированию и форматированию электронных таблиц

3. Выполнение простейших вычислений в таблицах Excel. Знакомство с элементарными функциями.

4. Использование абсолютной и относительной адресации ячеек в формулах.

5. Контроль правильности введенных ранее значений с помощью условного форматирования

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

Таблицы могут иметь весьма сложную структуру с несколькими уровнями записей



Рисунок 1.1 Табличный способ организации данных

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

Создание и форматирование таблицы:

Таблица обычно создается на основе имеющихся на листе данных.

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

2. Нажмите кнопкуФорматировать как таблицу в группе Стили вкладки Главнаяи выберите стиль оформления.

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

В результате будет создана таблица. В каждый столбец автоматически добавляется значок автофильтра. Автоматически будет отображена контекстная вкладка Работа с таблицами/Конструктор (рис.1.2.).

Рисунок 1.2 Контекстная вкладка Работа с таблицами/Конструктор

Функции вставки ячейки, строки, столбца или листа доступны в меню кнопки «Вставить», которая расположена в группе «Ячейки» на вкладке «Главная»

Рисунок 1.3 Меню кнопки Вставить

Для форматирования ячеек, после их выделения, используют вкладки диалогового окна Формат ячеек, а также элементы группы Шрифт вкладки Главная, минипанель инструментов

Рисунок 1.4 Мини-панель инструментов для форматирования

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

Для форматирования заголовка таблицы нужно выделить все ячейки строки с заголовком по ширине таблицы и нажать кнопкуОбъединитьпомещенную на вкладке Главнаяв группе Выравнивание

Рисунок 1.5 Элементы группы Выравнивание на вкладке Главная

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

1. Выделите ячейку, в которую требуется ввести формулу.

2. Введите = (знак равенства).

3. Выделите мышью ячейку, являющуюся аргументом формулы.

4. Введите знак оператора.

5. Выделите мышью ячейку, являющуюся вторым аргументом формулы.

6. При необходимости продолжайте ввод знаков операторов и выделение ячеек.

7. Подтвердите ввод формулы в ячейку: нажмите клавишу EnterилиTabили кнопку Ввод (галочка) в строке формул.

Задание 1. Освоение приемов работы с электронными таблицами.

1. Создайте новую рабочую книгу и сохраните ее в своей папке под именем Tabl1.xlsх.

2. Введите в следующие ячейки указанный текст:

А1 - Показатели производства	В2 - Март
А2 - Квартал	С2 - Июнь
А3 - План	D2 - Сентябрь
А4 - Факт	Е2 - Декабрь
А5 - Процент выполнения	F2 - Всего
В3 - 1000 С3 - 1000	D3 - 1100 Е3 - 1100
В4 - 900 С4 - 1050	D4 - 1200 Е4 - 1000

3. Проведите редактирование данных в ячейках План и Факт - добавьте единицы измерения тыс.руб. Для этого выделите щелчком мыши ячейку А3. Содержимое ячейки появится в строке формул. Поместите мышью текстовый курсор в строку формул после слова «План» и допишите «(тыс. руб.)». Для завершения редактирования нажмите клавишу Enter. Аналогично внесите изменения в ячейку А4.

4. Подсчитайте значения в столбце Всего. Для этого выделите ячей-ку F3, на вкладке Главная в группе Редактирование нажмите кнопку - Автосумма(при этом диапазон ячеек В3:Е3 выделяется мигающей пунктирной линией), нажмите клавишу Enter.

5. В строке 5 «Процент выполнения» подсчитайте отношения План/Факт. Для этого в ячейке В5 задайте частное, набрав: = В4/В3, нажмите клавишу Enter. Установите процентный формат. В ячейке появится результат деления.

6. Скопируйте формулу из ячейки В5 в ячейки С5: F5. Для этого скопируйте формулу из ячейки В5

7. Выделите название таблицы. Для этого покажите ячейку А1, задайте размер шрифта 20, выделите диапазон ячеек А1: F1 и нажмите

8. Выделите созданную таблицу и скопируйте ее ниже на этом же листе.

9. Отформатируйте исходый экземпляр таблицы с помощью автоформатирования. Для этого нажмите кнопкуФорматировать как таблицу в группе Стили вкладки Главная. Выберите стиль оформления таблицы Светлый.

10.. Разными способами отформатируйте исходный экземпляр таблицы, используя соответствующие вкладки для установки выравнивания, шрифтов, границ и т.д.

1 способ «Использование кнопки Формат по образцу группы Буфер -обмена вкладки Главная»: Выделите ячейку, оформление которой требуется копировать. Нажмите кнопку Формат по образцу. После этого кнопка Формат по образцу останется нажатой, а справа от указателя мыши появится значок копирования формата. Выделите ячейку или диапазон ячеек, для которых устанавливается копируемый формат. Если требуется многократно применять оформление выбранной ячейки, то необходимо два раза щелкнуть по кнопке Формат по образцу (щелкать надо быстро, чтобы кнопка осталась нажатой), а затем выделять разные фрагменты. Для окончания копирования формата нажмите клавишу Es cили кнопку Формат по образцу, чтобы она пришла в обычное состояние.

2 способ «Использование автозаполнения»: Копирование автозаполнением используют при копировании оформления одной ячейки на рядом расположенные ячейки.

· Выделите ячейку с копируемым оформлением.

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

· Щелкните по кнопке Параметры автозаполнения в правом нижнем углу области заполнения и выберите команду Заполнить только форматы.

3 способ «Использование буфера обмена». Копирование форматов с использованием буфера обмена обычно используют при копировании оформления на ячейки других листов или книг.

· Ячейку с копируемым форматом скопируйте в буфер обмена.

· Выделите ячейки, на которые копируется оформление.

· Извлеките скопированную ячейку из буфера обмена. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки.

· Щелкните по кнопке Параметры вставки в правом нижнем углу области вставки и выберите команду Только форматы.

4 способ «Использование возможностей специальной вставки». Копирование форматов с использованием специальной вставки чаще всего используют при копировании оформления на ячейки других листов или книг. Этот способ несколько более трудоемок, чем с использованием буфера обмена и обычной вставки. С другой стороны, он более надежен, так как снижает риск вставки не только формата, но и данных из копируемой ячейки.

· Ячейку с копируемым форматом скопируйте в буфер обмена.

· Выделите ячейки, на которые копируется оформление.

· Щелкните по стрелке кнопки Вставить в группе Буфер обмена вкладки Главная и выберите команду Специальная вставка или щелкните по выделенной области правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка.

· В диалоговом окне Специальная вставка установите переключатель форматы. Нажмите кнопку ОК.

Удаление форматирования.

Можно удалить сразу все параметры оформления (числовые форматы, параметры выравнивания, параметры шрифта, заливки, границы и т. д.).

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

· В группе Редактирование вкладки Главная щелкните по кнопке Очистить и выберите команду Очистить форматы.

Рисунок 1.6 Элементы группы Редактирование вкладки Главная

11. Сохраните результаты работы в своей папке.

Задание 2. Вставка и редактирование формул.

1. Создайте новую таблицу «Оборотная ведомость за ноябрь 2013 г.»

2. Предполагая, что в столбцах D, E, F и G могут быть заполнены все ячейки, заполните ячейки столбцов H и I формулами вида: для столбца H: =В+Е- G, для столбца I: =С+ D*Е- F*G. Введенную в первую ячейку столбца формулу, скопируйте в остальные ячейки столбца.

3. Получите в ячейках С8 и I8 суммы по столбцам. 4. Получите в столбце J процентные соотношения остатков к общей сумме. Выделите ячейку J3 и введите в нее формулу = I3/ I8, нажмите клавишу Enter, щелкните снова по ячейке J3, а затем по кнопке %. Для того чтобы правильно скопировать введенную формулу в остальные ячейки столбца J, ее нужно отредактировать: заменить относительный адрес ячейки I8, абсолютным адресом = I3/ $I$8.Замечание. С помощью символа абсолютной адресации $ можно варьировать способ адресации ячеек. Например, $А8 означает, что при переносе формулы будет меняться только адресация строки, а при обозначении А$8 - только адресация столбца.

Отредактировать уже введенную формулу можно одним из следующих способов:

· дважды щелкните мышью по ячейке, чтобы непосредственно в ней начать редактирование;

· выделите ячейку, нажмите клавишу F2 и редактируйте непосредственно в ячейке;

· выделите ячейку и редактируйте ее содержимое в строке ввода.

· 5. Отформатируйте данные на листе как таблицу. Выберите стиль оформления таблицы Средний 9. 6. Сохраните таблицу в своей папке под именем Tabl2.xlsx.

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

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

2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила выделения ячеек, а затем в подчиненном меню выберите условие (рис. 1.7.)

Рисунок 1.7 Выбор правила выделения значений

3. Настройте параметры условия и выберите способ выделения.

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

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

2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила отбора первых и последних значений, а затем в подчиненном меню выберите принцип отбора.

3. Настройте параметры отбора и выберите способ выделения.

Форматирование с использованием гистограммы.

Гистограммы помогают рассмотреть значение в ячейке относительно других ячеек. Длина гистограммы соответствует значению в ячейке. Чем она длиннее - тем выше значение.

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

2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Гистограммы, а затем в подчиненном выберите цветовое оформление. При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмент листа отображается с указанным оформлением.

Форматирование с использованием набора значков.

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

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

2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Наборы значков, а затем в подчиненном выберите набор значков. При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.

Управление правилами условного форматирования.

Правила условного форматирования можно изменять и удалять.

1. Щелкните по кнопке Условное форматирование группы Стили вкладки Главнаяи выберите команду Управление правилами.

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

3. Выделите правило, которое надо изменить и нажмите кнопку Изменить правило.

4. В окне Изменение правила форматирования можно выбрать другой тип правила, изменить условие и параметры форматирования.

Задание 3. Условное форматирование.

Введите в А1 число 1, в блок В1:В15 арифметическую прогрессию 1,2,...15, в С1 введите формулу =$А$1*В1 и скопируйте ее в С2:С15.

Одним из следующих способов скройте (временно удалите из таблицы) столбец В.

1. Выберите строки или столбцы, которые нужно скрыть.

2. В группе Ячейки на вкладке Главнаянажмите кнопку Формат

3. Выполните одно из следующих действий:

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

· В группе Размер ячейки щелкните пункт Высота строки или Ширина столбца, а затем введите «0» в поле Высота строки или Ширина столбца.

4. Можно щелкнуть правой кнопкой строку или столбец (либо несколько выделенных строк или столбцов) и выбрать командуСкрыть.

Требуется:

1. Наложить на С1:С15 условный формат: если число лежит в диапазоне от 10 до 20, то выводится курсивом; если число лежит в диапазоне от 20 до 40, то выводится полужирным шрифтом в рамке; если число больше 40, то выводится красными цифрами на голубом фоне. Вводя в ячейку А1 различные числа, проследите как изменяется формат ячеек в зависимости от выводимого значения.

2. Отобразите скрытый столбец В и скопируйте его значения в ячейки D1:D15, E1:E15, F1:F15, G1:G15.

3. С использованием условного форматирования в столбце D выделите 10 ячеек с максимальными значениями

4. Примените условное форматирование с использованием гистограмм для ячеек столбца Е.

5. Примените условное форматирование с использованием цветовых схем для ячеек столбца F

6. Примените условное форматирование с использованием значков для ячеек столбца G.

Задание 4.Откройте файл Tabl2.xls. 1.

С помощью условного форматирования обеспечьте возможность заливки ячеек поля «Сумма на 1.12.2013»:

- зеленым цветом, если сумма равна нулю;

- красным цветом, если сумма больше нуля;

- желтым цветом, если сумма меньше нуля.

2. Скопируйте таблицу на второй лист. Удалите ранее созданные условия.

3. Обеспечьте возможность заливки зеленым цветом записей, значения в которых отвечают следующему условию: значение в поле «Сумма на 1.11.2013» не меньше среднего значения по данному полю.

4. Выполните пункт 3, учитывая дополнительное условие, что значение в поле «Сумма на 1.12.2013» должно быть положительным.

5. Сохраните выполненное задание.

Задание 5.Построить таблицу расчета размера платы за электроэнергию в течение 12 месяцев по значениям показаний счетчика в конце каждого месяца и стоимости одного киловатт-часа энергии. Числовые данные выбрать самостоятельно. Предусмотреть оформление таблицы.

Задание 6. Для составления налоговой карточки нужно внести в ячейки месячный доход, а строкой ниже вычислить доход по нарастающей с начала года.



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

Лабораторная работа 2 Организация вычислений в табличном процессоре MS Excel 2010

Цель работы:

1. Освоение навыков практической работы по созданию и редактированию формул с использованием мастера функций и кнопки "Сумма".

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

3. Знакомство с особенностями использования некоторых стандартных функций Excel: функций преобразования и округления чисел, статистических функций для расчета средних значений, поиска наибольших и наименьших значений, расчета количества ячеек, выборочного суммирования.

4. Закрепление материала предыдущих лабораторных работ.

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

Рисунок 2.1 Вкладка Формулы

Порядок работы:

1. Выделите ячейку, в которую требуется ввести формулу.

2. Щелкните по кнопке нужной категории функций в группе Библиотека функций и выберите нужную функцию.

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

4. В окне Аргументы функции нажмите кнопку ОК.

Для выбора нужной функции можно использовать Мастер функцийпри работе в любой вкладке (рис. 2.2.).



Рисунок 2.2

Имена функций при создании формул можно вводить с клавиатуры. Для упрощения процесса создания и снижения количества опечаток используйте автозавершение формул:

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

2. Выберите нужную функцию, для чего дважды щелкните по ней мышью.

3. С использованием клавиатуры и мыши введите аргументы функции. Подтвердите ввод формулы.

Для быстрого выполнения некоторых действий с применением функций без запуска мастера функций можно использовать кнопку Сумма. Эта кнопка, помимо группы Библиотека функций вкладки Формулы (там она называется Автосумма), имеется также в группе Редактирование вкладки Главная. Кроме вычисления суммы чисел в ячейках, кнопку Сумма можно использовать при вычислении среднего значения, определения количества числовых значений, нахождения максимального и минимального значений.

Рисунок 2.3 Ввод формулы с использованием автозавершения

В этом случае необходимо щелкнуть по стрелке кнопки и выбрать необходимое действие:

Среднее - расчет среднего арифметического;

Число - определение количества численных значений;

Максимум - нахождение максимального значения;

Минимум - нахождение минимального значения.

Перемещать и копировать ячейки с формулами можно точно так же, как и ячейки с текстовыми или числовыми значениями. Кроме того, при копировании ячеек с формулами можно пользоваться возможностями специальной вставки. Это позволяет копировать только формулу без копирования формата ячейки. При перемещении ячейки с формулой содержащиеся в формуле ссылки не изменяются. При копировании формулы ссылки на ячейки могут изменяться в зависимости от их типа (относительные или абсолютные). По умолчанию ссылки на ячейки в формулах относительные, то есть адрес ячейки определяется на основе расположения этой ячейки относительно ячейки с формулой. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Именно возможность использования относительных ссылок и позволяет копировать формулы. В некоторых случаях использование относительных ссылок недопустимо. Для того чтобы ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка ячейки имеет формат $A$1, где $ - служебный символ, показывающий абсолютную ссылку.

Чтобы ссылка на ячейку была абсолютной, после указания ссылки на ячейку следует нажать клавишу F4. Ссылку можно преобразовать из относительной в абсолютную и при редактировании ячейки с формулой. К заголовкам столбца и строки в адресе ячейки следует добавить служебный символ $. Например, для того чтобы ссылка на ячейку А20 стала абсолютной, необходимо ввести$А$20. Ссылка может быть не только относительной или абсолютной, но и смешанной. Ссылка формата A$1 является относительной по столбцу и абсолютной по строке, т.е. при копировании ячейки с формулой выше или ниже, ссылка изменяться не будет. А при копировании влево или вправо будет изменяться заголовок столбца. Ссылка формата $A1 является относительной по строке и абсолютной по столбцу, т.е. при копировании ячейки с формулой влево или вправо выше или ниже ссылка изменяться не будет. А при копировании выше или ниже будет изменяться заголовок строки. Абсолютную адресацию в формулах обеспечивает также применение имен ячеек и их диапазонов. Например, формула =Март+Апрель+Май предполагает сложение данных, содержащихся в ячейках с соответствующими именами (вне зависимости от того, в каком месте книги находится именованные диапазоны и формула, которая на них ссылается). Имя - слово или строка знаков, представляющих ячейку, диапазон ячеек, формулу или константу. Имена можно использовать в любом листе книги.

Присваивание имени ячейке или диапазону ячеек:

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

2. В группе Определенные имена вкладки Формулы нажмите кнопку

Присвоить имя.

3. В окне Создание имени в поле Имя введите имя ячейки или диапазона (рис. 2.4.).

4. Для задания области действия имени в поле со списком Область выберите Книга или имя листа в книге.

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

Рисунок 2.4 Присваивание имени ячейке

При задании имен следует соблюдать некоторые правила:

· В именах можно использовать только буквы (в том числе русского алфавита), знак подчеркивания, точки и цифры. Имя может содержать до 255 знаков. Имя может состоять из строчных и прописных букв, но Excel их не различает;

· первый знак в имени должен быть буквой или знаком подчеркивания;

· пробелы не допускаются;

· не допускаются имена, которые имеют такой же вид, как и ссылки на ячейки, например Z$100 или R1C1;

· в имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2010 или Год.2010;

· при именовании выделенного диапазона в качестве его имени пользователю по умолчанию предлагается текст из ячейки, расположенной выше диапазона.

Имя можно присвоить формуле или постоянному значению (константе). Например, имя "Скидка", которому присвоено значение 33 процента, можно использовать в любом месте для вычисления цены со скидкой. Для присвоения имени формуле или константе в поле Диапазон окна Создание имени следует ввести знак равенства (=) и формулу или константу (рис. 2.5.).

Рисунок 2.5 Присвоение имени константе

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

Рисунок 2.6 Вставка имени в формулу

Если нужное имя не отображается в списке, выберите командуВставить имена (см. рис. 2.6.), а затем в окне Вставка имени выберите вставляемое имя. Ненужное или ошибочное имя можно удалить:

1. В группе Определенные имена вкладки Формулы нажмите кнопку Диспетчер имен.

2. В окне Диспетчер имен выделите имя и нажните кнопкуУдалить.

Задание 1.

1. Создайте таблицу

2. В ячейке Н4 рассчитайте сумму ячеек С4:G4.

3. В ячейке I4 рассчитайте произведение ячеек В4 и H4. Присвойте формуле имя «Доход».

4. В ячейке C10 с использованием кнопки (Сумма) рассчитайте сумму ячеек C4:C9.

5. В ячейке B10 с использованием кнопки (Сумма) рассчитайте среднее значение ячеек B4:B9.

6. Скопируйте формулу ячейки H4 на ячейки H5:H9.

7. Скопируйте формулу ячейки I4 на ячейки I5:I9.

8. Скопируйте формулу ячейки C10 на ячейки D10:I10.

9. Отредактируйте формулу в ячейке B10: среднее значение должно быть рассчитано для ячеек B4:B7.

10. В ячейке А11 введите текст «курс доллара», а в ячейке А12 укажите значение курса доллара по отношению к рублю на текущую дату.

11. Присвойте ячейке А12 имя «Курс_доллара».

12. В ячейке J4 рассчитайте частное от деления ячейки I4 на ячейку A12 так, чтобы эту формулу можно было копировать на ячейки J5:J10.

13. Удалите из книги имя ячейки Доллар.

14. Сохраните файл под именем «Продажа_компьютеров.xlsx».

Все функции на вкладке «Формулы» распределяются по темам на следующие группы:

«Математические». Предназначены для решения алгебраических задач: функции для округления данных, тригонометрические и т. д.

«Логические». Применяются для решения задач с условиями.

«Финансовые». Применяются для выполнения финансовых расчетов.

«Текстовые». Предназначены для работы с текстовыми значениями

«Даты и времени». Применяются для работы с данными в формате «Дата/время».

«Ссылки и массивы». Предназначены для просмотра информации, хранящейся в больших списках и таблицах.

Приведем обзор некоторых используемых в практикуме функций

Округление чисел особенно часто требуется при денежных расчетах. Например, цену товара в рублях, как правило, нельзя устанавливать с точностью более двух знаков после запятой. Если же в результате вычислений получается большее число десятичных разрядов, требуется округление. В противном случае накапливание тысячных и десятитысячных долей рубля приведет в итоге к ошибкам в вычислениях. Для округления чисел можно использовать целую группу функций. Наиболее часто используют функции ОКРУГЛ,ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ. Синтаксис функции ОКРУГЛ(А;В), где A - округляемое число; В - число знаков после запятой (десятичных разрядов), до которого округляется число. Синтаксис функций ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ точно такой же, что и у функции ОКРУГЛ. Функция ОКРУГЛпри округлении отбрасывает цифры меньшие 5, а цифры большие 5 округляет до следующего разряда. Функция ОКРУГЛВВЕРХ при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ при округлении отбрасывает любые цифры. Эти функции можно использовать и для округления целых разрядов чисел. Для этого необходимо использовать отрицательные значения аргумента В. Для округления чисел в меньшую сторону можно использовать также функцию ОТБР.

Синтаксис функции ОТБР(А;В), где A - округляемое число; В - число знаков после запятой (десятичных разрядов), до которого округляется число. Фактически функция ОТБР отбрасывает лишние знаки, оставляя только количество знаков, указанное в аргументе В. Для округления числа до меньшего целого можно использовать функцию ЦЕЛОЕ(А), где A - округляемое число. Наконец, для округления до ближайшего четного или нечетного числа можно использовать функции ЧЕТН(А) и НЕЧЕТН(А), а для ближайшего кратного большего или меньшего числа - функции ОКРВЕРХ и ОКРВНИЗ. Для преобразования результатов вычислений в положительное число можно использовать функцию ABS(А), гдеА - число, для которого определяется абсолютное значение.

Простое суммирование содержимого заданного интервала ячеек осуществляется функцией СУММ(А), где A - список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Формула =СУММ(В2:В7), тождественна формуле =В2+В3+В4+В5+В6+В7. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

Для умножения используют функцию ПРОИЗВЕД(А), где A - список от 1 до 30 элементов, которые требуется перемножить. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное умножение с использованием оператора умножения(*). Так же как и при использовании функции СУММ, при использовании функции ПРОИЗВЕД добавление ячеек в диапазон перемножения автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон перемножения. Аналогично формула будет изменяться и при уменьшении диапазона.

Задание 2.

1. Создать новую книгу. Сохранить ее на диске в своей папке с именем «Ассортимент».

2. На первом листе книги набрать данные таблицы 1, приведенной ниже



Указание: При вводе данных используйте Выпадающие списки (Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного менюВыбрать из раскрывающегося списка или нажать сочетание клавиш Alt+стрелка вниз).

3. На втором листе текущей рабочей книги создать таблицу 2:

4. Листам дать название «Ксероксы» и «Факсы» соответственно.

5. На листах «Ксероксы» и «Факсы» в столбиках «Цена», используя абсолютную ссылку, создать формулу для вычисления Цены, при условии, что она на 30% больше стоимости.

6. На листах «Ксероксы» и «Факсы» в столбцах «Сумма» создать формулу =количество*цена;

7. На листах «Ксероксы» и «Факсы» отформатировать таблицы:

шрифт - TimesNewRoman; размер 13; задать границы - тонкая пунктирная линия;

в столбцах «Товар», «Модель», «Название» установить выравнивание по левому краю;

в столбцах «Стоимость», «Цена», «Количество», «Сумма» установить выравнивание по центру;

в ячейках, содержащих денежные суммы, установить денежный формат.

8. Над таблицами добавить строку, в столбце А1 набрать «Курс $», в ячейке В1 набрать текущий курс доллара и установить денежный фор-мат. Присвоить этой ячейке имя «Курс_$».

9. к таблицам добавить столбец с названиями «Стоимость в у.е.», где создать формулу с абсолютной ссылкой =D3/Курс_$.

10. В Столбце «Стоимость в у.е.» установить форматирование как в предыдущих столбцах, воспользовавшись кнопкой «Формат по образцу».

11. Над таблицами добавить три строки в первой строке объединить столбцы, которые занимает ваша таблица, и набрать в объединенной ячейке «Ассортимент ксероксов фирмы «Флагман», «Ассортимент факсов фирмы «Флагман».

12. Используя «Буфер обмена», скопировать обе таблицы на третий лист текущей книги и назвать его «Полный ассортимент».

13. На листе «Полный ассортимент», используя Условное форматирование, выделить те суммы, величина которых от 6000 руб. до 450$.

14. В ячейке E2 округлите указанную там цену товара до ближайшего целого. Скопируйте формулу на ячейки Е3:Е22.

15. В основной таблице под столбиками «Цена», «Количество», «Сумма» найти: минимальный элемент, максимальный элемент и среднее значение.

16. Вычислите итоговое значение столбцов Количество и Сумма.

17. Сохраните файл под именем «Ксероксы и факсы».

Лабораторная работа 3 Логические выражения и функции Excel 2010. Условные вычисления

Цель работы:

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

2. Знакомство с дополнительными функциями, которые можно применять для анализа данных с использованием условий. Например, для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, используется функцию СУММЕСЛИ. А для подсчета количества ячеек внутри диапазона, удовлетворяющих заданному критерию используется СЧЁТЕСЛИ.

Часто выбор формулы для вычислений зависит от каких-либо условий. Например, при расчете торговой скидки могут использоваться различные формулы в зависимости от размера покупки. Для выполнения таких вычислений используется функцияЕСЛИ, в которой в качестве аргументов значений вставляются соответствующие формулы. Синтаксис функции: ЕСЛИ(А;В;С), где A - логическое выражение, правильность которого следует проверить; В- значение, если логическое выражение истинно; C - значение, если логическое выражение ложно. Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20: =ЕСЛИ(А1>3;10;20). Действие функции: функция ЕСЛИ, записанная в ячейку таблицы, выполняется следующим образом: если условие А истинно, то значение данной ячейки определит В, в противном случае С.

В и С могут быть числами, текстами или формулами. В функции ЕСЛИ можно использовать текстовые аргументы: =ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал"). В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, =ЕСЛИ(СУММ(А1:А3)=30;А10;""), здесь при невыполнении условия функция возвращает пустую строку вместо 0. Аргумент A (логическое выражение функции ЕСЛИ) может содержать текстовое значение. Например, =ЕСЛИ(А1="Динамо";10;290). Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным. Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или равно), =, <> (не равно)) и логических операций (логическоеИ, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические значения ИСТИНА или ЛОЖЬ.

ФункцииИиИЛИмогут иметь до 30 логических аргументов и имеют синтаксис:=И(логическое_значение1;логическое_значение2...)=ИЛИ (логическое_значение1;логическое_значение2...) Функция НЕ имеет только один аргумент и следующий синтаксис: =НЕ(логическое_значение).Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Всего допускается до 7 уровней вложения функций ЕСЛИ. Например, в следующей формуле ис-пользуются три функцииЕСЛИ:=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100); "Обычно"; ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда"))) Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда".

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ.

Эти функции не имеют аргументов и выглядят следующим образом: =ИСТИНА() =ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Выдать кредит", если выражение в ячейке А1 имеет значение ИСТИНА: =ЕСЛИ(А1=ИСТИНА();"Выдать кредит";"Не выдавать кредит"), в противном случае формула возвратит "Не выдавать кредит".

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

Выборочное суммирование. Иногда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям). В этом случае используют функцию СУММЕСЛИ(А;В;С), где A - диапазон вычисляемых ячеек; В - критерий в форме числа, выражения или текста, определяющего суммируемые ячейки; С - фактические ячейки для суммирования. В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для суммирования совпадают, аргумент С можно не указывать. Можно суммировать значения, отвечающие заданному условию. Напри-мер, в таблице на рис. 3.1 суммированы только продажи по фирмам, средняя цена продукции у которых не меньше 20 тыс. руб.



Рисунок 3.1 Выборочное суммирование

Функция СЧЕТЕСЛИ(А;В), подсчитывает в диапазоне A количество значений, удовлетворяющих критерию В. Функции СУММЕСЛИМН и СЧЕТЕСЛИМН работают аналогично классическим функциям СУММЕСЛИ и СЧЕТЕСЛИ, но умеют проверять не одно, а несколько условий (до 128 условий). Функция СРЗНАЧЕСЛИМН аналогична двум предыдущим, но считает не сумму, а среднее арифметическое. Функция ЕСЛИОШИБКА возвращает заданное вами значение или сообщение, если результатом вычисления формулы является значение ошибки, иначе возвращает результат вычисления формулы:



Рисунок 4.2 Проверка вычислений с помощью функции ЕСЛИОШИБКА.

Задание 1. Рассчитать подоходный налог на доходы физических лиц, если необлагаемая база для лиц, имеющих доход меньше 20000 рублей, равна 400 руб., в противном случае размер налога равен 13% от величины дохода. Решение.

Задание 2. Создать на листе Excel следующую таблицу:



Ответить на следующие вопросы:

1) Как изменится выручка, если курс акций 1 упадет на 2%, а курс акций 3 поднимется на 5%.

2) Как изменится выручка, если курс акций 1 упадет на 2%, а курс акций 2 и курс акций 3 поднимется на 5%.

3) Вычислить общую выручку по каждой ценной бумаге: «Акция 1», «Акция 2», «Акция 3». (Результаты оформить в виде новой таблицы).

Указание. Для суммирования ячеек по заданному условию используется функция СУММЕСЛИ, имеющая следующий формат: =СУММЕСЛИ(Диапазон; Критерий; Диапазон_суммирования).

Аргумент Диапазон - это интервал вычисляемых ячеек. Аргумент Критерий представляет собой число, выражение или текст, который определяет условия суммирования ячеек. Аргумент Диапазон_суммирования- это фактические ячейки для суммирования. Согласно данному выше описанию функции СУММЕСЛИ, выручка по ценной бумаге “Акция 1” может быть вычислена с помощью следующей формулы: =СУММЕСЛИ(А2:А6; “Акция 1”; B2:B6) или = СУММЕСЛИ(А2:А6; А4; B2:B6).

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

5) Написать формулу, осуществляющую автоматическую вставку сообщения «лидер на рынке ценных бумаг» для того типа акций, который имеет наибольший рейтинг.

Задание 3. Выборочное суммирование по двум критериям. Имеем таблицу по продажам следующего вида:

Требуется просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

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