Организация и принцип действия электронных таблиц

ИНСТРУКЦИЯ ПО EXEL

Выполнила:

Гудыма Наталья

ВОЗМОЖНОСТИ EXCEL

Организация и принцип действия электронных таблиц

Организация таблицы. Каждая ячейка таблицы имеет свой адрес, определяемый именем столбца и номером строки, на пересечении которых она находится, например, В4, С15. Строки нумеруются числами от 1 до 65536, а столбцы обозначаются одной или двумя латинскими буквами A, B, C,..Z, AA, AB,..IV. Важнейшим элементом таблицы является курсор - выделенный прямоугольник, отмечающий местоположение активной (выделенной, маркированной) ячейки. Перемещение в таблице выполняется как мышью обычным для окон Windows образом, так и при помощи клавиш и их сочетаний: <Ctrl+Home>, <Ctrl+End> - в начало таблицы, в конец; <Ctrl+PgDn>, <Ctrl+PgUp> - постранично вправо, влево, <PgDn>, <PgUp> - вниз и вверх на экран. Однако перемещение осуществляется только в пределах заполненной части таблицы. Можно работать и с группой (интервалом, блоком) ячеек - копировать их, перемещать, удалять, оформлять. Такую группу нужно выделить. Проще всего это делается мышью: ее курсор устанавливают в начальную (верхнюю левую) ячейку выделяемой области и при нажатой клавише мыши протягивают в последнюю (нижнюю правую) ячейку. Эту же операцию удобно выполнять клавишами < Shift - стрелки >. Адрес такой области таблицы записывается как адреса крайних полей, разделенные двоеточием, например, A8: C12. Если необходимо выделить группу несмежных полей, то описанные выше действия выполняют при нажатой клавише <Ctrl>. Эти группы полей адресуются с применением ещё и точки с запятой (A8: C12; D15: E20). Выделение целой строки - щелчок на ее номере, столбца - на его заголовке. Быстрый способ выделения сплошного блока (в пределах экрана) - <Shift> и по щелчку на крайних диагональных точках.

Ввод данных в таблицу. В качестве данных используют константы (числа, текст, даты) и формулы. Для ввода данных с клавиатуры устанавливают курсор в нужную ячейку, щелчком мыши выделяют ее и начинают ввод. В строке формулы окна при этом отображается адрес ячейки и вводимая строка данных. Завершается ввод нажатием клавиши <Enter> или переводом курсора в другую ячейку стрелками на клавиатуре. По умолчанию вводимые в ячейки числа выравниваются по правому краю, а текст - по левому. Для ввода отрицательных чисел перед числом вводят знак "минус" или число заключают в круглые скобки. Ввод десятичной дроби можно начинать с запятой - например, число 0,83 - набрать как ,83. Простая дробь вводится с обязательной нулевой частью и последующим пробелом: 3/4 Þ 0_{<пробел>}3/4. Смешанная дробь, например число 2,1875 вводится в исходном виде. Ввод даты или времени выполняется в одном из предлагаемых форматов (команда Формат-Ячейки-Дата/Время). Даты хранятся в памяти как числа, показывающие, сколько дней прошло от "начальной" даты - 1 января 1900 г. Это позволяет выполнять простейшие операции над датами.

Даты вводятся в формулы как текст, заключенный в двойные кавычки. Например, формула ="13.08.91"-"01.08.91" возвращает число 12 (в числовом формате Общий).

Данные из строки формул можно ввести сразу в несколько полей, для чего выделяется диапазон клеток, в строку формул вводится выражение и - <Ctrl+Enter>. Автоматическое заполнение клеток при вводе логически связанных данных - названий месяцев, годов, целых чисел, дней недели можно выполнить при помощи мыши: заполнить две подряд клетки (чтобы показать начальное значение и шаг изменения данных), выделить их и растянуть интервал выделения захватом и перемещением рамки за нижний правый уголок - черный крестик - маркер заполнения. Если интервал не распознан, в клетки запишется одно и то же значение. Другой способ - команда Правка-Заполнение-Прогрессия, которая в специальном диалоговом окне предлагает четыре варианта заполнения (арифметическую и геометрическую прогрессию, даты и Автозаполнение).

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

