2

Косовцева Татьяна Реональдовна, Петров Вадим Юрьевич. MS EXCEL в

расчетных задачах. Учебное пособие. – СПб: СПГУ ИТМО, 2010. – 82 с.

Учебное пособие предназначено для студентов экономических

специальностей, изучающих дисциплину «Информатика», и содержит

необходимые теоретические сведения по применению электронных таблиц

для решения конкретных математических и экономических задач,

построения диаграмм, обработки списков.

Для студентов специальностей – 080801 «Прикладная информатика в экономике», 080100 Экономика»

Рекомендовано к печати на заседании ученого совета Гуманитарного факультета, протокол № 8 от23 февраля 2010 г.

В 2009 году Университет стал победителем многоэтапного конкурса, в результате которого определены 12 ведущих университетов России, которым присвоена категория «Национальный исследовательский университет».

Министерством образования и науки Российской Федерации была утверждена Программа развития государственного образовательного учреждения высшего профессионального образования «СанктПетербургский государственный университет информационных технологий, механики и оптики» на 2009–2018 годы.

©Санкт-Петербургский государственный университет информационных технологий, механики и оптики, ©Т.Р. Косовцева, В.Ю.Петров,

ОГЛАВЛЕНИЕ

ОБЩИЕ СВЕДЕНИЯ

1. ОСНОВНЫЕ ОПЕРАЦИИ НА РАБОЧЕМ ЛИСТЕ MS EXCEL.

СОЗДАНИЕ И ФОРМАТИРОВАНИЕ ПРОСТОЙ ТАБЛИЦЫ

1.1. СТРУКТУРА ОКНА ПРИЛОЖЕНИЯ MS EXCEL

1.2. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ

1.3. ФОРМАТЫ ЧИСЛОВЫХ ДАННЫХ

1.4. ПРАКТИЧЕСКАЯ РАБОТА

1.4.1. ПРИМЕРЫ СОЗДАНИЯ ТАБЛИЦЫ

1.4.2. ВАРИАНТЫ ЗАДАНИЙ

2. ВЫЧИСЛЕНИЯ ПО ФОРМУЛАМ С ИСПОЛЬЗОВАНИЕМ

ВСТРОЕННЫХ МАТЕМАТИЧЕСКИХ ФУНКЦИЙ MS EXCEL.................. 19  2.1. ОСНОВНЫЕ ПОНЯТИЯ

2.2. ВСТРОЕННЫЕ ФУНКЦИИ MS EXCEL

2.3. ОПЕРАЦИИ

2.4. ПРАКТИЧЕСКАЯ РАБОТА

2.4.1. ПРИМЕРЫ

2.4.2. ВАРИАНТЫ ЗАДАНИЙ

3. ЛОГИЧЕСКИЕ ФУНКЦИИ MS EXCEL

3.1. ОСНОВНЫЕ ПОНЯТИЯ

3.2. ЛОГИЧЕСКИЕ ФУНКЦИИ ЕСЛИ, И, ИЛИ, НЕ

3.2. ПРАКТИЧЕСКАЯ РАБОТА

3.2.1. ПРИМЕРЫ

3.2.2. ВАРИАНТЫ ЗАДАНИЙ

4. ПОСТРОЕНИЕ ДИАГРАММ И ТАБУЛИРОВАНИЕ ФУНКЦИЙ............. 35  4.1. ОСНОВНЫЕ ПОНЯТИЯ

4.2. ПРИМЕРЫ ПОСТРОЕНИЯ ДИАГРАММ

4.4. ВАРИАНТЫ ЗАДАНИЙ

5.РЕШЕНИЕ ЗАДАЧ С ИСПОЛЬЗОВАНИЕМ ФУНКЦИЙ ВПР,

СУММЕСЛИ, СЧЕТЕСЛИ

5.1. ФУНКЦИЯ ВПР

5.2. ФУНКЦИЯ СУММЕСЛИ

5.3. ФУНКЦИЯ СЧЁТЕСЛИ

5.4. ПРИМЕР. СОЗДАНИЕ ВЕДОМОСТИ ДЛЯ РАСЧЕТА ЗАРАБОТКА

ПРИ ПОЧАСОВОЙ ОПЛАТЕ

5.4.1. ПОСТАНОВКА ЗАДАЧИ

5.4.2. РЕШЕНИЕ

5.4.3. РЕЗУЛЬТАТ

5.5. ВАРИАНТЫ ЗАДАНИЙ

6. ОБРАБОТКА СПИСКОВ

6.1. ОСНОВНЫЕ ПОНЯТИЯ

6.2.ФОРМИРОВАНИЕ СПИСКА

6.3. СОРТИРОВКА СПИСКОВ

6.4. АНАЛИЗ СПИСКА С ПОМОЩЬЮ ФИЛЬТРОВ

6.4.1. КОМАНДА АВТОФИЛЬТР

6.4.2. ПОИСК ТЕКСТОВЫХ ЗНАЧЕНИЙ

6.3.3. УДАЛЕНИЕ АВТОФИЛЬТРОВ

64.4. КОМАНДА РАСШИРЕННЫЙ ФИЛЬТР

6.4.5. ИНТЕРВАЛ КРИТЕРИЕВ

6.4.6. ТЕКСТОВЫЕ КРИТЕРИИ

6.4.7. ВЫЧИСЛЯЕМЫЕ КРИТЕРИИ

6.3.8. ССЫЛКИ НА ЯЧЕЙКИ ВНЕ СПИСКА

6.4.9. ССЫЛКИ НА ЯЧЕЙКИ ВНУТРИ СПИСКА

6.4.10. ПОДВЕДЕНИЕ ИТОГОВ

6.5. ВАРИАНТЫ ЗАДАНИЙ

СПИСОК ЛИТЕРАТУРЫ

ОБЩИЕ СВЕДЕНИЯ

Табличный процессор Microsoft Excel – ведущая программа, обработки электронных таблиц. Первая версия MS Excel появилась в 1985 году и обеспечивала только простые арифметические операции в строку или в столбец. В 1993 году вышла пятая версия MS Excel, ставшая составной частью интегрированного пакета Microsoft Office и обладает структурой управления, аналогичной другим программам MS Office.

