Указания по выполнению заданий контрольной работы в процессоре MS Excel

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

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего Рабочий лист может содержать до 16384 столбцов, пронумерованных от A до XFD. Строки последовательно нумеруются цифрами от 1 до 1048576.

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

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

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

Например: СУММ(А1:А10)

где СУММ –имя функции;

(А1:А10) – аргумент, записанный в виде диапазона клеток.

Для вызова функции нужно перейти на ленту инструментов Формулы (рис. 39) и использовать пиктографическое меню в блоке Библиотека функций (рис. 40). В блоке инструментов раскрыть необходимое пиктографическое меню со списком функций (рис. 41) или нажать кнопку Вставить функцию (рис. 42), выполняя два шага:

· выбор имени функции;

· оформление аргумента функции.

Рис. 39. Лента инструментов Формулы

Рис. 40. Библиотека функций на ленте Формулы

Формула в электронных таблицах – это совокупность арифметических операций, адресов клеток и обращений к функциям. Формула имеет свой признак (=). Ввод формулы начинается с признака.

Рис. 41. Пиктографическое меню библиотеки логических функций

Рис. 42. Кнопка Вставить функцию на ленте Формулы

Например: =А1*2

Знаки арифметических операций:

+ – сложение;

– – вычитание;

* – умножение;

/ – деление;

^ – возведение в степень;

() – повышение приоритета операции.

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

В формулах электронной таблицы применяются два типа адресов клеток:

· абсолютный адрес;

· относительный адрес.

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

Относительный адрес – адрес, которыйпри копировании формул корректируется (В4).

Рабочий лист. После запуска Ехсеl на экране появляется пустой рабочий лист. Рабочий лист представляет собой сетку, образованную столбцами и строками. Пересечение столбцов и строк создает прямоугольник, называемый ячейкой. На каждом рабочем листе имеется 16384 столбцов и 1048576 строк. Столбцы рабочих листов имеют заголовки, обозначаемые буквами латинского алфавита, а строки листов определяются цифрами. Каждая ячейка имеет адрес, в котором сначала идет буква столбца, а затем номер строки на пересечении которых находится ячейка. Например, в верхнем левом углу рабочего листа находится ячейка с адресом А1.

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

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

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

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

2. Значения. К значениям относятся данные – числа, дата и время. Надтакими данными можно выполнять расчеты.

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

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

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

Работа с формулами. С помощью Ехсеl можно быстро создать формулу простым выбором необходимых значений или аргументов с добавлением соответствующих операторов. Формула состоит из трех основных частей: знака равенства (=), совокупности значений или ссылок на ячейки, с которыми выполняются расчеты, и операторов. Запись всех формул начинается со знака равенства. Рассмотрим простейшую формулу, записанную в ячейку А1: =В1+С1

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

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

1. Все операции в круглых скобках.

2. Показательные операторы.

3. Умножение и деление.

4. Сложение и вычитание.

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

Диапозоны ячеек и операторы ссылок. Диапазон –это прямоугольная область с группой связанных ячеек, объединенных в столбец, строку, сочетание столбцов и строк. Особенно удобно использовать диапазоны в формулах. Вместо ввода в формулу ссылок на каждую ячейку, можно указать диапазон ячеек. В программе Ехсеl диапазон указывают ссылкой на адрес диапазона. Ссылка описывает конкретные точки привязки: верхнюю левую и правую нижнюю ячейки диапазона. Например, в диапазон А1:А5 входят ячейки А1, А2, АЗ, А4 и А5, а в диапазон А1: ВЗ ячейки А1, А2, АЗ, В1, В2, ВЗ.

Наиболее часто диапазоны указывают при записи различных функций. Например, чтобы записать формулу сложения чисел из ячеек от А1 до А5, мы можем записать ее через операторы =(А1+А2+АЗ+А4+А5) или с помощью функции суммирования =СУММ(А1;А2;АЗ;А4;А5;). Здесь используется оператор объединения – в данном случае точка с запятой (;), который позволяет соединять ячейки и обрабатывать их как целое. В формуле =СУММ(А1:А5) указан диапазон из пяти ячеек и используется оператор диапазона – в данном случае двоеточие (:).

Чтобы сложить все данные, содержащиеся, например, в диапазоне СЗ:С20 и в отдельной ячейке В10 можно записать формулу =СУММ (СЗ:С20;В10), которая к диапазону ячеек присоединяет отдельную ячейку.

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

=СУММ(А1:А5 А1:СЗ)