Создание своей (новой) последовательности: - выбрать команду Сервис-Параметры-Списки-Добавить, - в список последовательности ввести свой набор имен, нажимая <Enter > после каждого имени, - OK и возврат в таблицу. Ещё проще - ввести свои данные прямо в таблице один раз, выделить их, выполнить Сервис-Параметры-Списки, чтобы перейти в окно последовательностей, и нажать кнопку Импорт.

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

Ввод и копирование формул. Ввод любой формулы в ячейке должен начинаться с ввода знака равенства. Тогда в строке формулы отображается формула, а в ячейке после завершения набора и нажатия клавиши <Enter> - результат ее вычисления. При помощи формул можно присваивать содержимому выделеной ячейки числовое или текстовое значение. Например, формула =" Наташа" запишет в ячейку " Наташа ", выражение =3+2 вернёт 5. Но 3+2 без знака равенства воспримется системой как текст.

Если одна и та же формула используется для данных в последовательных строках или столбцах, её набирают один раз (обычно в 1-й строке), а затем копируют при помощи маркера заполнения или командами Правка-Копировать, Правка-Вставить. При обращении к ячейке в формуле, обычно указывается ее номер. Такая ссылка называется относительной. Например, A1, С4. При перемещении или копировании формулы относительные ссылки автоматически обновляются в зависимости от нового положения. Если при копировании формулы необходимо сохранить ссылку на конкретную ячейку, то необходимо воспользоваться абсолютной ссылкой. В абсолютной ссылке, когда указывается номер ячейки перед именем столбца и перед номером строки ставиться знак "$". Например, $A$1, $B$1 и т.п. Кроме этого, можно использовать смешанные ссылки, например, A$1 или $A1. Часть ссылки, не содержащая знак "$", будет обновляться при копировании, а другая часть, со знаком "$", останется без изменения. Вставка сразу двух дополнительных символов доллара выполняется клавишей <F4> сразу после набора адреса. Символы основных операций можно разделить на группы и представить их в следующем виде: арифметические: + - * / % ^ (cложение, вычитание, умножение, деление, процент и возв. в степень), сравнения: = < > <= >= <> (не равно), текстовые: & - конкатенация (соединение) символов, логические: И ИЛИ НЕ - логическое умножение, сложение, отрицание. Копирование и перенос данных с одного листа рабочей книги в другой выполняется путем их выделения, копирования в буфер обмена и вставки на другом листе командой Правка-Специальная вставка. Эта команда даёт возможность копировать отдельно значения, формулы, форматы и, кроме того, выполнять копирование в ячейки, содержащие другие значения, применяя при этом одну из операций: сложение, умножение, вычитание, деление между новыми и имеющимися в ячейках значениями. Использовать при переносе несмежные области нельзя.

Excel позволяет применять формулу к массивам данных (точнее, к наборам данных, содержащим однотипную информацию). В качестве аргументов можно брать целую строку, столбец или блок. Если структура аргументов одинакова, то результатом будет такой же массив, каждый элемент которого - результат применения формулы к каждой паре соответствующих элементов из массивов аргументов. Для реализации такой операции нужно выделить соответствующий интервал пустых ячеек, задать формулу, например, =А1:В5 / С1:D5 и - <Ctrl-Shift-Enter> (эта комбинация используется для группового ввода данных в ячейки таблицы вместо <Enter>).

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

Команда (Окно - Разделить) делит окно на 2 окна или на 4. Во всех окнах находится один и тот же рабочий лист и есть независимое управление окнами, что позволяет просматривать одновременно разные части одного документа, часто удаленные друг от друга. Можно
изменять данные в некоторых строках или столбцах и наблюдать изменение данных в другой
части таблицы. Убирают панели командой Окно-Снять разделение.

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

- для работы в этой же системе; в зависимости от типа объекта (таблица, диаграмма) файл получит расширение - .xls или.xlc;

- для сохранения таблицы в виде шаблона, используемого при создании других таблиц, c расширением .xlt;

- Text или CSV - кодирует файлы в кодах ASCII; в качестве разделителей - табуляторы или запятая;

- DBF - для экспорта данных в системы управления базами данных dBase и др.