В настоящее время MS Excel представляет собой достаточно мощное средство разработки информационных систем, которое включает как электронные таблицы, так и средствами визуального программирования (Visual Basic for Application (VBA). С помощью VBA можно автоматизировать всю работу, начиная со сбора информации, её обработки до создания итоговой документации, как для офисного пользователя, так и для размещения на Web-узле.

Важнейшей функцией MS Excel являются операции над числовыми данными, выполняемые с помощью формул, и автоматический пересчет результатов при изменении исходных данных. Электронные таблицы позволяют производить обработку текста, прогнозировать бюджет на основе сценария, публиковать рабочие листы и диаграммы в Интернете.

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

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

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

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

В данном пособие используется версия MS Excel 2007.

1. ОСНОВНЫЕ ОПЕРАЦИИ НА РАБОЧЕМ ЛИСТЕ MS EXCEL.

СОЗДАНИЕ И ФОРМАТИРОВАНИЕ ПРОСТОЙ ТАБЛИЦЫ

1.1. СТРУКТУРА ОКНА ПРИЛОЖЕНИЯ MS EXCEL

Файлы, создаваемые с помощью MS Excel, называются рабочими книгами Еxcel и имеют по умолчанию расширение xls. Имя файла может быть любым, разрешенным в операционной системе Windows.

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

Рабочее поле листа – это электронная таблица, состоящая из столбцов и строк (рис.1.1). Названия столбцов – буква или две буквы латинского алфавита. Названия строк – цифры. Размер рабочего листа определяется системой по умолчанию. MS Excel 2007 поддерживает листы размером до одного миллиона строк и 16-ти тысяч столбцов. Так сетка Office Excel состоит из 1048576 строк и 16384 столбцов.

Рис.1.1. Интерфейс стандартной рабочей книги с листами Пересечение конкретного столбца и строки образует ячейку.

Местоположение ячейки задается адресом, образованным из имени столбца и номера строки, на пересечении которых находится эта ячейка. Адрес ячейки в электронной таблице обычно называют ссылкой. Например, на рисунке 1.1.

указана ячейка с адресом (ссылкой) B5.

Ссылка типа A1 называется относительной. Кроме нее возможна абсолютная ссылка на ячейку - $A$1, а также смешанные ссылки $A1 и A$1.

Прямоугольная часть таблицы, состоящая из смежных ячеек, называется областью, или интервалом ячеек. Интервал однозначно определяется своей первой и последней ячейками и записывается с указанием этих ячеек, разделенных двоеточием, например, B2:D5 или C10:C15. Для областей применяются также термины: блок, диапазон, массив.

При использовании ячеек одного рабочего листа на другом листе, ячейки указываются с именем листа. Например, ячейку Лист1!С5 можно использовать на других листах. Можно использовать и ячейки другой рабочей книги.

ПРИМЕР: ЗАПИСЬ ‘[ОТЧЕТ.XLS]ЛИСТ2’!C10 ОЗНАЧАЕТ ЯЧЕЙКУ C

НА РАБОЧЕМ ЛИСТЕ С ИМЕНЕМ ЛИСТ2 РАБОЧЕЙ КНИГИ ОТЧЕТ.XLS.

Важным элементом управления является маркер заполнения (рис.1.2).

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

Рис.1.2. Окно рабочей книги. Стрелка указывает на Маркер заполнения Для просмотра электронной таблицы используются линейки прокрутки.

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

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

Создается рабочая книга при активизации кнопки Office командой Создать, при этом используется один из готовых шаблонов. Для новой книги стандартные установки определяются командой Параметры EXCEL, вкладка Основные (рис.1.3).

На вкладке Формулы указывается стиль ссылок (адресов) по умолчанию: A1 (адрес клетки - имя столбца, номер строки) или R1C1 (адрес клетки - номер строки и номер столбца).

Одна из ячеек рабочего листа является текущей, или выделенной, на рис.1.1 она обведена жирной рамкой. Адрес текущей ячейки при этом указывается в поле имени (области ссылок) - области в левой части строки формул. Для того чтобы выделить другую ячейку, нужно щелкнуть по ней после наведения указателя мыши в виде “белого креста”. Ввод данных с клавиатуры осуществляется в текущую ячейку. Содержимое текущей ячейки отображается в строке формул. В ячейки рабочего листа можно вводить два вида данных: постоянные значения и формулы Постоянные значения – это текст или числовые значения, включая даты, время дня, денежные значения, проценты, дроби, числа в экспоненциальном формате.

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

Чтобы ввести постоянное значение в ячейку, нужно ее выделить, набрать число или текст на клавиатуре и нажать клавишу Enter, после чего при обычной настройке MS Excel выделенной оказывается следующая ячейка текущего столбца. Для окончания ввода в ячейку можно также использовать клавишу Tab, клавиши управления курсором или щелчок мышью, что позволяет переходить к любой требуемой ячейке. В ячейку вводится одно число, одна формула или связанный текст (слово или предложение).

Формула - это некоторая последовательность постоянных значений, ссылок на ячейки, имен, функций или знаков операций, которая задает правило для вычисления нового значения по данным. Формулы всегда начинаются со знака равенства (=). После ввода формулы MS Excel автоматически выводит в ячейку результат вычислений. Он может меняться, если меняются значения в ячейках, входящих в формулу.

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

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

После завершения ввода число в ячейке (в том числе и результат вычисления по формуле) по умолчанию выравнивается по правому краю.

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

Средства MS Excel по форматированию чисел и шрифта, а также размеров строк и столбцов, позволяют правильно отображать числовые данные.

В MS Excel текстом считается любая последовательность, состоящая из алфавитно-цифровых символов и пробелов, которая не может быть интерпретирована как числовые данные или формула.

Чтобы ввести текст, нужно выделить ячейку и набрать текст. Ячейка может вмещать до 255 символов. Если требуется ввести произвольный набор символов как текст (в том числе числа и даты) следует заранее установить для данной ячейки формат Текстовый или набрать перед числом знак апострофа ('). Текст по умолчанию выравнивается по левому краю.

Если выделить ячейку и заново ввести данные, то ранее введенные данные стираются.

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

После этого можно изменить содержимое ячейки и нажать клавишу Enter.

В MS Excel 2007 можно выделить ячейки определенного типа, например, с числовыми данными, формулами или те, к которым применено условное форматирование. Для этого нужно нажать кнопку Найти и выделить в группе Форматирование на вкладке Главная Выделение фрагмента рабочего листа является важной операцией, так как операции форматирования и редактирования (правки) применяются к выделенным ячейкам и фрагментам рабочего листа. Для того, чтобы выделить область (интервал) ячеек, нужно протащить указатель мыши от начальной ячейки области к конечной. Выделенные ячейки обозначаются на листе инверсным цветом (установленным цветом выделения). Текущей является первая ячейка выделенного интервала. Ее адрес отображается в области ссылок.

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

MS Excel допускает выделение несмежных областей. Это достигается выделением каждой области при нажатой клавише Ctrl. Перед выполнением данных операций нужно выделить требуемую ячейку или область.

Укажем два наиболее важных способа очистки содержимого ячеек:

клавиша Delete удаляет содержимое ячейки;

кнопка |Очистить (группа Редактирование, вкладка Главная) предлагает выбрать вариант очистки из пунктов Все, Форматы, Содержимое, Примечание.

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

Можно вставить новый рабочий лист, строки, столбцы, ячейки на место выделенных строк, столбцов, ячеек со сдвигом последних. Вставка выполняется по командной кнопке Вставить (группа Ячейки, вкладка Главная) При вставке ячеек в диалоговом окне возникает запрос на сдвиг исходных ячеек вправо или вниз.

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

Если числовые данные имеют специальные единицы измерения – денежные, проценты, даты, время, то нужно использовать соответствующие специальные форматы.

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

Группа кнопок Шрифт позволяет выбрать требуемый формат из списка форматов и установить его параметры.

Группа кнопок Числовой (рис.1.4) позволяет установить требуемое количество значащих цифр в десятичной записи числа.

Формат Текстовый позволяет интерпретировать число или формулу как текст. В этом случае вычисления с данными ячейками произвести невозможно.

Формат Экспоненциальный позволяет записывать числа с десятичным порядком. Например: число 0,000873 может быть записано как 0,873E-3 или 8,73E-04. Число 1000000 можно задать как 1E+06 или 1E6 (знак “плюс”опущен). При этом буква E – латинская.

Существуют форматы Денежный, Финансовый и Процентный.

Формат Дата предлагает большое число вариантов ввода дат, например, 01.04.10 или 1 Апрель, 2010 или 31 дек 10. Еще один вариант формата даты содержит дробную черту, например, 1/6 MS Excel интерпретирует как июня. Чаще с дробной чертой используется форма 01/06/10.

Формат Время чаще всего использует двоеточие как разделитель (3:20:25 или 12:05). Если нужно выводить время дня в 12-ти часовом формате, то после времени вводят буквы AM или PM (до полудня, после полудня), например, 3:00 PM. Можно также ввести просто A или P вместо AM или PM, причем необходимо ввести пробел между временем и буквами.

Если не вводить AM или PM, то MS Excel автоматически выведет время дня, используя 24-х часовой формат, например, 15:00. Время можно задать в комбинации с датой, например, 1 сентября 2006 10:00.

Рекомендуется следующий общий порядок работы:

• загрузить MS Excel;

• изучить структуру окна MS Excel;

• изучить простейшие операции на рабочем листе;

• выполнить учебные примеры по вводу чисел, текста, формул;

• выполнить учебный пример по созданию и форматированию таблицы;

• создать и сохранить таблицу своего варианта.

Пример 1. Набор текста и простейшие расчеты в MS Excel 1. Ввод текста. Введите в ячейку A1 текст: Выполнение простейших расчетов в Excel. После этого просмотрите содержимое ячеек A1, B1,C1 в строке формул. Весь текст относится к ячейке A1. В дальнейшем при вводе текста следует применять принцип помещения связанного текста в одну ячейку. Это упростит форматирование таблицы.

2. Ввод чисел. Как правило, в расчетах используются целые и десятичные числа. Для разделения целой и дробной части используется запятая. В ячейки A5 и B5 введите числа 2,5 и 0,6.

Введите в ячейку C5 формулу =A5+B Введите в ячейку D5 формулу =2*A5+B Введите в ячейку E5 формулу =2*(A5+B5) Проанализируйте полученные результаты. Для исходных величин используйте обозначения x, y, которые введите в вышележащую строку. В соответствии с расчетами введите обозначения и для результатов.

Примерный вид таблицы показан на рис. 1.5.

Рис.1.5. Фрагмент рабочего листа при выполнении примера 4. Сохранение, закрытие и открытие файла Сохраните таблицу в личной папке в файле с именем Tаble1.xls. Для этого выполните команду Сохранить (кнопка Office). При выполнении команды появится диалоговое окно команды Сохранение документа (рис.

1.6).

Рис.1.6. Диалоговое окно при первом сохранении документа В поле Папка следует перейти к личной папке, в поле Имя файла набрать имя: Tаble1.xls. В поле Тип файла следует выбрать: Книга Excel.

При внесении в файл изменений и повторном сохранении файла по команде Сохранить, диалоговое окно не появляется.

Закройте приложение по команде Выход из Excel (кнопка Office)..

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

Замечание. Рабочую книгу можно сохранить под новым именем или в другом формате по команде Сохранить как… (кнопка Office)..

Пример 2. Создание и форматирование таблицы Переименуйте листы 1, 2 и 3 рабочей книги в следующие:

Простейшие, Расходы, Правка, соответственно.

Перейдите на лист Расходы. Следуя указаниям, создайте и отформатируйте таблицу, вид которой показан на рис. 1.7.

Указания к выполнению учебного примера 1. Введите в ячейку A1 заголовок таблицы, в ячейки A2:D2 заголовки столбцов, в ячейки A3:A7 заголовки строк, в ячейки B3:C7 цену (только числа) и количество товара.

2. В ячейках D3:D7 вычислите стоимость товара. Для этого в ячейку D введите формулу: =B3*C3, а затем скопируйте ее в ячейки D4:D7 с помощью Маркера заполнения. Для этого наведите указатель мыши на маркер (в нижний правый угол ячейки с формулой) и протащите его вдоль ячеек D4:D7.

3. В ячейке D8 вычислите сумму столбца D с помощью кнопки Автосуммирование (группа Редактирование, вкладка Главная).

4. Отформатируйте таблицу:

Форматирование заголовка. Выделите ячейки A1:D1 и назначьте шрифт Times New Roman,14 пунктов, полужирный с помощью кнопок на панели инструментов, вкладка Главная. В окне команды Выравнивание установите параметр Выравнивания по горизонтали - По центру выделению.

Форматирование остальной части таблицы. Выделите другую часть таблицы, в ней используйте шрифт Times New Roman, размер 12 пт., в ячейках с ценами и стоимостью назначьте денежный формат (группа Число, вкладка Главная), формат Денежный. Увеличьте ширину столбцов, чтобы заголовки помещались в ячейках.

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

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

7. Изучите все элементы форматирования, в частности, форматы данных, перенос по словам, выравнивание по горизонтали и по вертикали (группа Выравнивание) (рис.1.8.).

Замечание. Перенос внутри слов в MS Excel можно выполнить только вручную.

Рис.1.8. Вид окна Формат ячеек, вкладка Выравнивание 8. Откройте новую рабочую книгу, выполните задание по варианту, сохраните его и завершите работу приложения MS Excel.

ЗАДАНИЕ

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

3. Сохраните таблицу в личном каталоге в файле с расширением.xls.

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

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

Добавить четвертый столбец, в котором вычислить цены предыдущей недели по формуле Cold = Cnew (1 + / 100), где C - старая цена, изменение цены по отношению к прошлой неделе.

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

Наименование товара Средняя Изменение по сравнению с Металлы, металлоизделия Добавить четвертый столбец, в котором вычислить цены предыдущей недели (см. указания вар.3).

Наименование товара Средняя Изменение по сравнению с Лес, стройматериалы пород(тыс. руб./м куб.) пород(тыс. руб./м куб.) Добавить четвертый столбец, в котором вычислить цены предыдущей недели (указания вар. 3).

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

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

Вычислить относительный прирост прожиточного минимума по всему населению и по социальным группам.

Учитывая средние оптовые цены, определить суммарную стоимость товара.

2. ВЫЧИСЛЕНИЯ ПО ФОРМУЛАМ С ИСПОЛЬЗОВАНИЕМ

ВСТРОЕННЫХ МАТЕМАТИЧЕСКИХ ФУНКЦИЙ MS EXCEL

Формула задает правило для вычисления нового значения через исходные значения. Формула должна подчиняться определенным правилам записи, т.е. синтаксису. В MS Excel запись формулы всегда начинают со знака равенства. Часть формулы, следующая за знаком равенства, называется выражением.

Формулой в MS Excel называется последовательность, содержащая следующие элементы:

знак равенства (=) – признак формулы в MS Excel;

операнды (числа, текст, ссылки на ячейки, ссылки на массивы ячеек, встроенные функции);

знаки операций (иногда их называют операторами);

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

Встроенные функции MS Excel – это функции, вычисление которых выполняется по определенным алгоритмам, содержащимся в приложении MS Excel. Вызов встроенной функции происходит при вычислении по формуле, содержащей эту функцию.

Запись функции в формуле MS Excel аналогична записи функций в математике. Она имеет вид f ( x1 ; x 2 ; K; x n ), где f – имя функции, x1, x2,K, xn - аргументы.

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

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

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

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

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

Операции выполняются над некоторыми данными (операндами).

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

Порядок вычисления значения по формуле MS Excel:

вычисляются значения функций, входящих в формулу;

вычисляются выражения в скобках;

выполняются операции слева направо с учетом приоритета.

Приоритет арифметических операций в формулах MS Excel указан в табл. 2.3.

Комбинировать арифметические операции с прочими не рекомендуется (за исключением связывания массива).

Если формула не может быть вычислена, в ячейке появляется сообщение об ошибке, которое начинается символом #.

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

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

Сообщения об ошибках:

#ДЕЛ/0! - деление на нуль #ЧИСЛО! – недопустимый аргумент числовой функции #ЗНАЧ! – недопустимое значение аргумента или операнда #ИМЯ? – неверное имя ссылки или функции #Н/ Д! – неопределенные данные #ССЫЛКА! – ссылка на несуществующие ячейки При обнаружении ошибки следует перейти в режим редактирования и исправить формулу. В случае затруднений надо провести синтаксический анализ формулы и ввести ее заново.

Порядок вычислений по формуле: =3+5*COS(B4)-2*A2:

Пример 1. Вычислить объем и площадь поверхности заданного конуса с основанием R и высотой h. Значения R и h заданы. Положить R=1 м, h=3 м. Отчет представить в виде распечатки рабочих листов, содержащих условие задачи, расчетные формулы, расчеты в MS Excel в режиме отображения данных и формул.

РЕШЕНИЕ:

1. Расчетные формулы:

площадь боковой поверхности S1 = R l площадь поверхности конуса S = S 0 + S 2. Создание рабочего листа с заданием и расчетными формулами.

Порядок действий рекомендуется следующий:

создать новый файл и сохранить его в личной папке под именем Конус.xls;

в ячейку A1 ввести тему работы; в нижележащие ячейки (например, A3, A4) ввести номер примера и текст задачи;

в отдельные ячейки A6:A7 и A10:A14 ввести обозначения размеров конуса с пояснениями; ввести данные: значения 1 и 3 для R и h соответственно в B6, B7;

выполнить вставку расчетных формул с помощью приложения MS Equation 3.0.

3. Выполнение расчетов в MS Excel.

В ячейки E10:E14 последовательно ввести формулы:

=КОРЕНЬ(B6*B6+B7*B7) =ПИ()*B7*B =ПИ()*B7*E =E11*B6/3.

4. Форматирование таблицы.

Установить в таблице шрифт Times New Roman, размер 10.

Расположить текст по образцу, используя кнопки вкладки Главная.

Если весь текст не виден в объединенных ячейках, нужно увеличить высоту строки.

Выполнить подчеркивание заголовка, используя команду Формат ячейки|Шрифт|Подчеркивание одностороннее по значению контекстнозависимого меню (правая копка мыши).

Фрагмент рабочего листа MS Excel с решением примера 1 представлен на рис. 2.1.

A B C D E F

Вычисления по формулам с использованием встроенных математических Вычислить объем и площадь поверхности конуса, если заданы его высота и радиус 4 основания c точностью до четвертого десятичного знака Площадь основания конуса S0= Площадь боковой поверхности конуса S1= Площадь поверхности конуса S= 15 Формулы для вычисления Рис. 2.1. Фрагмент рабочего листа Excel для примера при заданных значениях m = 2; c = 1; t = 1,2; b = 0, Решение выполним на одном рабочем листе с примером 1. Порядок действий аналогичен предыдущему примеру:

введем условие задачи с формулами для вычислений;

введем в отдельные ячейки обозначения и значения исходных данных m, c, t, b (см. рис. 2.2);

в ячейки для результатов введем формулы:

=(A33*TAN(C33)+ABS(B33*SIN(C33)))^0, =LOG(A33;2)*COS(D33*C33)*EXP(C33+B33);

форматируем таблицу.

Фрагмент рабочего листа с решением примера 2 представлен на рис.2.2.

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

• при вставке ссылки на ячейку нужно щелкнуть мышью по этой ячейке;

• при вставке функции нужно вызвать Мастер функций (вкладка Формулы, кнопка Вставить функцию), который позволяет выполнить вставку функции за два шага: первый шаг – выбор категории функции и выбор функции, второй шаг – задание аргументов функции.

Опишем эту технологию подробнее на примере первой формулы:

• для возведения в степень используем знак операции ^, поэтому основание степени нужно взять в скобки; соответственно, наберем =( • введем значение m щелчком по ячейке A33 и знак умножения * • вызовем Мастер функций, при этом появится диалоговое окно первого шага, представленное на рис. 2.3, выберем категорию функций Математические и в появившемся списке функций укажем TAN Рис. 2.3. Диалоговое окно Мастера функций (первый шаг) • при нажатии OK появится окно второго шага Мастера функций (рис.

2.4). В поле Число введем аргумент щелчком по ячейке со значением t C33.

При нажатии OK или клавиши Enter ввод функции заканчивается, заканчивается и ввод формулы в ячейку; для продолжения ввода в эту ячейку нужно нажать кнопку (Изменить формулу) в строке формул;

Рис. 2.4. Диалоговое окно Мастера функций (второй шаг) • вставим функцию ABS. При задании аргумента наберем B33* и снова вызовем Мастер функций;

• функция от функции выбирается в списке функций в строке формул.

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

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

• после окончания ввода внешней функции ABS закончим ввод формулы, набрав закрывающую скобку и возведение в степень 0,5 – символы - )^0,5 или )^(1/2). Можно было также использовать функцию КОРЕНЬ, но это привело бы к тройному вложению функций в формуле и усложнению ее набора.

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

1. z = x1 / 2 + (3,37 x + 2,03) 2 ; x = 2, 1. z = x1 / 2 + (3,4 x + 12,3) 2 ; x = 12, 3. z = 4. z = u t, где u = x1/ 3 a1/ 2 ; t = ln(a1/ 2 + x1/ 3 ); x = 18,08; a = 11, 3. z =

3. ЛОГИЧЕСКИЕ ФУНКЦИИ MS EXCEL

Логическое выражение - это высказывание, принимающее значения ИСТИНА или ЛОЖЬ. Логические выражения в MS Excel позволяют выполнять вычисления, зависящие от условий. Условие считается выполненным, если значение соответствующего ему логического выражения - ИСТИНА, и не выполненным, если значение логического выражения ЛОЖЬ.

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

Пусть, например, в MS Excel требуется проверить истинность неравенств:

им могут соответствовать логические выражения в MS Excel:

В данном примере величины, обозначенные буквами, помещены в некоторые ячейки. Ссылка на ячейку $A$5 является абсолютной, показывая постоянство величины z Пара символов < > означает - «не равно», смысл остальных символов очевиден. На равенство можно проверить и текстовое значение, причем текст в выражении заключается в кавычки.

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

Логическая функция ЕСЛИ имеет вид:

где x1, x2, x3 – аргументы, здесь x1 - логическое выражение, x2, x3 – любые выражения, разрешенные вMS MS Excel; причем вычисляется x2, если x1 имеет значение ИСТИНА, и x3, если x1 имеет значение ЛОЖЬ. Если третий аргумент функции не определен, то ошибки в записи функции нет – в этом случае ей присваивается значение ЛОЖЬ, если условие не выполнено.

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

Примеры: ЕСЛИ(A5>0;LN(A5);-1); ЕСЛИ(B2< >0;1/B2;” ”) Логическая функция И имеет вид:

где x1; x2;; …;xn – аргументы, являющиеся логическими выражениями. Функция может содержать до 30 аргументов. Функция И принимает значение ИСТИНА, если все ее аргументы истинны, в противном случае она принимает значение ЛОЖЬ.

Логическая функция ИЛИ имеет вид:

где x1; x2;; …;xn –аргументы, являющиеся логическими выражениями.

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

Логическая функция НЕ имеет вид где x – логическое выражение. Ее значение ИСТИНА, если x имеет значение ЛОЖЬ, и наоборот.

Пример 1. Вычислить величину y при заданном значении x РЕШЕНИЕ.

В ячейки рабочего листа A1,B1 вводим обозначения x, y В ячейку A2 вводим значение x В ячейку B2 вводим формулу:

1-й способ. =ЕСЛИ(A2 1. При применении функции ЕСЛИ его выполнение соответствует вычислению значения, равного третьему аргументу, но нужно отделить случаи «меньше 4» и «больше или равно 4», поэтому третий аргумент снова будет функцией ЕСЛИ, с помощью которой мы и проверим условие x2+y2 < 4.

Значения x, y введены в ячейки A2, B2. В ячейку C2 для значения z вводим формулу, начав с вызова функции ЕСЛИ. Чтобы задать третий аргумент снова вызовем функцию ЕСЛИ. Последовательный вид окон внешней и внутренней функции ЕСЛИ представлен на рисунках 3.3-3.5..

Щелкнув в строке формул, мы вернемся к внешней функции ЕСЛИ.

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

Рис.3.5. Окно внешней функции ЕСЛИ после выхода из внутренней функции Пример 3. Определить, является ли истинной принадлежность точки заданной области D. Проверить условие принадлежности области для нескольких точек.

Область D составлена из двух секторов круга радиусом 5 см и изображена на рис.3.6 серым цветом. Область не содержит границу.

M 1 (2,2), M 2 (2,2), M 2 (1,1), M 3 (6,0), M 4 (2,2), 5 (0,0). При проверке принадлежность точки области D показать значением ИСТИНА.

РЕШЕНИЕ. Заданная область является решением системы неравенств:

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

=И(B2^2+C2^20). Затем копируем ее в ячейки D3:D6.

Результаты работы представлены на рис. 3.7.

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

=ЕСЛИ(И(B2^2+C2^20);

принадлежит области"). Затем копируем ее в ячейки E3:E6.

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

Задача 2. Определить принадлежность точек M, M, M, M, M заданной области D. Область задана системами или совокупностями неравенств.

Координаты точек на плоскости задать самостоятельно.

4. ПОСТРОЕНИЕ ДИАГРАММ И ТАБУЛИРОВАНИЕ ФУНКЦИЙ

MS Excel 2007 поддерживает различные типы диаграмм. Диаграммы в Excel используются для графического представления рядов данных. Рядом данных называется совокупность значений, находящихся в последовательных ячейках строки или столбца. Приложение MS Excel предоставляет пользователю 14 типов стандартных диаграмм, каждая из которых имеет несколько разновидностей. Диаграммы, создаваемые на рабочих листах, называются внедренными диаграммами и представляют собой графические объекты. Как и все графические объекты, внедренные диаграммы могут располагаться в любом месте рабочего листа и подчиняются обычным операциям с объектами, т.е. можно изменить их размеры, а также цветовое и графическое оформление.

Каждый используемый в диаграмме ряд данных может иметь не более 4000 значений. На одной диаграмме может быть до 255 рядов данных.

Для упорядочения значений в рядах данных служат категории.

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

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

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

Конкретные типы диаграмм и процесс построения рассмотрены на примерах.

Пример. Построение круговой диаграммы и гистограммы Круговая диаграмма является одним из самых простых видов диаграмм. Она строится по одному ряду числовых данных и показывает долю каждого числового значения в сумме значений. Можно вывести также процентное содержание долей относительно целого.

В качестве примера построить диаграмму распределения стоимости канцелярских товаров.

Рис. 4.1. Фрагмент рабочего листа с данными для круговой диаграммы Для этого нужно выполнить следующие действия:

выделить область данных — интервал D2:D7 ;

вызвать Мастер диаграмм (группа Диаграммы на вкладке Вставка);

в диалоговом окне выбрать Тип диаграммы — Круговая и Вид диаграммы — Объемный;

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

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

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

используя команду Переместить диаграмму (группа Расположение, вкладка Конструктор) выбрать вариант размещения диаграммы.

На этом построение диаграммы заканчивается. Построенная диаграмма является внедренным объектом. Его можно выделить, копировать, переместить, удалить, изменить размеры так же, как это выполнялось для рисунков.

Примечание: иногда кроме рядов числовых данных имеется ряд с указанием номера или названия элемента, к которому относится числовое значение - так называемая категория. При указании диапазона данных можно включать в него категории. Выполним задание категорий с помощью редактирования диаграммы. Для этого выделим ее и правой кнопкой мыши вызовем контекстное меню. Выберем команду Исходные данные и в диалоговом окне перейдем к вкладке Ряд. Щелкнем мышью в поле Подписи категории и выделим область A2:A7. Нажмем ОК. Результаты представлены на рис. 4.2.

Рис. 4.2. Круговая диаграмма с подписями данных Категория и доля В процессе редактирования можно изменить многие элементы, и даже тип диаграммы. Представим ряд данных нашей задачи в виде гистограммы, построив еще одну диаграмму. После редактирования диаграмма типа Гистограмма представлена на рис. 4.3.

Пример 2. Построение точечной диаграммы.

Все диаграммы, которые отображают ряды данных в прямоугольной системе координат, показывают действительные числовые значения лишь для ряда значений. Ряды категорий служат только для упорядочения значений, и фактически являются нумерацией типа 1, 2, …. Если требуется графическое представление функции для произвольно заданных значений аргумента, можно использовать единственный тип диаграммы для решения этой задачи – Точечную диаграмму. Ее построение рассмотрим на примере из практической работы темы 3, в которой рассматривались точки с произвольными декартовыми координатами на плоскости.

В качестве исходных данных возьмем точки из примера 2:

M1 (2,2), M 2 (2,2), M 2 (1,1), M 3 (6,0), M 4 (2,2), M 5 (0,0). Используем таблицу координат, ранее построенную в этой работе, или создадим новую таблицу (рис.4.4).

Построим диаграмму, в которой точки с данными координатами отображаются на декартовой плоскости. Выделим интервал B2:C6 и вызовем Мастера диаграмм. Выберем тип диаграммы – Точечная и выберем диаграмму первого вида, состоящую из отдельных точек. Если использовать другие виды, точки будут соединены в порядке их следования в таблице.

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

Рядом категорий считаются координаты по оси x. Далее зададим параметры диаграммы: удалим легенду, введем название диаграммы Точки на плоскости и оси x и y, назначим линии по обеим осям. Диаграмма с заданными точками показана на рис. 4.6. На рис. 4.7 показана диаграмма без линий сетки Рис. 4.6. Точечная диаграмма для примера 3 (с линиями сетки) Рис. 4.7. Точечная диаграмма для примера 3 с обозначением точек С помощью редактирования изменены: формат области построения, шкалы для осей, шкалы по осям выровнены с помощью растяжения – сжатия.

Многие элементы диаграммы можно изменять в процессе редактирования.

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

Пример 3. ( Функция одной переменной для шагового аргумента).

изменяющегося от 0 до 1,5 с шагом 0,1. Построить график функции.

РЕШЕНИЕ. Решение разбивается на два основных этапа: построение таблицы значений функции и построение графика функции.

Построение таблицы • Наберем заголовки столбцов для x и y в ячейках A1, B1.

• Наберем первое значение x, равное 0, в ячейку A2.

• Выполним команду по команде Прогрессия (вкладка Главная, группа Редактирование, кнопка Заполнить), зададим в диалоге Расположение в столбце, Арифметическая прогрессия, Шаг 0,1, Предельное значение 2. Заполнятся ячейки A4:A22.

• В ячейку B2 введем формулу: =SIN(4*A2)^2/(A2+1) и скопируем • Выполним форматирование данных (чисел) и обрамление таблицы. Фрагмент рабочего листа с таблицей показан на рис.4.8.

• Построение графика функции. Для построения графика выделим диапазон данных (ячейки A1:B22) и построим точечную диаграмму, вид которой представлен на рис. 4.8.

Пример 4. (Функция, заданная различными аналитическими выражениями (сложная функция)). Построить таблицу значений и график функции где: S 0 - заданный необлагаемый минимум, руб.;

K - ставка подоходного налога.

3). Сумма, которую получает работник на руки S на руки :

Требования к решению:

Создать электронную таблицу Расчетная ведомость, которая позволяет вычислить следующие величины: начислено ( S начисл ), подоходный налог ( P ) и сумму на руки ( S на руки ) для каждого из работников, а также итоговые суммы по предприятию в целом и по бригадам, задавшись конкретными значениями T, K и S0.

Таблица должна иметь вид, представленный на рис. 5.5.

Вычисления произвести при следующих исходных данных: величина заработка, не облагаемая налогом - S 0 =2000 руб, ставка подоходного налога - K =10%. Соответствие разряд- тариф представлен на том же рисунке.

Необходимо, что бы решение удовлетворяло следующим условиям:

• Изменение тарифа в справочнике приводит к автоматическому изменению тарифа T для каждого работника.

• Изменение разряда работника приводит к автоматическому изменению его тарифа.

• Перевод работника в другую бригаду (изменение в столбце Бригада) приводит к изменению сумм итогов по бригадам • Изменение ставки подоходного налога K и необлагаемого минимума S приводит к автоматическому изменению величины суммы на руки S на руки.

Подготовительные операции.

Заполним ячейки рабочего листа, которые не требуют ввода формул (рис.5.6). Обратите внимание, что в ячейку D4 заносим число 10, а в ячейку F4 - число 2000.

A B C D E F G H

1. Заполнение справочника. Введите:

1.1. В ячейку А6 текст “Тарифный справочник” 1.2. В ячейку А7 текст “Разряд” 1.3. В ячейку B7 текст “Тариф” 1.4. В ячейки А8 : А11 значения разрядов 2,3,4,5 (Значения разрядов 1.5. В ячейки В8 : В11 значения тарифов 40,45,50, 2. Заполнение столбца Тариф. Для этого необходимо использовать функцию ВПР 2.1. Активизируйте ячейку С 2.2. Активизируйте Мастер функций 2.3. На первом шаге выберите функцию ВПР (категория “Ссылки и 2.4. На втором шаге заполните значения аргументов:

2.5. Завершите ввод формулы (кнопка ОК ).

2.6. Формула имеет вид =ВПР(B14; A8: B11;2), т.е. в ней все ссылки относительные; для дальнейшего копирования ссылки на справочник исправьте на абсолютные =ВПР(B14;$A$8:$B$11;2) 2.7. В ячейки C15:C17 эту формулу занести копированием:

3. Заполнение столбца Начислено. В ячейку Е14 требуется ввести формулу =С14*D14 (помните знак равенства признак формулы!; в конце набора формулы клавиша ; ссылки на ячейки в этой формуле относительные). В ячейки Е15:Е17 эту формулу занести копированием.

4. Заполнение столбца Под. налог. В ячейку F14 требуется ввести формулу =ЕСЛИ(E14-$F$4>0;(E14-$F$4)*$D$4/100;0). В ячейки F15:F17 эту формулу занести копированием.

5. Заполнение столбца На руки аналогично столбцу Начислено. В ячейку G14 формула =Е14-F14, в ячейки G15:G17 формула заносится копированием.

6. Вычисление частичных сумм:

6.1. В ячейку Е19 введем текст “Итоги по бригадам”;

6.2. В ячейку Н20 введем текст “рг”;

6.3. В ячейку Н21 введем текст “рм”;

6.4. В ячейку Е20 введем формулу =СУММЕСЛИ($H$14:$H$17;$H20;E$14:E$17);

6.5. В ячейку Е21, и интервал ячеек F20:H21 эту формулу можно просто 7. Построение круговой диаграммы (рис.5.7).

B C D E F G H

7.1. В ячейки В24-В25 занесем названия бригад: “рг”, “рм” 7.2. В ячейки С24-С25 занесем содержимое ячеек Е20-Е21 (столбец Начислено). Для этого в ячейку С24 вставим формулу =Е20, а в 7.3. Вызвать Мастер диаграмм и построить круговую диаграмму;

7.4. В результате построения получим диаграмму, представленную 8. Печать результатов и завершение работы.

После выполненных операций рабочий лист в режиме отображения данных примет вид, представленный на рис.5.8. На рис.5.9.а - 5.9.б представлен тот же лист в режиме отображения формул.

Во всех вариантах требуется создать содержательную таблицу, которая должна состоять из 12-14 строк. Информационное наполнение таблицы определяется условием задания. При решении задач обязательно использовать встроенные функции ВПР, СУММ, СУММЕСЛИ, ЕСЛИ. При необходимости пользуйтесь другими встроенными функциями.

Отметим, что в ряде заданий доллар США обозначен как USD, а условная денежная единица - у.е.

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

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

Оценки на экзамене - 5, 4, 3, 2.

Базовая величина стипендии — 10 у.е.

Базовую стипендию получают все сдавшие сессию - (нет "двоек").

Сдавшие без “троек” получают 1,5 базовых стипендии.

Сдавшие все экзамены на “пятерку” получают 2 базовые стипендии.

Не сдавшие (получившие хотя бы одну “двойку”) стипендии не получают.

Курс у.е. равен 30 руб. и может меняться.

Все расчеты вести в рублях.

Требования к решению:

Изменение курса у.е. и величины базовой стипендии автоматически ведет к изменению величины стипендии.

Изменение оценки за экзамен автоматически изменяет размер стипендии.

Обеспечить подведение итогов сессии:

- стипендиальный фонд группы;

- отдельно суммы для всех трех "категорий" студентов, получающих стипендию (отличников, хорошистов, сдавших).

Построить диаграмму для иллюстрации доли стипендий различных "категорий".

Рекомендации:

• хранить величину базовой стипендии в отдельной ячейке;

• каждому студенту присвоить "категорию";

• "категория" вычисляется как минимальная оценка среди им полученных за сессию, для чего воспользоваться встроенной • для начисления стипендии завести справочник (рис.5.10), в котором вход - “категория”, а выход - величина коэффициента для начисления стипендии (0; 1; 1,5 и 2).

Составить таблицу, которая позволяет составить ведомость на приобретение персональных компьютеров (ПК) для некоторого холдинга.

Холдинг объединение нескольких фирм (12-14). Будем полагать, что он может включать фирмы двух видов: российские и совместные.

Считать, что вычисление стоимости ПК происходит следующим образом.

Базовая стоимость компьютера —1000 USD и может меняться. Курс USD 30 руб. и может меняться. Все расчеты вести в рублях.

Российские предприятия платят базовую стоимость плюс налог на добавленную стоимость (НДС) 20%.

Совместные предприятия НДС не платят.

Каждая фирма покупает несколько компьютеров (от 1 до 100 шт.).

Каждая фирма имеет право на скидку в зависимости от итоговой суммы. При покупке:

Требования к решению:

• Каждая строка обязательно содержит следующую информацию:

- количество приобретенных компьютеров;

- стоимость компьютеров без скидки и при необходимости с НДС;

- стоимость со скидкой (к оплате).

• Изменение Базовой стоимости и курса USD автоматически ведет к изменению стоимости.

• Вычислить:

- общая стоимость (к оплате) по холдингу;

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

Рекомендации:

• хранить Базовую стоимость и курс USD в отдельных ячейках;

• для начисления скидок завести справочник, в котором вход - “количество компьютеров”, а выход - величина коэффициента для начисления скидки (0; 5; 10 и 15%).

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

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

Каждый пенсионер имеет базовую пенсию от 100 до 200 у.е. и в зависимости от стажа получает надбавку:

Каждый пенсионер платит взнос в страховой фонд. Величина взноса зависит от МРОТ1 (10 у.е.) и возраста. При возрасте до 65 лет взнос равен двум МРОТ, 65 лет и более - трем МРОТ.

На руки пенсионер получает базовую пенсию плюс надбавку минус взнос в страховой фонд.

Требования к решению:

• Каждая строка обязательно содержит следующую информацию:

• Изменение базовой пенсии и МРОТ и коэффициентов для взносов автоматически ведет к изменению всех величин.

Вычислить:

• общую сумму и сумму на руки по собесу;

• отдельно сумму и сумму на руки для лиц в возрасте до 65 лет и • отдельно по группам по величине стажа.

Построить круговую диаграмму для иллюстрации доли сумма на руки по группам по величине стажа.

Рекомендации:

- хранить МРОТ и ставки (коэффициенты до 65 лет и свыше) в отдельных ячейках;

- для начисления надбавок завести справочник, в котором вход - “стаж”, а выход - величина коэффициента для начисления надбавки (0; 25; 50 и Составить таблицу, которая позволяет автоматизировано составить ведомость на выплату премиальных спортсменам олимпийцам ЦОП “ Железный кулак”.

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

• каждый спортсмен участвует в одном виде соревнований;

• премиальные выплачиваются спортсмену как за каждую завоеванную медаль (первые три места), так и за принесенные очки в общекомандный зачет (за места с 1 по 4); за последующие места • за первое место (золотую медаль) начисляют 1000 USD и 8 очков в общий зачет; за второе место (серебряную медаль) - 700 USD и очков, за третье место (бронзовую медаль) - 500 USD и 3 очка; за Требования к решению:

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

- фамилию спортсмена;

- специализация;

- завоеванное место;

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

- заработанные спортсменом суммы;

- и возможно какую-либо другую информацию.

Изменение стоимости медали в очках и условных единицах, а также курса USD автоматически ведет к изменению суммы вознаграждения.

Курс USD 30руб. и может меняться.

Окончательный результат расчетов — в рублях.

Общее число спортсменов, принявших участие в соревнованиях 12человек.

Вычислить:

- общую сумму очков и денежного вознаграждения по Центру;

- отдельно суммы очков и вознаграждения для каждого из видов.

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

Рекомендации:

• хранить курс USD в отдельных ячейках;

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

Составить таблицу, которая позволяет автоматизировано составить ведомость на начисление премии рабочим.

Две бригады рабочих изготовляет детали трех видов (А, В, С).

Стоимость одной детали вида А - 10 USD, вида В - 20 USD, вида С - 15 USD.

Каждый рабочий производит детали одного вида.

Общее количество работников 12-14 чел.

Считать, что начисление премии происходит по следующему принципу: премия начисляется, если изготовлено деталей на сумму больше 2000 USD в размере 10% от этой суммы для рабочих первой бригады и 12% для рабочих второй бригады.

Требования к решению:

• Каждая строка таблицы обязательно содержит следующую - количество деталей, изготовленных рабочим;

• Размер премии должен быть выражен в рублях.

• Изменение стоимости каждой детали, изменение курса доллара, и перевод работника в другую бригаду автоматически ведет к изменению всех расчетов.

Обеспечить подведение итогов: подсчитать общую сумму премий и сумму премий по каждой бригаде.

Построить круговую диаграмму для иллюстрации доли премий для первой и второй бригады.

Рекомендации:

• хранить курс доллара в отдельной ячейке;

• в отдельных ячейках хранить размер премиальных для каждой • для вычисления стоимости изготовленных деталей завести справочник, в котором вход - вид детали, выход - ее стоимость.

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

Несколько фирм (12-14), входящих в объединение, закупают оборудование трех видов. Фирмы могут быть двух типов - совместные и российские. Каждая фирма закупает оборудование одного вида. При закупке оборудования на определенную сумму фирма получает скидку.

Стоимость единицы закупленного оборудования 1-го типа - 1000 USD, 2-го 500 USD, 3-го 250 USD.

При покупке оборудования на сумму свыше 10000 USD для российских фирм действует скидка в размере 10% от общей стоимости, а для совместных 5%.

Требования к решению:

Каждая строка таблицы содержит следующую информацию:

- название фирмы;

- вид закупленного оборудования;

- количество единиц оборудования;

- стоимость;

- стоимость с учетом скидки.

Подсчеты вести в рублях.

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

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

Построить круговую диаграмму, отражающую долю от общей стоимости совместных и российских фирм.

Рекомендации:

• хранить курс доллара в отдельной ячейке;

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

В двух страховых компаниях "Русский мир" и "Росно" работает 12 - агентов, которые заключают договора трех типов (А, В, С): на 5 000 USD, на 1 000 USD и на 500 USD. Каждый агент заключает договора одного типа.

Если агент работает в первой компании, то его заработок составляет 10% от общей суммы заключенных договоров, а если во второй компании При заключении договоров на сумму свыше 10 000 USD дополнительно начисляется премия в размере 5% от общей суммы.

Требования к решению:

Каждая строка содержит следующую информацию:

- фамилия агента;

- название компании;

- вид, заключаемого договора;

- количество заключенных договоров;

- заработок агента.

Подсчеты вести в рублях.

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

Обеспечить подсчет суммарного заработка с учетом премии для всех агентов и отдельно для агентов первой и второй компаний.

Построить круговую диаграмму, отражающую долю от общего заработка агентов 1-ой и 2-ой компаний.

Рекомендации:

• хранить курс доллара в отдельной ячейке;

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

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

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

Стоимость путевки определяется как произведение базовой стоимости 1 дня на длительность заезда с учетом категории и скидки.

Базовая стоимость путевки - 10 у.е./день.

Сотрудникам предлагаются путевки трех категорий:

• для взрослых - 100% базовой стоимости;

• для детей - 60% базовой стоимости;

• семейная (2 чел) - 175% базовой стоимости.

Величина скидки на путевку зависит от длительности заезда:

Требования к решению:

Все промежуточные расчеты вести в у.е., итоговые - в рублях.

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

Отобразить в таблице сведения:

- ФИО сотрудника;

- категория путевки (взрослая, детская, семейная);

- длительность заезда;

- стоимость путевки со скидкой.

Вычислить:

• стоимость заказа для профсоюзной организации с учетом скидки;

• стоимость заказа по категориям.

Построить круговую диаграмму для иллюстрации суммы заказов по различным категориям путевок.

Рекомендации:

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

Составить таблицу, позволяющую автоматизировано рассчитывать квартплату квартиросъемщиков.

Расчет квартплаты P осуществляется по формуле:

S - жилая или общая площадь, B - базовая стоимость одного квадратного метра, k -повышающий коэффициент за качество жилья.

Квартплата начисляется за каждый квадратный метр общей площади, если квартира отдельная, и за каждый квадратный метр (1м2) жилой площади, если квартира коммунальная.

Повышающий коэффициент - k равен:

• 5 - для домов дореволюционной постройки после капитального • 3,5 - для домов “сталинской” постройки, • 2,8 - для кирпичных домов современной постройки, ремонтировавшихся, • 1- для современных блочных домов.

Базовая стоимость одного квадратного метра B общей площади в отдельной квартире равна 240 р., а жилой площади в коммунальной квартире - 320 р.

Базовая стоимость 1м2 и коэффициенты могут меняться.

Требования к решению:

Каждая строка должна содержать следующую информацию:

- категорию дома (которая определяет величину повышающего - тип квартиры (коммунальная или отдельная);

- начисленную квартплату;

- возможно какую-либо дополнительную информацию.

Для расчетов в таблице данные задать самостоятельно.

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

Вычислить:

• общую сумму квартплаты в данном списке;

• сумму квартплаты по категориям домов;

• общую сумму квартплаты в данном списке.

Построить круговую диаграмму, показывающую распределение квартплаты по категориям.

Рекомендации:

• базовые стоимости 1м2 B хранить в отдельных ячейках;

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

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

Считать, что результатом тестирования является количество неправильных ответов КОЛ_НЕПР из общего количества вопросов КОЛ_ВОПР. Определение оценки производится следующим образом:

Таблица оценок тестирования содержит следующие столбцы:

• общее количество вопросов (КОЛ_ВОПР);

• количество неправильных ответов (КОЛ_НЕПР);

• процент правильных ответов;

• оценка (определяется по справочной таблице).

Справочная таблица содержит два столбца (рис.5.11) - "Процент правильных ответов" и "Оценка".

Процент правильных ответов для каждого студента рассчитывается по формуле: 100*(КОЛ_ВОПР-КОЛ_НЕПР)/КОЛ_ВОПР.

Вычислить:

• средний балл по всему списку (целесообразно использовать • количество студентов, получивших оценки 2, 3, 4 и (целесообразно использовать функцию СЧЁТЕСЛИ).

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

Требования к решению:

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

6. ОБРАБОТКА СПИСКОВ

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

Термины "база данных" и "список" фактически являются синонимами.

Однако далее будем придерживаться принятого в Microsoft соглашения и называть структурированные таблицы в документах MS Excel списками.

Базой данных будем называть файлы таблиц, созданные другими системами обработки данных, такими как Microsoft Access, dBase или FoxPro.

Каждая таблица имеет строки, которые также называются записями, и столбцы, которые называются полями. Каждое поле заполнено данными одного типа, поэтому все записи в списке имеют одинаковую структуру.

Работа со списками в MS Excel, как и работа с любыми наборами данных, сводится к некоторому набору стандартных операций:

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

1.3. Корректировка (внесение изменений) в уже существующие записи.

2. Сортировка записей по какому- либо признаку.

3. Отбор записей по какому-либо критерию.

4. Подведение итогов.

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

1. Каждый столбец должен содержать однородную информацию.

2. Одна или две верхние строки в списке должны содержать метки, описывающие назначение соответствующего столбца.

3. Необходимо избегать пустых строк и столбцов внутри списка.

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

Правило 2 обеспечивает присвоение имен полям. Эти имена постоянно используются при обработке списков.

Правило 3 обеспечивает возможность работы со списком как с единым целым. В идеале на рабочем листе не должно быть ничего, кроме списка.

Если это невозможно, то список нужно отделить от других данных по крайней мере одной пустой строкой и пустым столбцом.

На рис.6.1 приведен список из 10 столбцов.

Новые данные можно добавлять непосредственно в конец списка.

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

Чтобы присвоить имя списку:

1. Выделите весь список, включая заголовки столбцов;

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

Присвоение списку имени и его автоматическое переопределение по мере расширения списка удобно во многих случаях.

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

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

Продемонстрируем сортировку на списке рис.6.1. Нужно отсортировать список по столбцу Бригада. Для этого:

1) выделите одну ячейку (не интервал) в этом списке;

2) выполните команду Сортировка (Вкладка Данные, группа Сортировка и фильтр);

3) откроется диалоговое окно Сортировка (рис.6.2);

4) выберите поле, по которому нужно сортировать (в этом примере Бригада).

Рекомендуется сразу же проверять результат сортировки. Если результат не устраивает, воспользуйтесь командой Отменить и восстановите предыдущий порядок строк в списке. Для восстановления исходного порядка строк в списке после различных сортировок, необходимо до сортировки создать столбец с номерами строк. В нашем примере это столбец №пп. Это позволяет восстановить первоначальный порядок строк, отсортировав список по этому столбцу.

Пользуясь командой Сортировка..., можно сортировать список по нескольким столбцам. Для этого необходимо использовать вкладку Добавить уровень в диалоговом окне Сортировка (рис.4.2).

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

6.4. АНАЛИЗ СПИСКА С ПОМОЩЬЮ ФИЛЬТРОВ

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

Для применения обычного или автофильтра нужно выполнить следующую последовательность действий:

1) выделите какую-либо ячейку в списке;

2) нажать кнопку Фильтр в группе Сортировка и фильтр Справа от каждого заголовка столбца появиться кнопка "Раскрывающийся список" (со стрелкой вниз). Если щелкнуть по этой кнопке, то раскроется список уникальных значений данного столбца, которые можно использовать для задания критерия фильтра. На рис 6. показан результат фильтрации по столбцу Бригада, выбраны только те строки, где значение Бригада равно 21.

Номера строк, не удовлетворяющие критериям команд Фильтр (Автофильтр) и Расширенный фильтр, MS Excel просто скрывает. Номера отфильтрованных строк выводятся контрастным цветом, а в строке состояния появляется сообщение Найдено записей.

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

Критерий заданного фильтра на рис.6.3 фактически состоит из одного равенства Бригада=21. Можно создать автофильтр с более сложным критерием, состоящим из одного или двух условий с любыми знаками сравнения. Например, следует отфильтровать значения, находящиеся в некотором интервале. Чтобы создать пользовательский автофильтр, раскройте список критериев и выберите элемент Настраиваемый фильтр...

(рис.6.4.а).

Откроется диалоговое окно Пользовательский автофильтр (рис 6.4.б). В этом диалоговом окне можно создать два условия, соединяемое логическими условиями И или ИЛИ.

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

Предположим, что в нашем списке требуется посмотреть только записи со значением столбца Бригада, лежащим в интервале от 9 до 14. Т.е.

значение Бригада больше или равно 9 И меньше или равно 14. Эти неравенства заданы в окне рис 4.5.б, условия соединены знаком логической операции И.

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

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

Т.к. отдельных букв С и Т в раскрывающемся меню нет, их надо просто ввести в соответствующие поля диалогового окна (рис. 6.4.б).

Та же задача может быть решена другим способом: по полю ФИО в диалоговом окне Пользовательский автофильтр следует создать два условия, соединенные логическим ИЛИ При создании критериев можно использовать два символа шаблона:

звездочка (*) и вопросительный знак (?).

последовательности символов.

Символ ? - для представления любого отдельного символа.

В таблице 6.1 даны примеры использования символов шаблона, удовлетворяющих таким критериям (пропускаемых фильтром):

Допустимы любые комбинации символов шаблона.

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

Команда Расширенный фильтр позволяет выполнять следующие операции:

создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ.

создавать критерии с тремя или более условиями для заданного столбца, связанными по крайней мере одним союзом ИЛИ.

создавать вычисляемые критерии.

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

Команда Расширенный фильтр в отличие от команды Автофильтр требует задания критерия в отдельном интервале рабочего листа. Разместить его лучше выше или ниже списка.

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

Пример критерия с двумя условиями Пусть из списка персонала, приведенного на рис 6.1, нужно выделить как работников с тарифом (столбец Тариф) выше 40, так и работников, отработавших более 100 час (столбец Отработано час.).

Выполним следующие действия:

Вставим несколько строк для интервала критериев выше списка.

Создадим интервал критериев в первых трех строках, как показано на рис. 6.5.

В диалоговом окне Расширенный фильтр(кнопка Дополнительно из группы Сортировка и фильтр) введем параметры в соответствии с рис Убедитесь, что установлен переключатель фильтровать список на месте, щелкните ОК Результат фильтрации приводится на рис.6.6.

По команде Расширенный фильтр, также как и по команде Автофильтр скрываются все строки, не удовлетворяющие критериям фильтра, выводятся номера строк в контрастирующем цвете, а в строке состояния выводится сообщение Фильтр: отбор.

На рис.6.6 в ячейках А1:В3 критерии записаны в виде обыкновенных меток. Под заголовком Тариф введено >40, под заголовком Отработано час. введено >100.

Обратите внимание, что критерии в данном примере располагаются на отдельных строках.

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

Условия на одной строке объединяются по правилу И Условия на отдельных строках объединяются по правилу ИЛИ.

Пример критерия с условиями, объединяемыми по правилам И и ИЛИ одновременно.

На рис.6.7 показан критерий и результат фильтрации для решения следующей задачи. Показать всех работников, фамилия которых начинается на букву “П“, у которых Сумма больше 3000 или Разряд выше 4.

Буква П присутствует в критерии в обеих строках поскольку записи, удовлетворяющие как условию Сумма> 3000, так и условию Разряд >4, должны одновременно удовлетворять критерию на фамилию (первая буква П).

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

Если задана одна буква, то по равенству (=) будут найдены все начинающиеся на эту букву значения. Например, по критерию =М будут найдены Мария, Максим, М.Шолохов и т.п.

По условию больше (>) или меньше ( М в столбце ФИО будут выделены работники с фамилиями, начинающимися с букв от М до Я.

Противоположный критерий $J$1 (Ссылка на ячейку вне списка абсолютная!) 7) Поставьте курсор на любую ячейку внутри списка.

8) В диалоговом окне Расширенный фильтр в качестве критерия (поле Диапазон условий) введите $A$1:$A$2, затем нажмите кнопку ОК.

