Использование Excel (стр. 7 из 14)
Абсолютные адреса при перемещении формул не изменяются, а в относительных адресах происходит смещение на величину переноса.
Рассмотрим пример работы с формулами, за основу возьмем Таблицу 1. В данной таблице подсчитываются по формулам поля:
· Закупочная цена ($): - в зависимости от текущего курса $, который заносится в отдельную ячейку D1.
· Сумма закупки: = Закупочная цена товара*Количество
· Сумма реализации: = Розничная цена товара*Количество
· Валовой доход: = Сумма реализации - Сумма закупки
Такие формулы записаны в ячейки таблицы:
Таблица 2
| A | B | C | D | E | F | G | H | I | |||
| 1 | Сегодня курс | 3.62 | Розничная цена | ||||||||
| 2 | бананы | 2.90руб | |||||||||
| 3 | виноград | 5.20руб | |||||||||
| 4 | ананас | 6.80руб | |||||||||
| 5 | № п/п | Наименование | Фирма поставщик | цена (руб) | цена ($) | Кол-во | Сумма закупки | Сумма реали-зации | Валовый доход | ||
| 6 | 1 | бананы | Frutis | 2 | =D6/kurs | 200 | =D6*F6 | =F6*I$2 | =H6-G6 | ||
| 7 | 2 | бананы | SUMP | 2.32 | =D7/kurs | 80 | =D7*F7 | =F7*I$2 | =H7-G7 | ||
| 8 | 3 | бананы | Forum | 1.98 | =D8/kurs | 165 | =D8*F8 | =F8*I$2 | =H8-G8 | ||
| 30 | 25 | ананас | UFO | 6.05 | =D30/kurs | 120 | =D30*F10 | =F30*I$4 | =H30-G30 | ||
В данном примере ячейке D1 присвоено имя «kurs», которое используется во всех формулах для пересчета закупочной цены в $. Эта ячейка содержит только число (в нашем примере 3.62, был и такой курс когда-то), а пояснительный текст содержится в ячейке С1.
При подсчете суммы реализации, использован другой прием для того, чтобы можно было правильно копировать формулы: для ссылки на розничную цену конкретного товара используется частично абсолютный адрес: I$2 - в этом адресе запрещено изменение номера строки, поэтому при копировании такой формулы для товара "бананы" ошибок не возникнет. Для других товаров нужно создать формулы со ссылкой на их розничную цену.
3.4. Функции
3.4.1. Понятие функции
Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel.
Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.
Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.
В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.
Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.
3.4.2. Типы функций
Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.
При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.
С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.
В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную, транспонировать.
С помощью статистических функций возможно проводить статистическое моделирование. Кроме того, возможно использовать элементы факторного и регрессионного анализа.
В Excel можно решать задачи оптимизации и использовать анализ Фурье. В частности, в Excel реализован алгоритм быстрого преобразования Фурье, при помощи которого вы можете построить амплитудный и фазовый спектр.
3.4.3. Мастер функций
Excel содержит более 400 встроенных функций. Поэтому непосредственного вводить с клавиатуры в формулу названия функций и значения входных параметров не всегда удобно. В Excel есть специальное средство для работы с функциями — Мастер функций
. При работе с этим средством вам сначала предлагается выбрать нужную функцию из списка категорий, а затем в окне диалога предлагается ввести входные значения. 
Рис. 17. Мастер функций
Мастер функций вызывается командой Вставка | Функции или нажатием на кнопку Мастер функций
. Эта кнопка расположена на панели инструментов Стандартная, а также в строке формул.Рассмотрим применение функции СУММ(диапазон) (сумма диапазона ячеек) и СРЗНАЧ(диапазон) (среднее значение диапазона ячеек) для подведения промежуточных итогов, на примере таблицы 2.
Вставим после каждой товарной позиции пустые строки и с помощью функции приведенных выше получим требуемые результаты.
Таблица 3
| A | B | C | D | E | F | G | H | I | |
| 5 | № п/п | Наи-мено-ван | Фирма поставщик | цена (руб) | цена ($) | Кол-во | Сумма закупки | Сумма реализац. | Валовый доход |
| 6 | 1 | Бана-ны | Frutis | 2 | =D6/kurs | 200 | =D6*F6 | =F6*I$2 | =H6-G6 |
| 7 | 2 | Бана-ны | SUMP | 2.32 | =D7/kurs | 80 | =D7*F7 | =F7*I$2 | =H7-G7 |
| 8 | 3 | Бана-ны | Forum | 1.98 | =D8/kurs | 165 | =D8*F8 | =F8*I$2 | =H8-G8 |
| 9 | 4 | Бана-ны | Like | 1.86 | =D9/kurs | 320 | =D9*F9 | =F9*I$2 | =H9-G9 |
| 10 | 5 | Бана-ны | UFO | 2.05 | =D10/kurs | 120 | =D10*F10 | =F10*I$2 | =H10-G10 |
| 11 | Средн: | =СРЗНАЧ (D6:D10) | Итого: | =СУММ (F6:F10) | =СУММ (G6:G10) | =СУММ (H6:H10) | =СУММ (I6:I10) | ||
| 12 | 6 | Вино-град | Frutis | 4 | =D12/kurs | 90 | =D12* F12 | =F12*I$3 | =H12-G12 |
| 25 | Итого: | Итого: | =СУММ (F6:F22) | =СУММ (G6:G22) | =СУММ (H6:H22) | =СУММ (I6:I22) |
Так будет выглядеть результат вычисления формул:
Таблица 4
| Сегодня курс $= | 1.3 | Розничная цена | ||||||||
| бананы | 2.90руб | |||||||||
| Расчет валового дохода от реализации товаров | виноград | 5.20руб | ||||||||
| ананас | 6.80руб | |||||||||
| № п/п | Наименование | Фирма поставщик | цена' (руб) | цена' ($) | Количество | Сумма закупки | Сумма реализации | Валовой доход | ||
| 1 | бананы | Frutis | 2.00руб | $ 1.54 | 200 | 400.00руб | 580.00руб | 180.00руб | ||
| 2 | бананы | SUMP | 2.32руб | $ 1.78 | 80 | 185.60руб | 232.00руб | 46.40руб | ||
| 3 | бананы | Forum | 1.98руб | $ 1.52 | 165 | 326.70руб | 478.50руб | 151.80руб | ||
| 4 | бананы | Like | 1.86руб | $ 1.43 | 320 | 595.20руб | 928.00руб | 332.80руб | ||
| 5 | бананы | UFO | 2.05руб | $ 1.58 | 120 | 246.00руб | 348.00руб | 102.00руб | ||
| Бананы | Средняя: | 2.04руб | Итого: | 885 | 1 753.50руб | 2 566.50руб | 813.00руб | |||
| 24 | ананасы | Like | 5.99руб | $ 4.61 | 60 | 359.40руб | 408.00руб | 48.60руб | ||
| 25 | ананасы | UFO | 6.60руб | $ 5.08 | 55 | 363.00руб | 374.00руб | 11.00руб | ||
| Ананасы | Средн: | 6.04руб | Итог: | 315 | 1 861.55руб | 2 142.00руб | 280.45руб | |||
| Итого: | Итог: | 1517 | 4 865.02руб | 6 356.90руб | 1 491.88руб | |||||
Того же самого результата получения суммы столбца или строки можно было достигнуть используя Автосуммирование, для этого необходимо выделить нужный диапазон ячеек и нажать кнопку
.