Опции команды Файл-Сохранить как-Параметры позволяют сохранять предыдущий вариант таблицы с расширением .bak, устанавливать пароль доступа к файлу и пароли защиты данных.

Выход из системы Excel реализуется командой Файл-Выход или двойным щелчком на значке программы в верхнем левом углу окна Excel или клавишами <Alt-F4>.

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

Наглядно показываю что и как я делала:

1. 3.

2. 4.

В следующем пункте задания нужно было создать 2 аналогичные таблицы с удвоенными
значениями данных двумя способами - с использованием массивов и специальной вставки:

В последнем пункте задания надо было в столбцах А и В создать таблицу значений функции

y = при изменении x от 0,5 до 4 с шагом 0,1.

Оформление таблицы

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

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

Формат Общий установлен по умолчанию - число отображается так, как вводится. Формат Числовой самый применяемый: по умолчанию тысячи разделены пробелом, после запятой два знака. Денежный - как Числовой, но имеет размерность рубля - р. Этот формат не дает выравнивания чисел по десятичной запятой. Финансовый - имеет выравнивание, в остальном похож на Денежный. Экспоненциальный формат служит для изображения очень больших или очень маленьких чисел, например, число 0,0000067в этом формате будет представлено в виде мантиссы и порядка как 6,7 E -06, где Е - основание системы счисления. На панели инструментов имеются также значки для изменения количества десятичных знаков и для разделения тысяч.

Вертикальное выравнивание данных - (Формат-Ячейки-Выравнивание-Вертикальное) позволяет установить 4 типа выравнивания и автоперенос слов в клетках (опция Переносить по словам).

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

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

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

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

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

Построение диаграмм. Excel строит диаграммы различных типов - круговые, гистограммы (столбиковые), объёмные, графики - стандартные и нестандартные. Очень важно для имеющихся данных правильно выбрать тип диаграммы. Сначала выделяется блок данных, необходимых для построения диаграммы, затем вызывается Мастер диаграмм.

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

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

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

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

Функции и вычисления

(логические, табличные, матричные функции)

Excel имеет около 300 встроенных функций. Любую из них можно вводить с клавиатуры непосредственно в строке формул окна Excel или используя Мастера функций. Функция в Excel - это знак равенства, имя функции и аргументы в круглых скобках, которые разделяются точкой с запятой. Аргументами в функциях могут быть константы и адреса переменных (ссылки). Функция СУММ (А1; В5; С10) выдаст сумму чисел, записанных в аргументах-ссылках; МИН(С2: Е4) найдёт минимальное среди чисел указанного в аргументе диапазона ячеек. В качестве аргументов могут применяться другие (вложенные) функции - например, СУММ (МИН (C2:E4); A1; B5; C10).

Все функции разделены на группы. Самую большую группу составляют вычислительные функции (их около сотни). Они, в свою очередь, разделены на подгруппы-категории. К вычислительным функциям относятся математические, тригонометрические, статистические и др.
Функции текстовой группы выполняют преобразования чисел в ASCII-коды и обратно, определяют длину данных, выделяют из строк подстроки, объединяют подстроки, удаляют пробелы.
Информационные функции определяют состояние ячеек, выдают типы ошибок. Финансовые обрабатывают платежи, инвестиции, процентные ставки. Подробная информация обо всех функциях есть в Справке диалогового окна Мастера функций. Там же можно получить информацию по синтаксису выбранной функции и прототипам ее аргументов. Мастер функций применяют также при наборе сложных формул с большим числом аргументов (Вставка-Функция или значок f_x);онупрощает набор функций, так как разбивает эту операцию на отдельные шаги, выдает подсказки, отображает результат каждого шага на экране и берет на себя часть работы, добавляя в формулу поля для ввода аргументов, скобки, точки с запятой. Его окно можно перемещать.

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

Автосуммирование. Вызывается кнопкой (S) на стандартной панели инструментов и предназначена для быстрого вычисления суммы значений ячеек, расположенных в последовательных строках или столбцах. Если выделить интервал C3:F5 в представленном ниже фрагменте таблицы и нажать кнопку Автосумма, то все пустые клетки заполнятся суммарными значениями.

C	D	E	F

C	D	E	F

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