В результате на рабочем листе отображается список сотрудников, чей средний заработок выше, чем у членов бригады 21 (рис.6.8).

Рассмотренный пример иллюстрирует следующее:

• Заголовок интервала критерия не совпадает ни с одним заголовком в списке, иначе фильтр работал бы неправильно.

• В формуле только ячейка J6 (адрес первой записи) сравнивается c J1, но при обработке фильтра сравнение выполняется последовательно для всех ячеек столбца J, начиная с первой ячейки J6 и до конца списка.

• Ссылка на ячейку J1 абсолютная. Если в ячейке задать формулу =J6>J1 с относительной ссылкой, то ячейка J6 сравнивается с ячейкой J1, ячейка J - с ячейкой J2 и т.д., что очевидно неправильно.

На значение, возвращаемое формулой критерия в ячейке А2, можно не обращать внимания. В данном случае значение ЛОЖЬ соответствует тому, что заработок первого работника меньше содержимого ячейки J1.

Пример 1. Для списка, приведенного на рис 6.1, требуется найти работников, отработавших в среднем больше 8 часов в день.

Решение.

Разделим число отработанных часов на количество рабочих дней (23) и поместим в формулу критерия:

=I6/23> В формуле используются относительные ссылки.

Пример 2. Пусть в интервале ячеек А6:С13 задан список, содержащий информацию о наличие оборудования в некоторых аудиториях (рис.4.10).

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

