Опорный конспект лекции ффсо пгу 18. 2/05 Министерство образования и науки Республики Казахстан
| Вид материала | Конспект |
- Опорный конспект лекции фсо пгу 18. 2/07 Министерство образования и науки Республики, 1449.98kb.
- Опорный конспект лекции фсо пгу 18. 2/07 Министерство образования и науки Республики, 337.81kb.
- Опорный конспект лекции фсо пгу 18. 2/07 Министерство образования и науки Республики, 909.59kb.
- Опорный конспект лекции фсо пгу 18. 2/07 Министерство образования и науки Республики, 290.94kb.
- Опорный конспект Форма ф со пгу 18. 2/05 Министерство образования и науки Республики, 856.54kb.
- Методические указания Форма ф со пгу 18. 2/05 Министерство образования и науки Республики, 98.43kb.
- Методические указания Форма ф со пгу 18. 2/07 Министерство образования и науки Республики, 249.4kb.
- Рабочая программа ф со пгу 18. 2/06 Министерство образования и науки Республики Казахстан, 295.37kb.
- Методические указания ф со пгу 18. 2/05 Министерство науки и образования Республики, 126.96kb.
- Программа дисциплины ф со пгу 18. 2/07 для студентов Министерство образования и науки, 280.24kb.
| Опорный конспект лекции |  | ФФСО ПГУ 7.18.2/05 |
Министерство образования и науки Республики Казахстан
Павлодарский государственный университет им. С. Торайгырова
Кафедра информатики и информационных систем
ОПОРНЫЙ КОНСПЕКТ ЛЕКЦИИ
по дисциплине Программные средства экономических расчетов
для студентов специальности 050703 «Информационные системы»
Павлодар
Лист утверждения опорного конспекта лекции
Ф СО ПГУ 7.18.1/05
УТВЕРЖДАЮ
| Лист утверждения опорного конспекта лекции | | Ф СО ПГУ 7.18.1/05 |
Декан факультета ФМиИТ
___________ С.К.Тлеукенов «___»________________200_г.
Составитель: Преподаватель Аканова А.С
Кафедра информатики и информационных систем
Опорный конспект лекции
по дисциплине _____Программные средства экономических расчетов
для студентов специальности _050703____Информационные системы
Рекомендована на заседании кафедры от «___»____________200_г. Протокол №_____.
Заведующий кафедрой _________________________ Нурбекова Ж.К.
Одобрена методическим советом факультета __________________ «_____»______________200_г. Протокол №____
Председатель МС_________________________ Даутова А.З.
Тема №1 Использование встроенных функции MS Excel. Автоматизация типовых расчетов в среде EXCEL. (1ч)
Программа Excel входит в пакет Microsoft Office и предназначена для подготовки и обработки электорнных таблиц под управлением Windows.
Документом (т.е. объектом обработки) Excel является файл с произвольным именем и расширением .XLS. В терминах Excel такой файл называется рабочей книгой. В каждом файле XLS может размещаться от 1 до 255 электронных таблиц, каждая из которых называется рабочим листом.
Электронная таблица Excel состоит из 16384 ,а (Excel-97-65536) строк и 256 столбцов, размещенных в памяти компьютера. Строки пронумерованны целыми числами от 1 до 16384
(или от 1 до 65536), а столбцы обозначены буквами латинского алфавита A,B, C, …Z, AA, AB,…IV. Структурный основной элемент Excel- ячейка- пересечение столбцов и строк. В любую ячейку можно ввести текст, число или формулу для расчета производной информации.
Формулами в программе называют выражения, по которым выполняются вычисления на странице. Формулы можно вводить в ручную или использовать готовые формулы, предлагаемые программой. Формула всегда начинается со знака равенства. Для создания формулы в программе предусмотрено несколько логических и математических задач. Вид выражения может быть довольно сложным.Кроме констан в формулах можно использовать адреса и имена ячеек. Порядок выполнения изменяется посредстом скобок.
= А1+А2*3/А3. Если нужно указать ссылку на ячейку из другого листа данной рабочей книги, то пишется Баланс!А1. В качестве аргументов могут выступать числа, адреса ячеек, формулы, или функции. Например, СУММ(А1,МАКС(А2,А3)). Абсолютная адресация и относительная адресация ячеек. Абсолютной адресацией ячеек служит для того чтобы при перемещении курсора адрес конкретной(нужной) ячейки не изменяется.Для этого используют знак $, например, =СУММ($A$1:$A$2). Для удобства можно присвоить имя ячейке. Для этого нужно выделить ячейку затем выбрать ВСТАВКА- ИМЯ-ПРИСВОИТЬ. Имя не должно превышать 255 символов, между слов не должно быть пробелов, дефиса. Имя начинается с символа или символа подчеркивания. Большинство функции вводятся с помощью мастера функции.
Для задания формулы можно использовать различные технические приемы. При этом необходимо помнить, что формула должна всегда начинаться со знака “=”. Формула может содержать знаки арифметических операций: “+” , “-”, “*”(умножить), “/”(разделить), круглые скобки, функции, порядок вычисления которых соответствует принятому в математике.
Технический прием 1:
ввести знак “=”, а затем записать всю формулу , указывая адреса ячеек, набирая их с помощью клавиатуры, и соединяя их знаками арифметических операций.
Технический прием 2:
ввести знак “=”, а затем вводить адреса ячеек с помощью щелчка на ячейке, адрес которой должен стоять в формуле и так же соединять их знаками арифметических операций.
Для вычисления суммы ячеек можно использовать клавишу
, а затем выделить с помощью мыши диапазон ячеек, подлежащих суммированию. Если необходимо выделить диапазоны несмежных ячеек, необходимо нажать при выделении клавишу [Ctrl].При формировании более сложных формул, содержащих различные математические, статистические или другие функции целесообразно использовать мастер формул, который можно вызвать, нажав клавишу
или выбрать меню “Вставка”, а затем — команду “Функция”.Механизм защиты ячеек от нежелательных изменений
Excel дает возможность защитить информацию от нежелательных изменений. При этом различаются способы защиты всей книги, листа целиком и отдельных ячеек или диапазонов ячеек.
Для того, чтобы защитить отдельный лист, следует выбрать команду “Защита” из главного меню “Сервис”, а затем команду “Защитить лист”(для защиты книги — выбрать команду “Защитить книгу”). При установке защиты можно задать пароль. Снять защиту с листа (книги) можно с помощью меню “Сервис” команды “Защита” и “Снять защиту листа”(“Снять защиту книги”).
Если необходимо защитить только ряд ячеек листа, то необходимо прежде, чем выполнять какие-либо действия четко представить, что Excel может защищать только лист целиком. Следовательно, прежде, чем выполнять защиту всего листа с данными, необходимо выполнить снятие защиты с тех ячеек, которые предназначены для хранения изменяющейся информации. Для этого необходимо выделить те ячейки, в которых не нужно защищать информацию, затем выбрать меню “Формат”, затем — команду “Ячейки”, а в открывшемся диалоговом окне выбрать вкладку “Защита” и убрать флажок из окошка “Защищаемая ячейка”. При установке защиты на весь рабочий лист или книгу выделенные ячейки не будут защищены от изменений. Например, необходимо вычислить с помощью Excel годовую и помесячную амортизацию трактора марки ДТ-75 М, первоначальная стоимость которого 280000 тенге, срок службы 6 лет.. Для этого необходимо составить следующую таблицу:
| | A | B | C | D | Е |
| 1 | Первоначальная стоимость | Годы | Норма амортизации | Амортизация за год | Амортизация за месяц |
| 2 | 280000 | 1-й | 6/21 | =A2*C2 | =A2*C2/12 |
| 3 | | 2-й | 5/21 | =A2*C3 | =A2*C3/12 |
| 4 | | 3-й | 4/21 | =A2*C3 | =A2*C4/12 |
| 5 | | 4-й | 3/21 | =A2*C4 | =A2*C5/12 |
| 6 | | 5-й | 2/21 | =A2*C4 | =A2*C6/12 |
| 7 | | 6-й | 1/21 | =A2*C4 | =A2*C7/12 |
Пример. Объект — трактор, первоначальная стоимость 260000 тенге, срок службы 5 лет, норма амортизации 20%. При расчете регрессивным методом начисления амортизации, который состоит в том, что ее начисление производится не от первоначальной, а от балансовой (остаточной) стоимости объекта и при ликвидации объекта неамортизированная часть стоимости объекта выражает ликвидационную стоимость(стоимость металлолома, годных запчастей и др. материалов), нужно составить следующую таблицу.
| | А | B | C | D | E |
| 1 | Годы | Стоимость трактора: 1-й год- первоначальная 2-й - 5-й - балансовая | Норма амортизации (%) | Сумма амортизации за год | Сумма амортизации за месяц |
| 2 | 1-й | 260000 | 20 | =B2*C2/100 | D2/12 |
| 3 | 2-й | =B2-D2 | 20 | =B3*C2/100 | D3/12 |
| 4 | 3-й | =B3-D3 | 20 | =B4*C2/100 | D4/12 |
| 5 | 4-й | =B4-D4 | 20 | =B5*C2/100 | D5/12 |
| 6 | 5-й | =B5-D5 | 20 | =B6*C2/100 | D6/12 |
| 7 | Итого | =B6-D6 | | =СУММ(D2:D6) | |
Следует обратить внимание , что вычисление итоговой амортизации за 5 лет эксплуатации трактора производится с помощью функции Excel СУММ, которая помещается в клетку D7. Вызов этой функции можно выполнить двумя способами :
- с помощью клавиши
и указания диапазона суммирования,- с помощью вызова Мастера формул, с выбором из предлагаемого списка функций функции СУММ и указания диапазона суммирования.
Для отчисления амортизации в Excel предусмотрено ряд функций, с помощью которых можно рассчитать амортизацию применяемыми на территории бывшего СССР методами: АМР и АМГД.
Синтаксис АМР следующий:
АМР(стоимость, остаток, период).
Результат:
В качестве результата возвращается величина амортизации имущества за один период.
Аргументы:
стоимость - начальная стоимость имущества;
остаток - остаточная стоимость в конце амортизации (ликвидационная стоимость);
период - количество периодов, на протяжении которых имущество амортизируется.
Функция вычисляет амортизацию равномерным методом. Для того, чтобы вычислить амортизационные отчисления за год на объект стоимостью 280000 тенге, при этом срок его эксплуатации будет составлять 5 лет, а ликвидационной стоимости не предусмотрено, необходимо записать
АМР(280000;0;5), в результате в той клетке, где помещена данная функция, должно появиться число 56000. Если необходимо вычислить помесячную амортизацию, то в качестве периода вводится 60 (число месяцев за 5 лет).
Синтаксис АМГД следующий:
АМГД(стоимость, остаток, время эксплуатации, период).
Результат:
В качестве результата возвращается величина амортизации имущества для указанного периода.
Аргументы:
стоимость - начальная стоимость имущества;
остаток - остаточная стоимость в конце амортизации (ликвидационная стоимость);
время эксплуатации - количество периодов, на протяжении которых имущество амортизируется
период - промежуток времени, величина которого используется в качестве единицы измерения времени эксплуатации (как правило, год).
Функция вычисляет амортизацию методом кумулятивных чисел. Для того, чтобы вычислить амортизационные отчисления за год на объект стоимостью 280000 тенге, при этом срок его эксплуатации будет составлять 6 лет, а ликвидационной стоимости не предусмотрено, следует записать для первого года эксплуатации АМГД(280000;0;6;1), в результате в той клетке, где помещена данная функция, должно появиться число 80000, для второго года эксплуатации - АМГД(280000;0;6;2), для третьего года эксплуатации - АМГД(280000;0;6;3) и так далее. Если необходимо вычислить помесячную амортизацию, то в полученное значение амортизации делится на 12 (число месяцев за год).
| 280000 | 1 | 80 000,00 |
| | 2 | 66 666,67 |
| | 3 | 53 333,33 |
| | 4 | 40 000,00 |
| | 5 | 26 666,67 |
| | 6 | 13 333,33 |
Метод уменьшающегося остатка (регрессивный метод начисления амортизации) можно вычислить, применив функцию АМР следующим образом:
| A | B | | C | |
| 1 | 260000-0= | 260000 | АМР(В1;0;5)= | 52 000,00. |
| 2 | В1-С1= | 208000 | АМР(В2;0;5)= | 41 600,00. |
| 3 | В2-С2= | 166400 | АМР(В3;0;5)= | 33 280,00. |
| 4 | В3-С3= | 133120 | АМР(В4;0;5)= | 26 624,00. |
| 5 | В4-С4= | 106496 | АМР(В5;0;5)= | 21 299,20. |
| ликвидная стоимость | В5-С5= | 85197 | | |
См. литература: [4], стр. 47 -76;
Тема № 2. Использование функции ВПР(), ГПР(), ПОИСКПОЗ(), ИНДЕКС(), ЕСЛИ(). Проведение анализа с помощью таблиц подстановок. (2ч)
Рекомендуемая литература: [1], стр. 828 -843;
Проведение анализа с помощью функций и таблиц подстановок
В Excel очень часто возникает необходимость просмотра результатов при определенном условии. Иногда это условие зависит от некоторых данных, размещенных в каких-либо ячейках. В Excel существует возможность, которая позволяет даже начинающему пользователю легко решать подобные задачи. В этой главе рассматриваются следующие темы:- использование формул для поиска информации в таблицах;
- использование функции ветвления если () (if ()) для принятия решений;
- работа с таблицей подстановок.
30.1. Функции поиска информации в таблицах
Рассмотрим задачу поиска нескольких значений из множества данных в таблице.
В Excel существует возможность просмотра данных с помощью функций просмотра впр () и гпр () (в англоязычной версии Excel им соответствуют функции vlookup() и hlookup(). Кроме того, можно воспользоваться функциями индекс () и поискпоз() (index () и match () соответственно).
Функции просмотра впр () и гпр () очень просты в использовании, но обладают меньшей гибкостью по сравнению с функциями индекс () И П0ИСКП03().
Все четыре описываемые функции предполагают, что искомое значение будет найдено в таблице. В противном случае результатом работы каждой функции будет ошибка #н/д (#n/a).
Использование функций ВПР() и ГПР()
функции впр () и гпр () аналогичны друг другу, за исключением того, что функция впр () просматривает таблицу по строкам, начиная с первой, до тех пор, пока не будет найдено наиболее подходящее значение, а функция гпр () просматривает таблицу по столбцам, также начиная с первого столб-ца, до тех пор, пока не будет найдено наиболее подходящее значение.
функции впр () и гпр () имеют следующий синтаксис:
ВПР(искомое значение; диапазон таблицы; номер столбца;интервальный_просмотр)
ГПР(искомое_значение; диапазон_таблицы; номер_строки; интервальный_просмотр) ,
где:
искомое_значение — это значение, которое требуется найти в первом столбце/строке таблицы;
диапазон_таблицы — это ячейки, составляющие информационную таблицу;
номер_столбца и, соответственно, номер_строки — номер столбца (строки), в котором требуется найти искомое значение.
Четвертый аргумент, интервальный_просмотр, является необязательным. Это логическое значение, которое определяет, нужно ли, чтобы функция впр () (или гпр ()) искала точное или приближенное соответствие. Если этот аргумент имеет истинное значение или опущен, то функция возвращает приблизительно соответствующее значение, то есть, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое значение.
Индексы строк и столбцов начинаются с единицы. Если аргумент интервальный_просмотр имеет значение истина или опущен, то строка или столбец, которые используются для поиска в таблице, должны быть обязательно упорядочены по возрастанию, так как функции впр () и гпр () просматривают строку или столбец до тех пор, пока не встретят значение больше, чем искомое.
Рассмотрим пример использования функции впр (): в рабочей книге Артур Хейли.х1s представлен список романов Артура Хейли с некоторыми дополнительными сведениями — оригинальным (английским) названием, годом создания и количеством страниц. Внизу расположена небольшая таблица поиск. Она позволяет по заданному русскому названию романа получить остальные сведения о произведении (рис. 30.1).
Для поиска в таблице используется функция впр (). На рис. 30.2 изображена та же самая рабочая книга, в которой включен режим отображения формул.
Использование функций ПОИСКПОЗ() и ИНДЕКС()
Альтернатива функциям впр () и гпр () — функции поискпоз () (match ()) и индекс () (index ()). Они имеют следующий синтаксис:
ИНДЕКС(массив; номер строки; номер столбца)
ПОИСКПОЗ(искомое_значение; массив_просмотра; тип_сопоставления)
Для функции индекс () аргумент массив — это интервал ячеек или массив констант. Эта функция возвращает значение, хранящееся в ячейке этого массива, расположенной в строке номер_строки и в столбце номер_столбца.
один из двух последних аргументов — номер_строки ИЛИ номер_столбца —
может быть не задан. Если массив содержит только одну строку или один столбец, то соответствующий аргумент номер_строки или номер_столбца не является обязательным. Если массив занимает больше, чем одну строку или столбец, а задан только один аргумент номер_строки или номер_столбца, то функция индекс () возвращает массив из целой строки или целого столбца аргумента массив.
Как видно из рисунка, эти функции работают в паре между собой. Вот как происходит, например, извлечение оригинального названия романа (т. е. содержимого ячейки bis):
Сначала определяется порядковый номер строки, в которой записано оригинальное название романа. Делается это при помощи вызова функции
=ИНДЕКС(В3:В11; ПОИСКПОЗ(В17;АЗ:А11;0)1) .
В17 — это ячейка, содержащая искомое значение (т. е. текстовую строку «перегрузка»); аз:а11 — таблица, в которой это значение должно быть найдено; последний аргумент, равный нулю, указывает на то, что мы ищем точное, а не приближенное значение.
Тогда, когда нам известен номер строки, содержащей оригинальное название, мы можем получить его, вызывая функцию ИНДЕКС(В3:В11; ПОИСКПОЗ(В17;АЗ:А11;0)1)— т.е. извлекаем значение диапазона В3:В11 с порядковым номером строки, равным ПОИСКПОЗ(В17;АЗ:А11;0)1, и порядковым номером столбца, равным 1.
Функция если (), результат которой зависит от истинности или
ложности задаваемого условия. Функция если () часто используется в макросах, где она применяется для проверки разнообразных условий и выполнения различных действий, зависящих от этих условий. Она полезна и в случаях, когда надо оценить результат по какому-либо критерию, или просто выбрать одно из значений из двух возможных.
функция если () имеет следующий формат:
ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) .
Если лог_выражение (условие) истинно, то эта функция вернет значение
значение_если_истина, В ПрОТИВНОМ случае она вернет значение_если_ложь.
функция может возвращать следующие значения: □ Текст (например, «оплаченный»):
=ЕСЛИ(В4="0плачен"; "оплаченный"; "не оплаченный").
Если в ячейке в4 содержится текст "Оплачен", то результатом при истине будет "оплаченный", в противном случае результатом будет текст
"не оплаченный". О Формула (например, В2*6):
=--ЕСЛИ(В4>10;ВЗ*2;ВЗ+2) .
Если в ячейке в4 содержится число больше ю, то результатом будет формула вз*2, если в ячейке В4 содержится число меньше ю, то формула
ВЗ + 2.
Просто ссылка на содержимое ячейки (например, cs):
=ЕСЛИ(В4>10;С5;0) .
Результатом будет значение ячейки съ, если значение в ячейке В4 больше ю, и о в противном случае.
Рассмотрим конкретный пример: на рис. 30.4 изображен набросок небольшой анкеты, в которой человеку предлагается ввести свои данные, а потом ответить на несколько вопросов.
Значение в ячейке В12 зависит от того, к какому полу принадлежит анкетируемый человек. Ячейка содержит слово "господин", если в графе "пол" указано "мужской", либо слово "госпожа" в противном случае. Добиться этого эффекта позволяет функция если (), вызванная следующим образом:
=ЕСЛИ(В2="м";"господин";"госпожа")
Такое решение не лишено недостатков, которые несложно исправить.
Во-первых, анкета начинает "здороваться" еще до того, как были введены анкетные данные; во-вторых, слово "госпожа" появляется на экране во всех случаях, кроме того, когда человек ввел в графе "пол" букву "м" — т. е. если человек по ошибке ввел какую-то иную строку ("муж", "мужской"), то на экране все равно появится слово "госпожа".
Доработаем нашу анкету таким образом, чтобы приветствие появлялось на экране лишь в тех случаях, когда все анкетные данные введены корректно
Аргументы номер_строки и номер_столбца должны указывать на ячейку внутри аргумента массив, в противном случае функция индекс () возвращает ошибку.
Функция ПОИСКПОЗ () возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция поискпоз () используется, если требуется не сам элемент, а позиция элемента в диапазоне. искомое_значение — это значение, используемое для поиска в таблице, для которого ищется соответствие в аргументе мас-сив_просмотра. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение.
Третий аргумент функции поискпоз () не является обязательным. Значение О означает, что нужно искать значение, точно соответствующее искомому. Если при вызове функции третий аргумент пропущен или равен единице, то будет возвращен порядковый номер наибольшего из значений, не превышающих искомого. При этом массив должен быть упорядочен по возрастанию. Если значение аргумента тип_сопоставления равно —1, то функция поискпоз () найдет наименьшее из значений, больших или равных искомому. При этом массив должен быть упорядочен по убыванию.
При помощи функций поискпозо и индексо можно получить те же результаты, что и при использовании впр () и гпр (). На рис. 30.3 изображена Рабочая книга Артур Хейли.хХэ из предыдущего примера с использованием Функций ПОИСКПОЗ() и индекс().
В функции если () результат которой зависит от истинности или ложности задаваемого условия.
Она полезна и в случаях, когда надо оценить результат по какому-либо критерию, или просто выбрать одно из значений из двух возможных.
функция если () имеет следующий формат:
ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) .
Если лог_выражение (условие) истинно, то эта функция вернет значение
значение_если_истина, В ПрОТИВНОМ случае она вернет значение_если_ложь.
функция может возвращать следующие значения: □ Текст (например, «оплаченный»):
=ЕСЛИ(В4="0плачен"; "оплаченный"; "не оплаченный").
Если в ячейке в4 содержится текст "Оплачен", то результатом при истине будет "оплаченный", в противном случае результатом будет текст
"не оплаченный". О Формула (например, В2*б):
=--ЕСЛИ(В4>10;ВЗ*2;ВЗ+2) .
Если в ячейке в4 содержится число больше ю, то результатом будет формула вз*2, если в ячейке В4 содержится число меньше ю, то формула
ВЗ + 2.
Просто ссылка на содержимое ячейки (например, cs):
=ЕСЛИ(В4>10;С5;0) .
Результатом будет значение ячейки С5, если значение в ячейке В4 больше ю, и о в противном случае.
Рассмотрим конкретный пример: на рис. 30.4 изображен набросок небольшой анкеты, в которой человеку предлагается ввести свои данные, а потом ответить на несколько вопросов.
Значение в ячейке В12 зависит от того, к какому полу принадлежит анкетируемый человек. Ячейка содержит слово "господин", если в графе "пол" указано "мужской", либо слово "госпожа" в противном случае. Добиться этого эффекта позволяет функция если (), вызванная следующим образом:
=ЕСЛИ(В2="м";"господин";"госпожа")
Такое решение не лишено недостатков, которые несложно исправить.
Во-первых, анкета начинает "здороваться" еще до того, как были введены анкетные данные; во-вторых, слово "госпожа" появляется на экране во всех случаях, кроме того, когда человек ввел в графе "пол" букву "м" — т. е. если человек по ошибке ввел какую-то иную строку ("муж", "мужской"), то на экране все равно появится слово "госпожа".
Доработаем нашу анкету таким образом, чтобы приветствие появлялось на экране лишь в тех случаях, когда все анкетные данные введены корректно,
т. е. графа «Фамилия» непуста, а графа «Пол» содержит букву «м» или «ж» Для этого надо ввести в ячейки A12-D12 формулы:=ЕСЛИ( И(ВК>"";ИЛИ(В2 = "м";В2="ж") ) ; "Здравствуйте, "; "" )
=ЕСЛИ( ИЛИ(В1 = "";И(В2о"м";В2<>"ж") ) ; "" ; ЕСЛИ ( В2="м"; "господин"; "госпожа") )
=ЕСЛИ(И(ВК>"";ИЛИ(В2 = "м";В2="ж") ) ;В1; "") =ЕСЛИ(И(ВК>"";ИЛИ(В2="м";В2 = "ж") );"!"; "")
В этих формулах используются две важные логические функции — и о и или ().
Функция и о возвращает значение истина, если все ее аргументы истинны, в противном случае она возвращает значение ложь.
Функция или () возвращает значение истина в том случае, если хотя бы один из ее аргументов имеет значение истина. Иначе значение функции будет равно ложь.
Хаким образом, строка «Здравствуйте,» в ячейке А12, фамилия анкетируемого человека в ячейке С12 и восклицательный знак в ячейке D12 будут отображаться лишь в том случае, если содержимое ячейки bi (т. е. графы «фамилия») не будет равно пустой строке, а содержимое ячейки В2 (т. е. графы «Пол») будет равно «м» или «ж».В ячейке А14 будет отображена пустая строка, если графа «Фамилия» пуста, либо графа «Пол» не содержит букв «м» или «ж», либо и то, и другое одновременно. В противном случае применяется вызов функции если () из предыдущего примера, Т. е. ЕСЛИ(В2="м";"господин";"госпожа") .
30.2. Проведение анализа с помощью таблиц подстановок
Как повлияет изменение данных на конечный результат? Ответ на этот вопрос можно получить, воспользовавшись таблицами подстановок. С их помощью нетрудно проследить, как будет развиваться система при различных условиях. Таблицей подстановки данных называется диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Таблицы подстановки обеспечивают быстрый доступ к выполнению одной операции разными способами, а также возможность просмотра и сравнения полученных результатов. Использование таблицы подстановки вместе со статистическими функциями позволяет быстро и эффективно анализировать финансовую и научно-исследовательскую информацию.
В Excel существует понятие анализа чувствительности — это анализ, позволяющий определить, насколько надо изменить исходные данные, чтобы конечный результат претерпел значительные изменения. С помощью команды меню Данные, Таблица подстановки (Data, Table) можно проводить анализ чувствительности со сколь угодно широким диапазоном исходных данных.
Для более мобильного и эффективного использования таблиц подстановок в Excel существует возможность применения на одном рабочем листе нескольких таблиц подстановок.
Если необходимо изменять одно исходное значение, просматривая результаты одной или нескольких формул, или изменять два исходных значения, просматривая результаты только одной формулы, — используйте команду Меню Данные, Таблица подстановки (Data, Table).
Таблица подстановки с одной изменяющейся переменной
Создание таблицы подстановки может оказаться очень полезным, если существует множество данных и требуется получить результат по какой-то формуле. -В данном разделе рассматривается практическая задача — расчет зарплаты школьных учителей в зависимости от количества академических часов в неделю.
Чтобы создать таблицу подстановки с одной переменной, следует сформировать таблицу так, чтобы введенные значения были расположены либо в столбце, либо в строке. Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода. Ячейка ввода — это ячейка, в которую подставляются значения из таблицы данных. Любая ячейка на листе может быть ячейкой ввода. Хотя ячейка ввода не обязательно должна быть частью таблицы данных, формулы в таблице данных всегда ссылаются на ячейку ввода.
Для начала следует создать рабочий лист с исходными данными задачи (рис. 30.5).
Зарплата вычисляется по формуле: Зарплата = (Тариф * Кол-во_часов) / 18,
где 18 часов — полная ставка учителя. Эта формула введена в ячейку сб. Теперь требуется рассчитать зарплату для всех возможных вариантов занятости — от половины ставки (9 часов) до 24 часов. Для этого нужно выполнить следующие действия:
1. Введите в ячейки В7-В22 значения аргумента, т. е. числа от 9 до 24. Пер
вая ячейка столбца значений аргумента должна находиться на одну ячей
ку левее и ниже ячейки с формулой.
Правее первой формулы можно ввести любые другие формулы или адреса формул. Если значения расположены в строке, то формула должна находиться в ячейке, расположенной на один столбец левее и на одну строку ниже первого значения. В том же столбце, но ниже, можно набрать любые другие формулы.
- Выделите диапазон ячеек, содержащий формулы и значения подстановки (в данном случае — диапазон вб:С22) (рис. 30.6).
- Выполните команду Данные, Таблица подстановки (Data, Table). На экране откроется диалоговое окно Таблица подстановки (Table) (рис. 30.7). Если аргументы функции составляют столбец, то введите ссылку на ячейку ввода в поле Подставлять значения по строкам в (Row input cell). Для этого доста точно просто щелкнуть левой кнопкой мыши, предварительно поместив указатель в соответствующее текстовое поле. Если же аргументы функции составляют строку, то ссылка на ячейку ввода вводится в поле Подставлять значения по столбцам в (Column input cell). Для описываемого примера не обходимо в текстовом поле Подставлять значения по строкам в (Row inputcell) указать ячейку $с$4, поскольку список нужных значений академических часов находится в левом столбце, и эти значения изменяются по строкам сверху вниз. Ячейка $с$4 — это ячейка ввода, в которую последовательно (вручную) вводились бы суммы зарплат, если бы не было средства Таблица подстановки (Table). Excel самостоятельно подставит значения в эту ячейку, просчитает формулу, расположенную в заголовке выделен
ного диапазона, и поместит под ней список результатов.
4- Нажмите кнопку ОК.
После выполнения этих действий таблица заполнится требуемыми значениями (рис. 30.8).