Логическая функция ЕСЛИ имеет три аргумента и позволяет по условию выбирать различные пути решения. Её синтаксис: ЕСЛИ (лог_выраж; значение1_если_истина; значение2_если_ложь)

Смысл (семантику) данной функции можно пояснить следующим образом:

если логическое_выражение (1-й аргумент) - истина, то результатом будет
значение1_если_истина (2-й аргумент), иначе –значение2_если_ложь (3-й аргумент).

Любое логическое выражение (“высказывание”) может иметь одно из двух значений:
TRUE (истина) или FALSE (ложь). В качестве логических выражений используются

- логические отношения - два арифметических выражения, соединенные символом операции
отношения, например, x > k-1; это самые простые логические выражения;

- логические одночлены - два или более логических отношения, соединенные логической операцией "И": x > 10 И x < 15 ("х лежит между 10 и 15" ); эту операцию называют также логическим умножением; в Еxcel эти выражения записываются по-другому (в префиксной форме) - символ логической операции находится перед аргументами): И (x > 10; x < 15);

- логические многочлены - два или более логических одночлена, соединенные операцией "ИЛИ": ИЛИ (x=2; y=2; z=2) - т.е. "хотя бы одна из трех переменных - x, y, z - равна 2 ".

Пример использования функции ЕСЛИ в одном из вариантов расчёта подоходного налога: =ЕСЛИ (C4>1000000; 20%*C4; 12%*С4); здесь функция ЕСЛИ выдаёт два возможных результата в зависимости от оклада. Если в качестве 3-го аргумента функции ЕСЛИ использовать эту же функцию, то можно получить 3 пути решения: = ЕСЛИ (Х>10; формула1; ЕСЛИ (X<5; формула2; форм.3)).

В этой же формуле на место формулы3 можно вставить еще одну функцию ЕСЛИ и выполнить вычисления по 4 формулам и т.д. (но не более 7). Функция ЕСЛИ неявно применяется в других вычислениях, например, в функциях СУММЕСЛИ и СЧЁТЕСЛИ, вычисляющих сумму или
количество лишь тех значений, которые удовлетворяют заданному условию.

Табличные функции предназначены для обработки данных одной или нескольких таблиц. В данном разделе рассматриваются функции ВПР, ГПР, ЧАСТОТА и РАНГ.

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

Функция РАНГ возвращаетранг значений в списке значений (их порядковые номера относительно других значений). Например, тестируемых можно распределить по местам, задав 1 тому, кто набрал наибольшее количество баллов, 2 – для второго места и т.д. Функция имеет 3 аргумента: * число в первой ячейке столбца исходных данных; * ссылка на весь исходный столбец в абсолютной адресации; * порядок (0 - по возрастанию ранга, 1 - по убыванию).

Функция ЧАСТОТА (исходный массив; массив карманов) подсчитывает, сколько значений из исходного массива попадают в диапазоны значений, представленных в массиве карманов. Если массив карманов содержит числа {a, b, c}, то числа исходного массива распределяются по интервалам: (-¥, a], (a, b], (b, c], (c, ¥). Таким образом, количество промежутков на 1 больше элементов в массиве карманов. Прежде чем использовать функцию ЧАСТОТА, выделяют свободный массив ячеек, на единицу больший чем массив карманов, и вводят функцию ЧАСТОТА. За исходный массив можно взять массив ячеек в любом столбцетаблицы Табл1, за массив карманов – значения из столбца Сумма баллов справочной таблицы Табл2 (в абсолютных адресах). Затем щелчок в строке формул и - завершение операции одновременным нажатием Ctrl+Shift+Enter (не ОК).

Матричные функции предназначены для обработки двумерных массивов данных - матриц, которые широко применяются для решения математических задач. В Excel это функции категории Математические: МОПРЕД – вычисление определителя матрицы, МОБР – вычисление обратной матрицы, МУМНОЖ – перемножение матриц, и в категории Ссылки и массивы ТРАНСП – транспонирование матрицы. Функция МОПРЕД возвращает число, поэтому вводится как обычная формула. Остальные функции (а также операции поэлементной обработки матриц одинакового размера) формируют блок ячеек, поэтому они вводятся как массивы.
При обработке матриц удобно вводить имена для массивов данных. Так, например, если две матрицы введены в блоки А1:С2 и Е1:G2 и этим блокам присвоены имена М и N, то формула поэлементного сложения матриц будет иметь простой и понятный вид: =M+N (набор формулы завершается групповой операцией ввода).