Решение.

Критерий показан на том же рисунке, он задан формулой =B7>C7, содержащейся в ячейке А2, ссылки на ячейки относительные. Это обеспечивает перебор всех значений списка, т.е. на втором шаге сравнивается В8 и С8, на третьем В9 и С9 и т.д. до конца списка.

Отображаемое значение в ячейке А2 - "ИСТИНА" является результатом сравнения ячеек В7 и С7.

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

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

Для этого необходимо выполнить следующие действия:

1) Отсортировать таблицу по столбцу 2) Поместить курсор на любую ячейку внутри списка.

3) Выбрать команду Данные >Итоги… 4) В диалоговом окне Промежуточные итоги установите необходимые параметры (рис.6.11). После этого нажмите кнопку В результате MS Excel создает новые строки промежуточных результатов и структуру таблицы (рис.6.12). Слева выведены символы структуры- кнопки со знаками плюс и минус. Если щелкнуть мышью по кнопке со знаком минус, то соответствующая область таблицы будет скрыта.

Когда соответствующая часть таблицы скрыта, то линия структуры, которая указывает на эту часть таблицы “схлопнута”, а на кнопке появляется знак плюс. Если щелкнуть по кнопке со знаком плюс, то скрытая часть таблицы будет вновь на экране.

Используя символы уровня строки (кнопки помеченные цифрами 1,2,3), можно просмотреть итоговую таблицу с различной степенью детализации. При нажатой кнопке 1 будет показана таблица с указанием каждого работника, при нажатой кнопке 2 будут показаны итоги по полю Бригада, при нажатой кнопке 3 - только общие итоги.

