Лабораторная работа Г. Нижнекамск Как задать простое логическое условие в Excel
 Скачать 3.3 Mb.
|
|
Векторная форма Функция ПРОСМОТР (в векторной форме) просматривает диапазон, который состоит из одной строки или одного столбца. Находит в нем заданное значение и возвращает результат из соответствующей ячейки второго диапазона, который также состоит из одной строки или столбца. Пример 1 На рисунке ниже представлена таблица, где указаны номера телефонов и фамилии сотрудников. Наша задача по фамилии сотрудника определить его номер телефона.  Рис. 101 - Векторная форма (Пример 1) В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР. Формула будет выглядеть следующим образом:  Рис. 102 - Векторная форма (Пример 1) Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор. Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter, убеждаемся, что все верно.  Рис. 103 - Векторная форма (Пример 1) Пример 2 Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность. На рисунке ниже Вы можете увидеть один из таких примеров:  Рис. 104 - Векторная форма (Пример 2) Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:  Рис. 105 - Векторная форма (Пример 2) При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат. Так вот коротко и на примерах мы познакомились с функцией ПРОСМОТР и научились использовать ее в рабочих книгах Excel. Надеюсь, что данная информация оказалась для Вас полезной, и Вы обязательно найдете ей применение.  Рис. 106 - Векторная форма (Пример 2) В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.  Рис. 107 - Векторная форма (Пример 2) Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов: 0 — функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.  Рис. 108 - Векторная форма (Пример 2) 1 или вовсе опущено — функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.  Рис. 109 - Векторная форма (Пример 2) -1 — функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.  Рис. 110 - Векторная форма (Пример 2) В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС. Функция ИНДЕКС в Excel Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.  Рис. 111 - Функция ИНДЕКС в Excel Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:  Рис. 112 - Функция ИНДЕКС в Excel Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно. Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):  Рис. 113 - Функция ИНДЕКС в Excel Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):  Рис. 114 - Функция ИНДЕКС в Excel Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой. На рисунке ниже представлена таблица, которая содержит месячные объемы продаж каждого из четырех видов товара. Наша задача, указав требуемый месяц и тип товара, получить объем продаж.  Рис. 115 - Функция ИНДЕКС в Excel Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 — тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter: =ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))  Рис.116 - Функция ИНДЕКС в Excel Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:  Рис. 117 - Функция ИНДЕКС в Excel В данной формуле функция ИНДЕКС принимает все 3 аргумента: 1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.  Рис. 118 - Функция ИНДЕКС в Excel 2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:  Рис. 119 - Функция ИНДЕКС в Excel 3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:  Рис. 120 - Функция ИНДЕКС в Excel Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид: =ИНДЕКС(B2:E13;D15;D16)  Рис. 121 - Функция ИНДЕКС в Excel На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel – ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Статистические функции Excel, которые необходимо знать Функции категории Статистические предназначены в первую очередь для анализа диапазонов ячеек в Excel. С помощью данных функций Вы можете вычислить наибольшее, наименьшее или среднее значение, подсчитать количество ячеек, содержащих заданную информацию, и т.д. Данная категория содержит более 100 самых различных функций Excel, большая часть из которых предназначена исключительно для статистических расчетов и обычному рядовому пользователю покажется темным лесом. Мы же в рамках этого урока рассмотрим самые полезные и распространенные функции данной категории.  Рис. 122 - Статистические функции Excel, которые необходимо знать СРЗНАЧ() Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.  Рис. 123 - СРЗНАЧ() Данная функция может принимать до 255 аргументов и находить среднее сразу в нескольких несмежных диапазонах и ячейках:  Рис. 124 - СРЗНАЧ() Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13  Рис. 125 - СРЗНАЧ() Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).  Рис. 126 - СРЗНАЧ() Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:  Рис. 127 - СРЗНАЧ() СРЗНАЧЕСЛИ() Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ. Следующая формула вычисляет среднее чисел, которые больше нуля:  Рис. 128 - СРЗНАЧЕСЛИ() В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее. Допустим, в таблице ниже собрана статистика по стоимости лекарств в городе. В одной аптеке лекарство стоит дороже, в другой дешевле. Чтобы посчитать стоимость анальгина в среднем по городу, воспользуемся следующей формулой:  Рис. 129 - СРЗНАЧЕСЛИ() Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН, которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям. МАКС() Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:  Рис. 130 - МАКС() МИН() Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:  Рис. 131 - МИН() НАИБОЛЬШИЙ() Возвращает n-ое по величине значение из массива числовых данных. Например, на рисунке ниже мы нашли пятое по величине значение из списка.  Рис. 132 - НАИБОЛЬШИЙ() НАИМЕНЬШИЙ() Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.  Рис. 133 - НАИМЕНЬШИЙ() МЕДИАНА() Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел. Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.  Рис. 134 - МЕДИАНА() МОДА() Возвращает наиболее часто встречающееся значение в массиве числовых данных.  Рис. 135 - МОДА() Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости. Как известно, категория Статистические в Excel содержит более 100 самых разноплановых функций. Но, как показывает практика, львиная доля этих функций практически не применяется, а особенно начинающими пользователями. Математические функции Excel, которые необходимо знать В категории математические и тригонометрические представлено около 80 самых различных функций Excel, начиная от незаменимых суммирования и округления, и заканчивая мало кому известным рядом тригонометрических функций. В рамках данного урока мы проведем обзор только самых полезных математических функций Excel.  Рис. 136 - Математические функции Excel, которые необходимо знать ОКРУГЛ() Математическая функция ОКРУГЛ позволяет округлять значение до требуемого количества десятичных знаков. Количество десятичных знаков Вы можете указать во втором аргументе. На рисунке ниже формула округляет значение до одного десятичного знака:  Рис. 137 - ОКРУГЛ() Если второй аргумент равен нулю, то функция округляет значение до ближайшего целого:  Рис. 138 - ОКРУГЛ() Второй аргумент может быть и отрицательным, в таком случае значение округляется до требуемого знака перед запятой:  Рис. 139 - ОКРУГЛ() Такое число как 231,5 функция ОКРУГЛ округляет в сторону удаления от нуля:  Рис. 140 - ОКРУГЛ() Если необходимо округлить число в сторону большего или меньшего по модулю значения, можно воспользоваться функциями ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.  Рис. 141 - ОКРУГЛ()  Рис. 141 - ОКРУГЛ() |