Формула =5*М–3*N выполнит поэлементное умножение
каждой матрицы на постоянное число, вычитание 13 -15 23
и возвратит результирующую матрицу -11 34 15

Для решения системы уравнений её представляют в матричном виде: АХ=В, где А - матрица коэффициентов при неизвестных, Х - массив неизвестных, В - массив свободных членов. Если определитель матрицы не равен нулю, то решение системы можно получить путём перемножения обратной матрицы A^-1 на массив В (с применением функции МУМНОЖ).

Практическая работа № 3. В данной работе я должна была вставить формулы расчета среднего балла для каждого студента, по каждому предмету и общего по всем предметам (добавив итоговую строку); ввести в последний столбец формулу с функцией ЕСЛИ, вычисляющую следующие текстовые значения: ОТЛ для студентов, имеющих только “5”, ХОР -"4" и "5", УД - "3", "4", "5" и НЕУД - "2", "3", "4", 5"; посчитать сумму баллов для каждого ученика; подсчитать в новых ячейках (с пояснениями) количество отличников и неуспевающих:

Мои действия:

1. 2.

5. 6.

Также я выполнила обработку табличных данных с использованием функций ВПР, ЧАСТОТА и РАНГ:

Мои действия:

1. 2.

3. 4.

Также нужно было применить матричные функции:

Мои действия:

1. 2.

3. 4.

5. 6.

Функции и вычисления

(прогнозирование, финансовые функции, "Подбор параметра" и "Поиск решения")

Функции прогнозирования. Одной из наиболее часто используемых возможностей Excel является экстраполяция ряда имеющихся данных с целью оценки характера их изменения и получения прогноза на будущее. Известны различные способы решения этой задачи - простые (на основе арифметической и геометрической прогрессии) и специально разработанные методы. Среди статистических функций Excel есть две функции - Тенденция и Рост, вычисляющие соответственно линейную и экспоненциальную экстраполяцию. Эти функции имеют по три обязательных аргумента (известные значения y; известные значения х и новые значения х). По первым двум наборам данных Excel строит зависимость у от х; после обработки этих значений формируется новая линия - тренд (прямая для Тенденции и кривая для Роста), которая затем продолжается до заданного нового значения х. Можно проиллюстрировать решение такой задачи на следующем примере. Этот пример достаточно подробно описан и может быть выполнен самостоятельно.

Финансовые функции. Более 50 различных функций предоставляет Excel-2000 для обработки бухгалтерских ведомостей и банковских капиталов, для вычисления процентов по вкладам и займам, для работы с ценными бумагами. Рассмотрим некоторые часто используемые функции.

БЗ - будущее значение вклада с периодическими постоянными платежами и постоянной процентной ставкой. Синтаксис функции Б3: Б3 (ставка; кпер; плата; нз; тип), где
3 обязательных аргумента:
- процентная ставка за период, - количество периодов выплат, - размер одной выплаты;

и 2 необязательных:
НЗ - текущая (начальная) стоимость или общая сумма всех будущих платежей с настоящего момента (если аргумент опущен, то он равен 0);
Тип - 0 или 1 (0 - выплата в конце периода, 1 - в начале);
если аргумент опущен, то Тип = 0.

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

AMP - функция, определяющая амортизацию имущества за один
период при равномерном распределении износа. Её синтаксис:

АМР (стоимость; остаток; период), где стоимость - начальная стоимость имущества, остаток - ликвидная стоимость в конце периода амортизации, период - количество периодов эксплуатации.

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

Синтаксис: АМГД (стоимость; остаточная_стоимость; время_эксплуатации; период), где

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