По индивидуальному варианту заполнить содержательной информацией предложенную таблицу. Заменить формальные обозначения в критериях отбора (X,Y,Z,..) на фактические таким образом, чтобы не было «пустых» выборок. При создании таблицы можно ограничиться 20 записями.

Сформулировать постановку задачи, то есть подробно ответить на вопросы:

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

Сформировать и обработать базу данных в Excel.

• Работа с фильтрами (автофильтр и расширенный фильтр) • Результаты обработки списков Информация о сотрудниках (отдел кадров).

• Фамилия • Год рождения • Образование • Дата поступления на работу Подготовить документы, содержащие сведения:

1) о сотрудниках с высшим образованием моложе X лет;

2) о женщинах, поступивших на работу после Y года;

3) о сотрудниках со средним или неполным высшим образов Информация о жилом фонде кооператива • № квартиры • Фамилия И.О. владельца(ответственного квартиросъемщика) • Общая площадь • Число проживающих • Наличие телефона Подготовить документы, содержащие сведения о:

квартирах с общей площадью более X м2, 2) квартирах с телефоном, расположенных не на первом этаже 3) о квартирах, в которых на каждого проживающего приходится меньше Y Информация о студентах (отдел кадров) • Фамилия И.О.

• Факультет • Группа • Сколько детей Подготовить документы:

1) о студентах, имеющих детей;

2) ведомость для получения материальной помощи на детей ( один МРОТ на одного ребенка) для студентов Х факультета.

3) о студентах факультетов Y и Z, не имеющих детей.

Инвентаризация помещений.

• N аудитории • Число мест • Число стульев • Число столов • Принадлежность (общая или название кафедры) • Наличие доски Предоставить следующую информацию:

1) какие аудитории на Х мест имеют Х стульев;

2) какие общие аудитории с досками на Y мест;

3) какие аудитории полностью укомплектованы мебелью (доска, число стульев не меньше числа мест, число столов не меньше половины мест).

Туристические маршруты • Номер маршрута • Турбаза • Длительность маршрута (в днях) • Стоимость • Наличие путевок Подготовить документы содержащие сведения о:

1) наличии путевок на турбазу Х длительностью не более Y дней;

2) наличии путевок не дороже Z рублей для группы 12 человек;

3) наличии путевок, для которых стоимость 1 дня маршрута меньше средней стоимость 1 дня по всем маршрутам.

Поставка комплектующих изделий • Номер договора • Наименование изделия • Объем поставки • Сумма договора • Предприятие-поставщик Подготовить документы со сведениями о:

1) договорах на поставки Х изделия с Y предприятия-поставщика;