Оператор пересечения обрабатывает общие ячейки этих диапазонов – в данном случае – А1, А2, АЗ.

Абсолютные и относительные адреса ячеек. Для упрощения копирования и перемещения формул в Ехcеl применяется концепция, известная как относительная адресация, т.е. адреса ячеек в формулах интерпретируются как указание на их относительное расположение. Например, формула =А1+А2, записанная в ячейку А3 интерпретируется как указание взять первое число из ячейки, лежащей на две строки выше и сложить его с числом из ячейки, лежащей выше на одну строку.

Если формулу из ячейки А3 скопировать в ячейку В5, то она автоматически превратится в формулу =ВЗ+В4, в которой используются ссылки на ячейки с аналогичным расположением. Однако в некоторых формулах необходимо сохранить ссылку на точно указанную ячейку. Ссылка на ячейку не должна изменяться от расположения формулы на листе. В таком случае применяют ссылку иного типа, именуемую абсолютной ссылкой. Абсолютная ссылка остается неизменной при копировании ячейки с формулой.

При установке абсолютной ссылки в адрес ячейки ставят знак $ перед адресом столбца и строки. Например, $C$10 – абсолютный адрес ячейки С10.

В некоторых случаях в формуле необходимо применить смешанную ссылку. Скажем, ссылка на строку должна быть абсолютной, на столбец – относительной. Например, в адресе С$6 ссылка на столбец относительная, а на строку – абсолютная. Напротив, в адресе $C6 ссылка на столбец абсолютная, а на строку – относительная.

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

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

Чтобы быстро отформатировать рабочий лист, достаточно ленты инструментов – Главная (рис. 43). Она обеспечивает быстрый доступ к командам изменения шрифта и его размера, к кнопкам изменения выравнивания, применения числовых форматов, заливки цветом, добавления границ и т. д.

Рис. 43. Лента инструментов Главная

Установка числовых форматов. Числовые данные вводятся в рабочий лист Ехсеl как простые числа, например 25. Но это число может означать что угодно. Чтобы числа имели смысл, им следует назначить соответствующий числовой формат. На ленте Главная в блоке инструментов Число (рис. 44), есть кнопки команд трех самых распространенных форматов: денежный, формат с разделителем и процентный.

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

Рис. 44. Блок инструментов Число ленты Главная

Рис. 45. Кнопка Увеличить разрядностьблока инструментов Число

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

Рис. 46. Кнопка раскрывающегося списка Границы

блока инструментов Число

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

Заливку ячеек и фонового узора можно выполнить и с помощью контекстного меню, выбором команды Формат ячеек (рис. 49) или на ленте Главная в блоке инструментов Ячейки в пиктографическим меню Формат (рис. 50). В появившемся диалоговом окне Формат ячеек (рис. 51) на вкладке Заливка можно выбрать узор заливки, цвет узора, фон заливки. Для подтверждения своего выбора нажмите на кнопку ОК. с помощью этого диалогового окна можно также изменять тип и цвет границ ячеек, менять написание, размер и начертание текста, применять выравнивание данных и менять формат ячеек.

Рис. 47. Цветовое оформление ячеек с помощью библиотеки стилей

Рис. 48. Цветовое оформление ячеек с меню Цвет заливки

Рис. 49. Выбор команды Формат ячеек в контекстном меню

Рис. 50. Выбор команды Формат ячеек

с помощью пиктографического меню Формат

Рис. 51. Выбор узора, цвета узора, фона узора

в диалоговом окне Формат ячеек

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

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

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

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

В Excel для этого предназначена кнопка Вставить функцию, которая расположена на ленте Формулы в блоке инструментов Библиотека функций (рис. 52). При нажатии на кнопку Вставить функцию откроется диалоговое окно Мастер функций (рис. 53), которое позволяет найти нужную функцию и получить по ней краткую справку. Аналогичный результат можно получить, установив в ячейке знак равенства =, и развернуть список функций, находящийся левее строки формул (рис. 54). После выбора функции откроется диалоговое окно Агруметы функции (рис. 55), в котором можно завершить построение функции, выбрав необходимые аргументы.

Рис. 52. Кнопка Вставить функцию на ленте Формулы

Рис. 53. Диалоговое окно Мастер функций

Рис. 54. Выбор функции из списка функций в раскрывающемся списке Функции

Рис. 55. Диалоговое окно Аргументы функций

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

Рассмотрим некоторые наиболее часто используемые функции этой категории:

