Табличный процессор ms excel
 Скачать 6.89 Mb.
|
|
Табличный процессор MS Excel 2 MS Excel является компонентой пакета MS Office. Excel имеет единый интерфейс приложений MS Office. MS Excel может свободно обмениваться данными со всеми приложениями MS Office. MS Excel содержит средства просмотра Web- страниц. MS Excel поддерживает инструментальную среду программирования Visual Basic for Application, в которой пользователь может создавать свои приложения. Рабочая книга – это файл, используемый для обработки и хранения данных. Книга может содержать от 1 до 255 рабочих листов. Рабочий лист представляет собой совокупность поименованных латинскими буквами столбцов и пронумерованных строк, на пересечении которых образуются ячейки. Каждая ячейка имеет уникальный адрес. Активная (текущая) ячейка – ячейка, выделяемая внутри таблицы курсором. Адрес и содержимое активной ячейки отображаются в строке формул. Диапазон ячеек – прямоугольная область, состоящая из смежных ячеек рабочего листа. Диапазон ячеек также имеет адрес, в котором указывается адрес верхней левой ячейки диапазона и адрес правой нижней ячейки диапазона. Например: А1: С10. Строка формул содержит поле имени , в котором отображается адрес активной ячейки и непосредственно строку формул, в которой отображается содержимое активной ячейки. 4 Элементы окна MS Excel Панель быстрого доступа строка формул ярлыки листов поле имени активная ячейка Данные В MS EXCEL выделяют следующие типы данных: Числа; Текст; Дата и время; Логические значения; Сообщения об ошибках; Формулы. Тип данных определяет множество допустимых значений и операций с данными Числовые значения Числовые значения выравниваются по правому краю ячейки. Целая часть от дробной отделяется запятой. Числовые значения могут включать цифры, знаки +, -, %, запятую, наклонную черту /, специальные знаки числовых и пользовательских форматов, например, знак $. Если ширины ячейки недостаточно для отображения всего числа , то возможны следующие ситуации: Excel автоматически подровняет ширину ячейка по размеру числа; Число будет выведено в экспоненциальном формате (это в основном касается очень больших и малых чисел); Ячейка заполняется символами решетки ####### ; Текстовые значения Выравниваются по левой границе ячейки; Если первый символ в ячейке - апостроф, то число воспринимается Excel как текст. Для работы с текстом употребляется операция объединения (конъюнкции) & и текстовые функции. Дата и время Дата представлена в Microsoft Excel в виде числа, определяемого количеством дней от системной даты (1 января 1900 года) до представленной в ячейке. Время представлено в виде дроби (время является частью даты и отделяется от даты пробелом). Например: 01.01.03 12:00 Над датой или временем можно производить арифметические операции – сложение и вычитание. Например, чтобы определить число дней между двумя датами, можно вычесть одну дату из другой. При изменении формата ячеек, содержащих компоненты даты и времени на числовой, дата и время отображаются в виде числа с десятичной точкой. Например: Логические значения Принимают значения ИСТИНА или ЛОЖЬ В ячейки записываются как формулы со знаком равенства. Используются в логических операциях, в качестве аргументов некоторых функций. Пример =2+2=4 равняется ИСТИНА =5*5>25 равняется ЛОЖЬ Сообщения об ошибках Сообщение выводится в ячейке с формулой. Первый символ #, затем описание, последним следует символ ! или ? Сообщение об ошибке зависит от вида ошибки: #ИМЯ? – в ячейке указано имя несуществующей функции или допущена ошибка в имени ячейки, которая участвует в формуле. #ССЫЛКА! – в формуле указана ссылка на несуществующие ячейки. #ДЕЛ/0 ! – деление на ноль. #Н/Д ! – в аргументе функции задана ссылка на пустую ячейку. #ЗНАЧ! – в формуле (функции) используется аргумент недопустимого типа (текстовый). #ПУСТО! - ошибка возникает, когда задано пересечение двух областей, которые в действительности не пересекаются. Формулы Формула – выражение, предназначенное для вычисления нового значения. Сама формула отображается в строке формул, а в ячейке виден результат вычисления по формуле. Для задания режима отображения формул на рабочем листе, следует выполнить команду Вкладка Формулы – Зависимости формул – кнопка Показать формулы. Формула начинается со знака = за которым идут вычисляемые элементы (операнды), разделенные операторами. В качестве операндов могут выступать постоянные значения (константы), ссылки на ячейки или диапазоны ячеек, заголовки, имена или функции. В Microsoft Excel используется четыре вида операторов: • арифметические, • текстовые, •операторы сравнения •операторы ссылок Арифметические операторы. Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение. + (знак плюс) Сложение – (знак минус) Вычитание * (звездочка) Умножение / (косая черта) Деление % (знак процента) Процент ^ (крышка) Возведение в степень Текстовый оператор - & конкатенации используется для объединения нескольких текстовых строк в одну строку. Оператор ссылки. Для описания ссылок на диапазоны ячеек используются следующие операторы. : (двоеточие) Оператор диапазона используется для ссылки на все ячейки диапазона. , (запятая) Оператор объединения объединяет несколько ссылок в одну ссылку. Операторы сравнения. Используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ. = Равно > Больше < Меньше >= Больше или равно <= Меньше или равно <> Не равно Формат данных определяет способ представления данных на экране и в памяти компьютера. Атрибуты форматирования могут быть связаны с типом данных в ячейке. Вкладка Главная – группа Число – список Числовой формат открывает окно диалога Формат ячеек. Форматы данных Для представления чисел на рабочем листе Excel можно выбрать следующие форматы: Форматы числовых значений Общий формат используется для отображения как текстовых, так и числовых значений произвольного типа. Число в общем формате будет выглядеть так же, как оно выглядело при вводе. Числовой формат позволяет установить следующие параметры форматирования: разделитель групп разрядов применяется для больших чисел. Числа делятся на триады для наглядности представления их порядка; Например: 60000000 и 60 000 000 Число десятичных знаков; Например: 0,56476946793; 34,058; 8,5; 60 000 000,00 вид отрицательных чисел (красным, с минусом, с минусом красным). Денежный формат содержит те же параметры форматирования, что и числовой формат. В дополнение задается обозначение денежной единицы любой страны (выбирается из раскрывающегося списка). Например: 60 000 000,00 р. - $4 000 Финансовый формат служит для выравнивания денежных величин по разделителю целой и дробной части. Нулевые значения отображаются как прочерк, а прочерк выравнивается по разделителю целой и дробной части. Процентный формат Введенное в ячейку значение умножается на 100, округляется до установленного знака после запятой и добавляется знак процента % в конце. Например, после применения процентного формата для числа 0,5 на экране мы увидим 50%. Дробный формат Формат отображает обыкновенные дроби. В списке Тип можно выбрать представление дроби в различных видах: с представлением числителя и знаменателя в одно-, двух-, или трехцифровом виде. Возможно представление дроби половинными, четвертыми, восьмыми, шестнадцатыми, десятичными и сотыми долями. ( в дробном формате число 0,5 будет выглядеть как 1/2) экспоненциальный формат используется для очень больших и малых величин. Число преобразуется в число с дробной частью с указанием степени в которую необходимо возвести число 10 для получения реального значения. Например: 7 683 000 000 можно представить как 7,683 * 10 9 В экспоненциальном формате это выглядит как 7,683Е+09 Ссылки рабочего листа При организации вычислений в MS Excel удобно использовать не значения числовых данных, а адреса ячеек или ссылки на ячейки, где хранятся эти данные. Использование ссылок на ячейки в формулах обеспечивает автоматический пересчет результата при внесении изменений в ячейки, содержащие исходные данные. В MS Excel используются следующие виды ссылок: Относительные; Абсолютные; Смешанные; Объемные; Внешние; Циклические. Относительные ссылки Относительные ссылки совпадают со стандартным именем ячейки (A1, D2, С5). При копировании формул относительные ссылки изменяются. При переносе формулы относительные ссылки не меняются = A1 + B1 = B1 + C1 = C1 + D1 = A2 + B2 = A3 + B3 Изменяется номер строки Изменяется буква столбца Абсолютные ссылки Абсолютная ссылка – это абсолютный адрес ячейки, содержит символ доллара $ перед буквой столбца и номером строки ( $A$2, $L$5, $F$11). При копировании формул абсолютные ссылки не меняются. $A$1 $A$1 $A$1 Адрес ячейки не меняется Смешанные ссылки Смешанная ссылка - сочетание абсолютной и относительной адресации. Использование смешанной ссылки помогает сохранить часть ссылки без изменений. Символ абсолютной адресации $ ставится перед тем элементом ссылки, который закрепляется, не будет изменяться при копировании или тиражировании формулы. $A1, A$1 При копировании и тиражировании формулы закрепляется либо адрес столбца, либо номер строки. Относительная часть ссылки изменяется, абсолютная часть остается неизменной. Смешанные ссылки $A1 $A2 $A1 A$1 A$1 В$1 Чтобы изменить тип ссылки на ячейку нужно выделить ссылку в строке формул и нажать клавишу F4. Тип ссылки изменяется в такой последовательности: относительная, смешанная, абсолютная и т.д. Стиль ссылок R1C1 Стиль ссылок R1C1 устанавливается через кнопка Офис – Параметры Excel – Формулы – установить флажок стиль ссылок R1C1. После применения стиля R1C1 цифрами обозначаются не только строки рабочего листа но и столбцы. Относительная ссылка R1C1 соответствует ссылке А1 обычного стиля адресации. R - строка C – столбец Относительные ссылки R1C1 используются в формулах для указания адреса ячейки относительно позиции ячейки, в которой содержится формула. В качестве ссылки на ячейки в формуле фиксируются координаты ячейки, участвующей в вычислениях, относительно ячейки с формулой. Стиль ссылок R1C1 Модель пространства ячеек можно представить в виде оси координат. Точка отсчета – ячейка с формулой. -R -C +R +C -1 B A -1 -2 -3 1 F C 2 1 D Стиль ссылок R1C1 Относительная адресация Например: R[-1]C[1], R[-1]C[4], R[1]C[5] Абсолютная адресация Например: R1C7, R2C2, R11C4 Смешанная адресация Например: R[-3]C5, R8C[-1], R20C При копировании, тиражировании и переносе ссылки на ячейки не меняются ! Объемные ссылки Объемные ссылки ссылаются на данные, расположенные на другом рабочем листе. Например: Лист2! А1 Лист3! $В1:$В15 Внешние ссылки Внешние ссылки используются при обращении к данным, расположенным на другом рабочем листе в другой рабочей книге. Например: [Книга2] Лист3! А1 [Книга1] Лист2! С1:С20 Циклические ссылки Циклической ссылкой называется последовательность ссылок, при которой формула ссылается (через другие ссылки или напрямую), сама на себя. Когда формируется циклическая ссылка, появляется сообщение, предупреждающее об этом. Если циклическая ссылка является случайной, нажмите кнопку OK, появится панель инструментов «Циклические ссылки» и стрелки слежения укажут на все ячейки, на которые ссылается эта циклическая ссылка. Некоторые научные и инженерные формулы требуют циклических ссылок. Функции рабочего листа Функция – зависимость одной переменной у от другой х или от нескольких переменных (х 1 , х 2 ,….х n ), причем, каждому набору переменных соответствует единственное значение функции. Функция - это заранее определенная формула, которая оперирует с одним или несколькими значениями и возвращает в качестве результата одно или несколько значений. Последовательность записи называется синтаксисом функции. Правила синтаксиса для всех функций одинаковы. При нарушении правил синтаксиса MS Excel выдает сообщение об ошибке и делает попытку исправить формулу. Каждая функция имеет имя и аргументы , записываемые в круглых скобках и отделяемые друг от друга точкой с запятой. Аргументы – значения, над которыми функция выполняет вычисления. Существуют функции, которые не содержат аргументов: ПИ(), ТДАТА(), СЕГОДНЯ(). Список аргументов может состоять из чисел, текста, логических величин ИСТИНА или ЛОЖЬ, массивов, значений ошибок (#Н/Д, #ЗНАЧ и др.), ссылок на ячейки, формул. Необходимо также следить за соответствием типов аргументов. Если в качестве аргумента одной функции выступает другая функция, то функция называется вложенной. Функция может содержать несколько вложенных функций. MS Excel 2007 (2010)допускает64 уровня вложенности функций. Правила ввода функции совпадают с правилами ввода формулы. Знак =, затем имя функции и в скобках список ее аргументов. Для удобства работы с функциями предусмотрен Мастер функций . Мастер функций Для обращения к мастеру функций можно воспользоваться специальной кнопкой на панели инструментов или вкладкой Формулы. На первом шаге Мастер функций предлагает выбрать функцию из 11 основных категорий. Категории функций 1. Финансовые 2. Дата и время 3. Математические 4. Статистические 5. Ссылки и массивы 6. Работа с базой данных 7. Текстовые 8. Логические 9. Проверка свойств и значений 10. Инженерные 11. Аналитические Если неизвестно к какой категории относится функция, необходимо просмотреть полный алфавитный перечень. При выделении конкретной функции в нижней части окна выводится краткая информация о том, что выполняет данная функция. В нижней части окна по гиперссылке можно вызвать справку по данной функции. Математические функции = КОРЕНЬ(16) Аргументы положительные числовые значения. = ABS(-16) Функция возвращает модуль числа. = КОРЕНЬ(ABS(-16)) = LN (86) Возвращает натуральный логарифм числа. = EXP(степень экспоненты). Возвращает значение экспоненты в указанной степени. е 5 = EXP(5) Математические функции = ПИ() Возвращает точное значение числа = SIN (1) Возвращает синус числа. =COS (0) Возвращает косинус числа. =COS (ПИ()) Математические функции =ОКРУГЛ(число;число_разрядов) Округляет число до указанного количества десятичных разрядов. Число - это округляемое число. Число_разрядов - это количество десятичных разрядов, до которого нужно округлить число. Если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой. Если число_разрядов равно 0, то число округляется до ближайшего целого. Если число_разрядов меньше 0, то число округляется слева от десятичной запятой. Математические функции Примеры =ОКРУГЛ(2,15; 1) равняется 2,2 =ОКРУГЛ(2,149; 1) равняется 2,1 =ОКРУГЛ(-1,475; 2) равняется -1,48 =ОКРУГЛ(21,5; -1) равняется 20 44 Функции СЧЕТЕСЛИ() СУММЕСЛИ() 45 СЧЁТЕСЛИ Подсчитывает количество ячеек диапазона, удовлетворяющих заданному критерию. Синтаксис СЧЁТЕСЛИ(диапазон;критерий) Диапазон — это диапазон, в котором нужно подсчитать ячейки. Критерий — это условие в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки". Критерий может быть ссылкой на ячейку, в которой записан сам текст критерия. 46 Примечания В качестве критерия нельзя использовать логическое выражение в котором участвуют ссылки на ячейки или логические функции. Например: А2<50, D4>=F3 Функция СЧЕТЕСЛИ не допускает вложений , поэтому нельзя сформировать сложный критерий с использованием логических функций. 47 Сколько магазинов имеют товарооборот больше 70 тыс.руб.? =СЧЁТЕСЛИ(B4:B13;">70000") результат 4 Сколько магазинов расположено в Советском районе? =СЧЁТЕСЛИ(C4:C13;C4) результат 5 48 СУММЕСЛИ Суммирует ячейки, заданные критерием. Синтаксис СУММЕСЛИ(интервал; ритерий;сумм_интервал) Интервал — это интервал вычисляемых ячеек. 49 СУММЕСЛИ Критерий — это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как G10, ―80", ">=9", ―высший сорт". Сумм_интервал — это фактические ячейки для суммирования. Ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют критерию. Если сумм_интервал опущен, то суммируются ячейки в аргументе интервал 50 В качестве критерия нельзя использовать логическое выражение в котором участвуют ссылки на ячейки или логические функции. Аргументы интервал и сумм_интервал должны быть равноразмерными Например: Интервал – A1:A10 Сумм_интервал – С1:С10 51 Общий товарооборот магазинов с оборотом менее 55 тыс.руб.? =СУММЕСЛИ(B4:B13;"<55000") результат 94 700 Общий товарооборот магазинов Центрального района. =СУММЕСЛИ(C4:C13;‖Центральный‖;B4:B13) результат 198500 Текстовые функции ДЛСТР Возвращает количество символов в текстовой строке. Синтаксис: =ДЛСТР(текст) Примеры: =ДЛСТР("Москва_Россия") равняется 13 ПРАВСИМВ Возвращает последние (самые правые) символы текстовой строки. Синтаксис: =ПРАВСИМВ(текст;число_символов) Текст - это текстовая строка, содержащая извлекаемые символы. ПРАВСИМВ Число_символов - это количество извлекаемых символов. Число_символов должно быть больше или равно нулю. Если число_символов больше, чем длина текста, то функция ПРАВСИМВ возвращает весь текст. Если число_символов опущено, оно полагается равным 1. Примеры =ПРАВСИМВ("Продажная Цена"; 4) равняется "Цена" =ПРАВСИМВ("Ассортиментный Номер") равняется "р" ЛЕВСИМВ Возвращает первые (самые левые) символы текстовой строки. Синтаксис =ЛЕВСИМВ(текст;количество_символов) ЛЕВСИМВ аналогична функции ПРАВСИМВ. Примеры = ЛЕВСИМВ("Цена Товара"; 4) равняется "Цена" Если ячейка A1 содержит "Швеция", то: = ЛЕВСИМВ(A1) равняется "Ш" СЦЕПИТЬ Объединяет несколько текстовых строк в одну. Синтаксис = СЦЕПИТЬ (текст1;текст2;...) Текст1, текст2, ... - это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку. СЦЕПИТЬ Примеры Пусть имеется рабочий лист, в котором собраны данные о видах рыб, обитающих в реке, и пусть ячейка C2 содержит "вида", ячейка C5 содержит " речная форель" и ячейка C8 содержит 32. Тогда =СЦЕПИТЬ("Численность популяции ";C2;" ";C5;" составляет ";C8;" на километр.") равняется "Численность популяции вида речная форель составляет 32 на километр." ПОВТОР Повторяет текст заданное число раз. Функция ПОВТОР используется, чтобы заполнить ячейку некоторым количеством вхождений текстовой строки. Синтаксис = ПОВТОР (текст;сколько_раз) Текст - это повторяемый текст. Пример ПОВТОР("*-"; 3) равняется "*-*-*-" Функции категории Дата и время = СЕГОДНЯ() Возвращает текущую дату в числовом формате. Числовой формат даты - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. = ТДАТА () Возвращает текущую дату и время в числовом формате. Синтаксис: ТДАТА( ) – без аргументов. Функция ТДАТА меняет свое значение только при расчете листа Excel или при выполнении макроса, содержащего эту функцию. Значение этой функции не обновляется непрерывно. ДЕНЬНЕД Синтаксис: ДЕНЬНЕД(дата_в_числовом_формате;тип) Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое число от 1 до 7. Дата_в_числовом_формате — это число, соответствующее дате, день недели которой необходимо найти. Даты могут вводиться как текстовые строки в двойных кавычках (например "30.1.2000"), как числа (например 35825 ), а также как ссылки на ячейки, где хранится дата. = ДЕНЬНЕД(А10) Тип— это число, которое определяет тип возвращаемого значения. Если тип = 1 или опущен, то функция возвращает число от 1 (воскресенье) до 7 (суббота). Если тип = 2, то функция возвращает число от 1 (понедельник) до 7 (воскресенье). Если тип = 3, то функция возвращает число от 0 (понедельник) до 6 (воскресенье). Примеры = ДЕНЬНЕД("14.2.98";2) равняется 6 (суббота) = ДЕНЬНЕД("23.2.2003";3) равняется 6 (воскресенье) ДНЕЙ360 Возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев). Эта функция используется для расчета платежей, если бухгалтерия основана на двенадцати 30-дневных месяцах. Синтаксис: =ДНЕЙ360 (нач_дата; кон_дата; метод) Нач_дата и кон_дата — это две даты, количество дней между которыми необходимо вычислить. Если нач_дата соответствует дате более поздней, чем кон_дата, функция возвращает отрицательное значение. Метод— это логическое значение, которое определяет, какой метод, Европейский или Американский, должен использоваться при вычислениях. Если Метод - ЛОЖЬ или опущено, то работает Американский метод (NASD). Если Метод - ИСТИНА, то работает Европейский метод. Статистические функции Статистические функции поддерживают инструментарий раздела статистики. Категория «Статистические» содержит функции для обработки статистических данных: расчета моды, медианы, среднего, максимального и минимального значений и т.д. Функции Минимум, Максимум и Среднее значение Функции МИН, МАКС, СРЗНАЧ позволяют определить минимум, максимум и среднее арифметическое значение в указанном диапазоне ячеек. Функции игнорируют текстовые и логические значения, а также пустые ячейки (нулевые значения учитываются). Синтаксис функций = ИМЯ (число1; число2; число3;…) В качестве аргументов используются константы, ссылки на ячейки, диапазоны ячеек, имена, массивы и другие функции. Примеры: = МАКС (А3:А15;D3:D15) = МИН (А1:А10; В1:В10) = СРЗНАЧ (В20:В35) Функция СЧЕТ Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервале ячеек. Синтаксис СЧЁТ(значение1; значение2; ...) Значение1, значение2, ... - это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа. Учитываются аргументы, которые являются числами, пустыми значениями, логическими значениями, датами, или текстами, изображающими числа. Если аргумент является массивом или ссылкой, то подсчитываются только числа в этом массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются. Примеры : =СЧЁТ(A1:A7) возвращает 3 =СЧЁТ(A4:A7) возвращает 2 =СЧЁТ(A1:A7, 2) возвращает 4 РАНГ Возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.) Синтаксис РАНГ(число; ссылка; порядок) Число - это число, для которого определяется ранг. Порядок - это число, определяющее способ упорядочения. Если порядок равен 0 (нулю) или опущен, то Microsoft Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если порядок - это любое ненулевое число, то Microsoft Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания. РАНГ РАНГ присваивает повторяющимся числам одинаковый ранг. Однако, наличие повторяющихся чисел влияет на ранг последующих чисел. Например, для списка целых, если число 10 появляется дважды и имеет ранг 5, то 11 будет иметь ранг 7 (и никакое число не будет иметь ранг 6). Примеры Если ячейки A1:A5 содержат числа 7, 3,5, 3,5, 1 и 2 соответственно, то: =РАНГ(A2;A1:A5;1) равняется 3 =РАНГ(A1;A1:A5;1) равняется 5 Наименование предприятия Товарооборот отчетного года Ранг магазина Магазин№1 56 500 8 Магазин№2 97 230 3 Магазин№3 156 200 1 Магазин№4 52 400 9 Магазин№5 75 100 4 Магазин№6 42 300 10 Магазин№7 58 900 7 Магазин№8 63 510 5 Магазин№9 63 510 5 Магазин№10 123 000 2 МЕДИАНА Возвращает медиану заданных чисел. Медиана - это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Синтаксис МЕДИАНА(число1;число2; ...) Число1, число2, ... - это от 1 до 30 чисел, для которых определяется медиана. Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа. Excel проверяет все числа, содержащиеся в аргументах, которые являются массивами или ссылками (текстовые, логические значения или пустые ячейки игнорируются). Если в множестве четное количество чисел, то функция МЕДИАНА вычисляет среднее двух чисел, находящихся в середине множества. Примеры =МЕДИАНА(1; 2; 3; 4; 5) равняется 3 =МЕДИАНА(1; 2; 3; 4; 5; 6) равняется 3,5 (среднее между 3 и 4). ТЕНДЕНЦИЯ Возвращает значения в соответствии с линейным трендом. Синтаксис ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x; новые_значения_x;конст) Известные_значения_y - это множество известных значений y для соотношения y = mx + b. Известные_значения_x - это необязательное множество значений x, которые известны для соотношения y = mx + b. Новые_значения_x - это новые значения x, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y. Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x. Если новые_значения_x опущены, то предполагается, что они совпадают с известные_значения_x. Пример. Исходные данные для прогнозирования зависимости значения заработной платы персонала от объема товарооборота, тыс. руб. Необходимо найти уравнение корреляционной связи между товарооборотом и заработной платой персонала, определить ее тип и сделать прогноз заработной платы при товарообороте Х = 110 тыс. руб. Товарооборот Х 103,5 103,6 57,5 34,5 69 46 69 46 92 69 Зарплата У 7,2 6,4 3,2 1,6 4 2,4 5,6 1,6 4,8 3,2 Уравнение линейного тренда y = 0,0747x - 1,1571 R 2 = 0,8559 0 1 2 3 4 5 6 7 8 30 40 50 60 70 80 90 100 110 Товарооборот, тыс. руб. З а рп л а та , ты с . ру б . Если в качестве аргументов функции ТЕНДЕНЦИЯ() ввести значения заработной платы персонала и товарооборота за исследуемый период, и в качестве известного значения Х указать 110, то мы получим результат 7,1 79 Логические функции 80 Функции ИСТИНА(), ЛОЖЬ() Логические константы ИСТИНА и ЛОЖЬ в Excel реализованы как функции: ИСТИНА() и =ЛОЖЬ(). Они используются в логических выражениях и в качестве аргументов функций. Выражения с использованием знаков (операторов) сравнения и логических функций. Результатом логического выражения всегда являются значения ИСТИНА или ЛОЖЬ. 81 Примеры логических выражений =A1>A2 результат ЛОЖЬ =A3>0 результат ИСТИНА =A5 Сложные неравенства 0< D2 < 10 = И (0< D2; D2 < 10) 82 Логические функции И, ИЛИ, НЕ 83 Синтаксис И (логическое_значение1; логическое_значение2; ...) Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. ИЛИ (логическое_значение1; логическое_значение2; ...) Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Логическое_значение1, логическое_значение2, … — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ. Аргументы должны быть логическими значениями, массивами или ссылками, которые содержат логические значения. Если указанный интервал не содержит логических значений, то функция возвращает значение ошибки #ЗНАЧ!. 84 Функция НЕ Меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине. Синтаксис =НЕ(логическое_значение) Логическое_значение - это значение или выражение, которое при вычислении дает ИСТИНА или ЛОЖЬ. Если логическое_значение имеет значение ЛОЖЬ, то функция возвращает значение ИСТИНА; если логическое_значение имеет значение ИСТИНА, то функция возвращает значение ЛОЖЬ. Примеры НЕ(ЛОЖЬ) равняется ИСТИНА НЕ(1+1=2) равняется ЛОЖЬ 85 Таблицы истинности для логических функций И, ИЛИ, НЕ А В А И В А В А ИЛИ В Истина Истина Истина Истина Истина Истина Истина Ложь Ложь Истина Ложь Истина Ложь Истина Ложь Ложь Истина Истина Ложь Ложь Ложь Ложь Ложь Ложь А НЕ А Истина Ложь Ложь Истина 86 Примеры =И(A2>80;ИЛИ(A4=10;A1 =ИЛИ(ИСТИНА();A5>A6;НЕ(ЛОЖЬ())) результат ИСТИНА 87 Функция ЕСЛИ Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется при проверке условий для значений и формул. Синтаксис =ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) Или короче: =ЕСЛИ(условие; если да; если нет) 88 Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА, иначе — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения. Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. 89 Пример Разветвление вычислительного процесса в среде Excel можно реализовать при помощи функции ЕСЛИ. Значение Х поместим в ячейку А1 (например, 2). Если ячейка А1 содержит число меньше 5 , то значение функции рассчитывается по формуле х 2 +4х+5. =ЕСЛИ(А1<=5;A1^2+4*A1+5;1/( A1^2+4*A1+5)) результат 17 Финансовые функции ПЛТ Вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки. Синтаксис ППЛАТ(ставка; кпер; пс; бс; тип) Выплаты, возвращаемые функцией ПЛТ включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или гонораров, иногда связываемых со ссудой. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента "ставка" и 4*12 для задания аргумента "кпер" . Ставка - это процентная ставка по ссуде. Кпер - это общее число выплат по ссуде. пс - это текущее значение или общая сумма, которую составят будущие платежи. бс - это будущая сумма, которой нужно достичь после последней выплаты. Если аргумент опущен, он полагается равным 0. Тип - это число 0 или 1, обозначающее, когда должна производиться выплата (в конце периода или в начале периода). Пример Определить ежемесячные выплаты по займу в 10 000 руб. и годовой процентной ставке 8 процентов, которые можно выплачивать в течении 10 месяцев: =ПЛТ(8%/12; 10; 10000) равняется -1037,03 руб. Пример Рассчитать сумму, которую необходимо выплачивать каждый месяц, если Вы дали взаймы 5000 руб. под 12 процентов годовых и хотите получить назад деньги за пять месяцев: =ПЛТ(12%/12; 5; -5000) равняется 1030,20 руб. БС Возвращает будущую стоимость инвестиции на основе периодических постоянных платежей и постоянной процентной ставки. Синтаксис БС(ставка; кпер; плт; пс; тип) Ставка — это процентная ставка за период. Кпер — это общее число периодов выплат годовой ренты. Плата — это выплата, производимая в каждый период; это значение не может меняться в течении всего периода выплат. пс — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент пс опущен, то он полагается равным 0. Тип — это число 0 или 1, обозначающее, когда должна производиться выплата. Примеры БС(0,5%; 10; -200; -500; 1) равняется 2581,40 р. БС(1%; 12; -1000) равняется 12 682,50 р. БС(11%/12; 35; -2000; ; 1) равняется 82 846,25 р. Примеры Предположим, необходимо зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, предполагается вложить 1000 рублей под 6% годовых (что составит в месяц 6%/12 или 0,5%) и вкладывать по 100 рублей в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету в конце 12 месяцев? БЗ(0,5%; 12; -100; -1000; 1) равняется 2301,40 р. КПЕР Возвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки. Синтаксис КПЕР(ставка; плт; пс; бс; тип) Ставка — процентная ставка за период. Плт — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Пс — приведенная к текущему моменту стоимость, т. е. общая сумма, которая на текущий момент равноценна ряду будущих платежей. Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент опущен, предполагается, что он равен 0 (например, бс для займа равна 0). Тип — число 0 или 1, обозначающее срок выплаты (в конце периода или в начале периода соответственно). Пример Годовая процентная ставка – 12%. Выплата за каждый период -100 р. Стоимость на текущий момент -1 000 р. Будущая стоимость 10 000 р. Платежи осуществляются в начале периода. Определить количество периодов выплат для данной инвестиции. =КПЕР(В1/12; В2; В3; В4; 1) равняется 60. Периоды выплат для данной инвестиции, исключая значение будущей стоимости: =КПЕР(A2/12; A3; A4) равняется -9,5. |