2) договорах по мелким поставкам (меньше Z штук);

3) общей сумме договоров с W предприятием-поставщиком.

Список абонентов телефона • Фамилия И.О.

• Номер телефона • Год установки • Тип установки (индивидуальный, коллективный, блокированный) • Число аппаратов Представить информацию:

1) об абонентах, имеющих блокированный телефон с более, чем одним аппаратом.

2) об абонентах, установивших телефон после Y года 3) об абонентах, номер телефона которых начинается на YZ.

Личная коллекция книголюба.

• Шифр книги • Название • Год издания • Местонахождение (шкаф, полка - номера) Предоставить информацию о:

1) местонахождении книги автора Х названия Y;

2) книгах автора Z, изданных до W года;

3) список книг автора V.

Наличие билетов на самолет • Номер рейса • Пункт назначения • Компания • Время вылета • Время прибытия • Наличие билетов Предоставить информацию о:

1) о всех рейсах в пункт Х;

2) о рейсах Y компании, на которые имеются билеты;

3) на каких рейсах в пункт W есть в наличии билеты на группу из человек.

Наличие железнодорожных билетов Поля:

• Номер поезда • Станция назначения • Время отправления • Время в пути • Наличие мест Представить информацию:

1) о поездах, отправляющихся в пункт X в интервале до Z часов и после Y часов;