= АВS(число) – функция АВS возвращает абсолютное значение единственного числового аргумента;

= СРЗНАЧ(число1; число2;…) – функция СРЗНАЧ возвращает среднее значение от ряда чисел;

= ПИ() – функция ПИ возвращает значение числа n с точностью до 15 знаков;

= ОКРУГЛ(число;число_разрядов) – функция ОКРУГЛ округляет число до указанного количества знаков;

= СУММ(число1;число2;…) – функция СУММ суммирует наборы данных;

= ПРОИЗВЕД(число1;число2;…) – функция ПРОИЗВЕД перемножает числа, заданные в качестве аргументов;

= СТЕПЕНЬ(число;степень) – функция СТЕПЕНЬ возвращает результат возведения в степень;

= КОРЕНЬ(число) – функция КОРЕНЬ вычисляет квадратный корень;

= СУММПРОИЗВ(массив1;массив2;массив3;…) – функция перемножает соответствующие элементы заданных массивов и возвращает сумму произведений;

= ЦЕЛОЕ(число) – округляет число до ближайшего меньшего целого;

= ОСТАТ(число;делитель) – возвращает остаток от деления числа на делитель;

= SIN(число) – возвращает синус заданного числа;

= COS(число) – возвращает косинус заданного числа;

= LOG10(число) – возвращает десятичный логарифм числа;

= ЕХР(число) – возвращает число е, возведенное в указанную степень.

Логические функции И, ИЛИ, НЕ. В программе Excel может использоваться логический тип данных. Эти данные могут принимать только два значения: ИСТИНА или ЛОЖЬ. В Excel можно использовать число 1 для обозначения значения ИСТИНА и 0 для значения ЛОЖЬ. Функция И, ИЛИ, НЕ взяты из математики, где существует раздел булевойалгебры, рассматривающий вопросы истинности различных высказываний. Логические функции принимают значения ИСТИНА или ЛОЖЬ, когда в задачах возникают условия, от которых зависит результат или ход решения. Например, деканат института решил премировать студентов пятого курса, проживающих в общежитии и имеющих отличные оценки. В качестве критериев отбора этой группы студентов служат три параметра, которые должны одновременно присутствовать в выборке: пятый курс, общежитие, отличные оценки. Условие выборки может быть реализовано логическими функциями И, ИЛИ, НЕ.

Функция И принимает значение ИСТИНА когда все ее аргументы – истинны. В случаях, когда один или несколько элементов имеют значение ЛОЖЬ функция И тоже имеет значение ЛОЖЬ. Например, запись =И("сентябрь";1;2+2=4), содержащая три аргумента, будет иметь значение ИСТИНА в сентябре и значение ЛОЖЬ в другие месяцы года, так как два других аргумента этой функции – 1 и 2+2=4 всегда истинны.

Функция ИЛИ принимает значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА. Например, результатом выражения =ИЛИ(х=5;у=7;z>=25) будет ИСТИНА, когда хотя бы один из аргументов х, у, zбудет соответствовать заданным отношениям.

Функция НЕ просто возвращает противоположное логическое значение аргумента. Например, функция =НЕ(2+2=4) будет всегда иметь значение ЛОЖЬ, а функция = НЕ(0) будет всегда ИСТИНА.

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

Логическая функция ЕСЛИ в общем случае состоит из трех аргументов: логического выражения, значения, если логическое выражение ИСТИННО и значения, если логическое выражение ЛОЖНО. Например, если ячейка С10 содержит фактические затраты, а ячейка D10 – запланированные, то формула =ЕСЛИ(С10>D10;"Превышение бюджета";"Все идет по плану") выведет на рабочий лист сообщение Превышение бюджета, если логическое условие С10>D10 ИСТИННО и Все идет по плану, если оно ЛОЖНО.

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

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

Функция СЕГОДНЯ() и ТДАТА() возвращают текущую дату и время, установленные в компьютере. Функция СЕГОДНЯ возвращает только дату, а функция ТДАТА возвращает текущую дату и время.

Функция ДАТА имеет синтаксис =ДАТА(год;месяц; день) и возвращает дату в числовом формате.

Функция ДНЕЙ360 имеет синтаксис = ДНЕЙ360(нач_ дата;кон_дата;метод) возвращает количество дней между двумя датами на основе 360-дневного года.

Функция ДАТАЗНАЧ имеет синтаксис написания =ДАТАЗНАЧ(дата_как_текст) возвращает числовой формат даты, представленной в виде текста.

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