Метод подбора параметра. Реализуется командой Сервис-Подбор параметра, которая устанавливает режим итерационных вычислений для отыскания нужного решения при одном изменяющемся параметре в заданной формуле. Причём, начальное ("грубое") значение параметра должно быть известно. Метод итераций состоит в следующем: проверяется начальное значение функции, содержащей параметр, в "целевой" ячейке. Если это значение не дает нужную величину функции, то значение параметра изменяется и - следующий цикл. Для завершения вычислений задается точность и/или количество циклов-итераций (по умолчанию 100). Если точность за 100 циклов не достигнута, вычисления прекращаются, выдается результат и соответствующее сообщение.

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

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

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

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

Задачи, решаемые этим методом, имеют ряд общих свойств:

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

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

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

Практическая работа № 4. Применяю теорию на практике:

5. 6.

Работа со списками

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

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

Создание списка. Выполняется в любом месте рабочего листа в два этапа.

В ячейки вводят первую строку списка с именами полей (столбцов), которые определяют структуру (форму) списка, и следующую строку с данными - первую запись. В ячейки первой записи можно ввести и формулы по правилам электронных таблиц. Затем выделяют эти две строки и - Данные-Форма. Эта команда переводит Excel в режим работы со списком данных. Появляется окно формы - встроенной базы данных (БД). В этом окне удобно вводить, удалять, корректировать записи и выполнять типовые для БД операции.

После перехода в режим БД можно продолжить ввод данных. Для перехода из одного поля в другое применяется клавиша < Tab >, обратный переход - < Shift - Tab >; переход к следующей записи - <¯> или < Enter >. Если в какую-либо ячейку первой строки была введена формула и получено значение, то при заполнении остальных строк в режиме БД в соответствующих полях эти же действия выполнятся автоматически. Однако ни формулы, ни значения в этих полях не отразятся, править их нельзя. Можно копировать одинаковые значения полей из предыдущей записи в текущую - < Ctrl + ">. После заполнения всех записей - < Esc > или кнопка Закрыть.

Операции со списками. Просмотр БД - листание записей списка - выполняют так же, как и при наборе в окне формы; для этой цели предусмотрена линейка прокрутки.
Очистка поля - < Del > на клавиатуре; удаление целой записи - Delete (Удалить) в окне формы.

Для выполнения операций над БД ее выделяют или устанавливают курсор внутри БД (списка) и командой Данные-Форма переходят в режим форм.

Поиск записей - кнопка Критерий. Появляются чистые текстовые окна для каждого поля - в них вводятся критерии поиска. Например, если в поле ФИО задать Н*, будут выданы все фамилии, начинающиеся с буквы Н; для соотношения <=300, выбираются строки-записи со значениями переменной этого поля не более 300. Затем - Найти следующее и появляется первая найденная запись, удовлетворяющая критерию. Так же выдаются и последующие записи. Чтобы задать другой критерий - новый щелчок на кнопке Критерий; отменить режим поиска - кнопка Форма.

Выборку данных начинают с установки курсора на ключевое поле. Затем - Данные-Фильтр-Автофильтр - и на экране появится таблица, к каждому имени поля которой добавлен раскрывающийся список. В списке можно выбрать значение поля для использования его в простом критерии выборки или - пункт Настройка, чтобы задать сложное условие с использованием логических операций И и ИЛИ. Выбранные записи можно скопировать, распечатать, перенести в другую таблицу. Восстановление всех записей на экране - команда Данные-Фильтр-Показать Все. Отмена режима фильтрации - Данные-Фильтр-Автофильтр.

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

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

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

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

Данные - Итоги - При каждом изменении в - Наименование - Использовать функцию: - Сумма - Добавить итоги по: - Колич - Стоим - Заменить текущие итоги - Итоги под данными - OK.

Кнопка "+" повышает уровень детализации списка.

Удаление промежуточных итогов выполняется при помощи команды Данные-Промежуточные итоги-Удалить все.

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

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

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

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

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

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

Практическое задание № 5.

Операции со списками:

Несколько-уровневая сортировка списка:

Промежуточные итоги:

Консолидация:

Сводные таблицы:

Макропрограммирование.

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

Второй способ применяется для сложных макросов с диалогом, циклами и анализом ситуаций. При этом макрокоманда записывается в макрошаблон на специальном языке.
Таким языком в программах MS Office является язык объектного программирования Visual Basic for Application (VBA).

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

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

Практическая работа № 6.