2) о поезде до пункта W с минимальным временем в пути;

3) о поездах до пункта V с наличием свободных мест не менее 100.

СПИСОК ЛИТЕРАТУРЫ

1. Беляев В.В., Виноградова Т.А, Косовцева Т.Р. Информатика. Метод.

указания по выполнению контрольных работ для студентов заочной формы обучения. – СПб.: СПГГИ(ТУ), 2004.-40 с.

2. Быкова Е.И.. Информатика. Основы работы в EXCEL. Методические указания к лабораторным занятиям.- СПб:СПГГИ(ТУ), 2006. -70 с.

3. Златопольский Д.М.. 1700 заданий по Microsoft Excel. – СПб.: БХВПетербург, 2003.-544 с.

4. Информатика. Базовый курс. 2-е издание / Под ред. С.В. Симоновича. – СПб.: Питер, 2005.-640 с.

5. Информатика. Практикум по то технологии работы на компьютере / Под ред. проф. Н.В. Макаровой. Изд. 3-е, перераб. М.: Финансы и статистика, 2004.-256 с.

6. Информатика. Учебник / Под ред. проф. Н.В. Макаровой. Изд.3-е, перераб. М.: Финансы и статистика, 2008.–768 с.

7. Рудикова Л.В.. Microsoft Excel для студента. – СПб.: БХВ-Петербург,