Функция СЦЕПИТЬ используется для объединения текстовых строк в одну. Ее синтаксис имеет вид:

=СЦЕПИТЬ(строка1;строка2;...)

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

«Забайкальский институт предпринимательства».

Функция ЛЕВСИМВ возвращает указанное количество символов, начиная с левого края строки. Ее синтаксис имеет вид:

=ЛЕВСИМВ(текст;число_знаков).

Формула =ЛЕВСИМВ(А1;13) возвратит строку – Забайкальский.

Функция ПРАВСИМВ возвращает указанное количество символов, начиная с правого края строки. Ее синтаксис имеет вид:

=ПРАВСИМВ(текст;число_знаков).

Формула =ПРАВСИМВ(А1;19) возвратит строку – предпринимательства.

Функция ПСТР возвращает указанное количество символов, начиная с определенного места в строке. Ее синтаксис имеет вид:

=ПСТР(текст;начальная_ позиция;количичество_символов).

Формула =ПСТР(А1;14;9) возвращает строку – институт.

Примеры реализации заданий:

Работа со справочниками и сводными таблицами Excel

Основные функции Excel, используемые в работе:

Сумм(число1:числоN) – суммирует данные из диапазона;

СуммЕсли(диапазон;критерий;диапазон_суммирования) – просматривает ячейки из диапазона, и суммирует ячейки с тем же индексом из диапазона сумирования, если условие выполняется;

Если(логическое_выражение;значение_если_истинно; значение_если_ложь) – если логическое условие истинно, выполняется значение если истино, иначе значение ложь;

И(логическое_выражение1;логическое_выражение2;…) – истина, если все логические выражения истинны, иначе ложь;

Поискпоз(искомое_значение;просматриваемый_массив; тип_сопоставления) – ищет значение в массиве (0 – ищется точное совпадение) и возвращает номер строки, где оно встретилось;

Просмотр(искомое_значение;просматриваемый_вектор; вектор_результатов) – ищет значение в диапазоне просмотра и возвращает соответствующее ему значение из диапазона результатов;

Индекс(массив;номер_строки;номер_столбца) – выбирает значение элемента из массива по номеру строки и номеру столбца;

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просморт) – ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из указанного столбца массива;

ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр) – выполняет поиск значения в верхней строке таблицы или массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) значений и возвращает значение того же столбца в заданной строке таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые – на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, следует использовать функцию ВПР.

Буква Г в аббревиатуре «ГПР» означает «горизонтальный».

Задача на сортировку, выборку и функции Если, Месяц, День

БД городской телефонной сети. Основная таблица содержит поля – фамилия, адрес, номер телефона, дата последней оплаты. Таблица сортируется по фамилиям. Вторая таблица содержит ту же самую информацию, но упорядочена по номерам телефонов. Разработать две квитанции для расчета оплаты по заданному номеру телефона и по фамилии. В квитанции вводится месяц, включая который производится оплата. Учитывается пеня, если оплата производится после 10 числа текущего месяца.

На листе «Таблица 1» (рис. 56) абоненты упорядочены по номеру телефона, на листе «Таблица 2» (рис. 57) – по фамилии. Так как расчетные формулы однотипны, ниже приводятся комментарии для первого листа. Номера строк и столбцов листа приведены непосредственно в документе Excel.

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

Рис. 56. Лист Таблица 1

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

=ПРОСМОТР(C5;C21:C1000;A21:A1000)

Здесь C5 – значение искомой строки (номера телефона), C21:C1000 – диапазон просмотра базы данных, A21:A1000 – диапазон, из соответствующей ячейки которого берется результирующее значение.

Для удобства в ячейке С9 выводится дата последнего взноса платы абонентом. Аналогично, поиск реализуется по формуле:

=ПРОСМОТР(C5;C21:C1000;D21:D1000)

Рис. 57. Лист Таблица 2

Сумма оплаты рассчитывается в ячейке С11 по следующей формуле:

=ЕСЛИ(МЕСЯЦ(C9)>=C6;0;(C6-МЕСЯЦ(C9))*C16+ ЕСЛИ(И(ДЕНЬ(C9)>10;МЕСЯЦ(C9)<=C6);C17;0))

Здесь МЕСЯЦ(C9), ДЕНЬ(C9) – месяц и день, когда абонент последний раз вносил плату, С16 – ячейка со значением помесячной платы, С17 – ячейка со значением пени.

Если месяц, когда абонент вносил плату больше либо равен текущего месяца – результат формулы равен 0. Иначе плата вычисляется по формуле:

(Месяц, по который оплачиваем – Текущий месяц) *

*Помесячная плата + Пеня.

Пеня вычисляется по следующему правилу:

ЕСЛИ(И(ДЕНЬ(C9)>10;МЕСЯЦ(C9)<=C6);C17;0))

Если день, когда абонент последний раз вносил плату, больше 10 и месяц, до которого вносилась плата, не превышает текущего, то начисляется пеня, иначе ее значение равно 0.

Построение графика функций

1. Создайте новый лист Excel и назовите его Построение графика функции.

2. В ячейки столбца А, начиная с первой, введите значения констант. В следующую ячейку этого столбца введите численное значение шага. В ячейку В1 – начало отсчета.

3. В ячейку В2 введите формулу изменения шага в виде = В1 + А$6, где А$6 – ячейка, в которую введено значение шага. В ячейку С1 введите формулу вычисления функции, в виде =А$1*(A$2+B1), заметьте, что все значения, кроме В1, пишутся через $, поскольку В1 – относительный адрес, а А$1 и A$2 – абсолютные адреса.

4. Копируйте ячейку В2 в столбец В от В3 до конца вашего диапазона (в зависимости от значения, указанного в таблице), а С1 – в столбец С от С2 до конца интервала соответственно.

5. На ленте Вставка в группе Диаграммы выберите тип График и вид – График с маркерами (рис. 58).

Рис. 58. Выбор графика с маркерами

в группе Диаграмма ленты Вставка

6. После выбора вида графика появится дополнительная лента Конструктор. В группе инструментов Данные необходимо нажать кнопку Выбрать данные (рис. 59).

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

Рис. 59. Кнопка Выбрать данные на ленте Конструктор

Рис. 60. Выбор данных в диалоговом окне Выбор источника данных

8. С помощью дополнительной ленты Макет (рис. 61) оформите название диаграммы, названия осей, легенду для диаграммы, подписи данных и другое.

Рис. 61. Оформление диаграммы с помощью ленты Макет

9. На дополнительной ленте Формат с помощью блока инструментов Стили фигур (рис. 62) поменяйте заливку фигуры, контур фигуры и настройте эффекты для фигуры.

Рис. 62. Оформление диаграммы с помощью ленты Формат

Поиск решения

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

Рис. 63. Команда Поиск решения на ленте Данные

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

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

Ограничения вводятся в поле Ограничения при нажатии на кнопку Добавить, после чего откроется диалоговое окно Добавление ограничения (рис. 65). Ограничения указываются в виде:

Зависимая ячейка Знак Выражение,

где Знак может быть <=, =, >= или ограничение до целого числа, если задача целочисленна.

Рис. 64. Диалоговое окно Поиск решения

Рис. 65. Диалоговое окно Добавить ограничения

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

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

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

Пример: определить длины сторон a, b, h прямоугольного бака заданного объема V, минимизируя длину сварного шва, которая вычисляется по формуле: L=2(a+2b)+h. Математическая модель этой задачи вместе с ограничениями такова: L→min – оптимизируемая функция; V=const; a,b,h>0 – ограничения.

Рис. 66. Диалоговое окно Параметры поиска решения

Рис. 67. Диалоговое окно Результаты поиска решения

Решение: Введем начальные значения зависимых переменных а=1, b=1, c=1 в ячейки B3, C3, D3, а ограничение для них – число 0 – в ячейку B4. Зависимую переменную V=a*b*c введем в ячейку C7 в виде формулы =B3*C3*D3, а ограничение на нее в виде значения 2 – в ячейку E7. Целевую формулу для вычисления L в виде =2*(B3+2*C3)+D3 введем в ячейку C8. После этого в окне Поиск решения укажем $C$8 в качестве целевой ячейки, выберем поиск минимального значения, в поле «Изменяя ячейки» укажем ссылку $B$3:$D$3, в поле ограничения введем ограничения вида $B$3>=$B$4, $C$3>=$B$4,$D$3>=$B$4, $C$7=$E$7 (рис. 68). Применим к ячейкам B3, C3, D3 и C8 числовой формат ячеек с двумя знаками после запятой. Найденное решение должно быть таким: A=1,26, B=0,63, C=2,52 (рис. 69).

Рис. 68. Таблица значений и диалоговое окно Поиск решения

Рис. 69. Результатырешения задачи

Дата добавления: 2014-11-16