Лабораторная работа Г. Нижнекамск Как задать простое логическое условие в Excel
 Скачать 3.3 Mb.
|
|
ПРОИЗВЕД() Математическая функция ПРОИЗВЕД вычисляет произведение всех своих аргументов.  Рис. 142 - ПРОИЗВЕД() Мы не будем подробно разбирать данную функцию, поскольку она очень похожа на функцию СУММ, разница лишь в назначении, одна суммирует, вторая перемножает. Более подробно о СУММ Вы можете прочитать в статье Суммирование в Excel, используя функции СУММ и СУММЕСЛИ. ABS() Математическая функция ABS возвращает абсолютную величину числа, т.е. его модуль.  Рис. 143 - ABS() Функция ABS может быть полезна при вычислении количества дней между двумя датами, когда нет возможности определить какая дата начальная, а какая конечная. На рисунке ниже в столбцах A и B представлены даты, причем какая из них начальная, а какая конечная неизвестно. Требуется посчитать количество дней между этими датами. Если просто вычесть из одной даты другую, то количество дней может оказаться отрицательным, что не совсем правильно:  Рис. 144 - ABS() Чтобы избежать этого, воспользуемся функцией ABS:  Рис. 145 - ABS() Нажав Enter, получим правильное количество дней:  Рис. 146 - ABS() КОРЕНЬ() Возвращает квадратный корень из числа. Число должно быть неотрицательным.  Рис. 147 - КОРЕНЬ() Извлечь квадратный корень в Excel можно и с помощью оператора возведения в степень:  Рис. 148 - КОРЕНЬ() СТЕПЕНЬ() Позволяет возвести число в заданную степень.  Рис. 149 - СТЕПЕНЬ() В Excel, помимо этой математической функции, можно использовать оператор возведения в степень:  Рис. 150 - СТЕПЕНЬ() СЛУЧМЕЖДУ() Возвращает случайное число, находящееся между двумя значениями, заданными в качестве аргументов. При каждом пересчете листа значения обновляются.  Рис. 151 - СЛУЧМЕЖДУ() Хоть математических функций в Excel достаточно много, реальную ценность из них представляют лишь единицы. Нет никакого смысла изучать сразу все, поскольку многие могут даже не пригодиться. Математические функции, описанные в этом уроке, – тот самый минимум, который обеспечит уверенную работу в Excel и не перегрузит Вашу память лишней информацией. Текстовые функции Excel в примерах Excel предлагает большое количество функций, с помощью которых можно обрабатывать текст. Область применения текстовых функций не ограничивается исключительно текстом, они также могут быть использованы с ячейками, содержащими числа. В рамках данного урока мы на примерах рассмотрим 15 наиболее распространенных функций Excel из категории Текстовые. СЦЕПИТЬ Для объединения содержимого ячеек в Excel, наряду с оператором конкатенации, можно использовать текстовую функцию СЦЕПИТЬ. Она последовательно объединяет значения указанных ячеек в одной строке.  Рис. 152 – СЦЕПИТЬ СТРОЧН Если в Excel необходимо сделать все буквы строчными, т.е. преобразовать их в нижний регистр, на помощь придет текстовая функция СТРОЧН. Она не заменяет знаки, не являющиеся буквами.  Рис. 153 – СТРОЧН ПРОПИСН Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр. Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.  Рис. 154 - ПРОПИСН ПРОПНАЧ Текстовая функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные преобразует в строчные.  Рис. 155 - ПРОПНАЧ Каждая первая буква, которая следует за знаком, отличным от буквы, также преобразуется в верхний регистр. ДЛСТР В Excel Вы можете подсчитать количество знаков, содержащихся в текстовой строке, для этого воспользуйтесь функцией ДЛСТР. Пробелы учитываются.  Рис. 156 – ДЛСТР ЛЕВСИМВ и ПРАВСИМВ Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.  Рис. 157 - ЛЕВСИМВ и ПРАВСИМВ ПСТР Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.  Рис. 158 – ПСТР СОВПАД Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.  Рис. 159 – СОВПАД СЖПРОБЕЛЫ Удаляет из текста все лишние пробелы, кроме одиночных между словами.  Рис. 160 – СЖПРОБЕЛЫ В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.  Рис. 161 – СЖПРОБЕЛЫ Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:  Рис. 162 – СЖПРОБЕЛЫ Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ. ПОВТОР Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.  Рис. 163 – ПОВТОР НАЙТИ Текстовая функция НАЙТИ находит вхождение одной строки в другую и возвращает положение первого символа искомой фразы относительно начала текста.  Рис. 164 - НАЙТИ Данная функция чувствительна к регистру…  Рис. 165 – НАЙТИ … и может начинать просмотр текста с указанной позиции. На рисунке ниже формула начинает просмотр с четвертого символа, т.е. c буквы «r«. Но даже в этом случае положение символа считается относительно начала просматриваемого текста.  Рис. 166 – НАЙТИ ПОИСК Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.  Рис. 167 – ПОИСК ПОДСТАВИТЬ Заменяет определенный текст или символ на требуемое значение. В Excel текстовую функцию ПОДСТАВИТЬ применяют, когда заранее известно какой текст необходимо заменить, а не его местоположение. Приведенная ниже формула заменяет все вхождения слова «Excel» на «Word»:  Рис. 168 – ПОДСТАВИТЬ ЗАМЕНИТЬ Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен. Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение «2013». Применительно к нашему примеру, формула заменит «2010» на «2013».  Рис. 169 – ЗАМЕНИТЬ Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Обзор ошибок, возникающих в формулах Excel Ошибки в Excel возникают довольно часто. Вы, наверняка, замечали странные значения в ячейках, вместо ожидаемого результата, которые начинались со знака #. Это говорит о том, что формула возвращает ошибку. Чтобы избавиться от ошибки, Вы должны исправить ее причину, а они могут быть самыми разными. Несоответствие открывающих и закрывающих скобок Самым распространенным примером возникновения ошибок в формулах Excel является несоответствие открывающих и закрывающих скобок. Когда пользователь вводит формулу, Excel автоматически проверяет ее синтаксис и не даст закончить ввод, пока в ней присутствует синтаксическая ошибка.  Рис. 170 - Несоответствие открывающих и закрывающих скобок Например, на рисунке выше мы намеренно пропустили закрывающую скобку при вводе формулы. Если нажать клавишу Enter, Excel выдаст следующее предупреждение:  Рис. 171 - Несоответствие открывающих и закрывающих скобок В некоторых случаях Excel предлагает свой вариант исправления ошибки. Вы можете либо согласиться с Excel, либо исправить формулу самостоятельно. В любом случае слепо полагаться на это исправление ни в коем случае нельзя. Например, на следующем рисунке Excel предложил нам неправильное решение.  Рис. 172 - Несоответствие открывающих и закрывающих скобок Ячейка заполнена знаками решетки Бывают случаи, когда ячейка в Excel полностью заполнена знаками решетки. Это означает один из двух вариантов: Столбец недостаточно широк для отображения всего содержимого ячейки. Для решения проблемы достаточно увеличить ширину столбца, чтобы все данные отобразились…  Рис. 173 - Ячейка заполнена знаками решетки …или изменить числовой формат ячейки.  Рис. 174 - Ячейка заполнена знаками решетки Ошибка #ДЕЛ/0! Ошибка #ДЕЛ/0! возникает, когда в Excel происходит деление на ноль. Это может быть, как явное деление на ноль, так и деление на ячейку, которая содержит ноль или пуста.  Рис. 175 - Ошибка #ДЕЛ/0! Ошибка #Н/Д Ошибка #Н/Д возникает, когда для формулы или функции недоступно какое-то значение. Приведем несколько случаев возникновения ошибки #Н/Д: Функция поиска не находит соответствия. К примеру, функция ВПР при точном поиске вернет ошибку #Н/Д, если соответствий не найдено.  Рис. 176 - Ошибка #Н/Д Ошибка #ИМЯ? Ошибка #ИМЯ? возникает, когда в формуле присутствует имя, которое Excel не понимает. Например, используется текст не заключенный в двойные кавычки:  Рис. 177 - Ошибка #ИМЯ? Ошибка #ПУСТО! Ошибка #ПУСТО! возникает, когда задано пересечение двух диапазонов, не имеющих общих точек. Например, =А1:А10 C5:E5 – это формула, использующая оператор пересечения, которая должна вернуть значение ячейки, находящейся на пересечении двух диапазонов. Поскольку диапазоны не имеют точек пересечения, формула вернет #ПУСТО!.  Рис. 178 - Ошибка #ИМЯ? Ошибка #ЧИСЛО! Ошибка #ЧИСЛО! возникает, когда проблема в формуле связана со значением. Например, задано отрицательное значение там, где должно быть положительное. Яркий пример – квадратный корень из отрицательного числа.  Рис. 179 - Ошибка #ЧИСЛО! Ошибка #ССЫЛКА! Ошибка #ССЫЛКА! возникает в Excel, когда формула ссылается на ячейку, которая не существует или удалена. Например, на рисунке ниже представлена формула, которая суммирует значения двух ячеек.  Рис. 180 - Ошибка #ССЫЛКА! Ошибка #ЗНАЧ! Ошибка #ЗНАЧ! одна из самых распространенных ошибок, встречающихся в Excel. Она возникает, когда значение одного из аргументов формулы или функции содержит недопустимые значения. Самые распространенные случаи возникновения ошибки #ЗНАЧ!: Формула пытается применить стандартные математические операторы к тексту.  Рис. 181 - Ошибка #ЗНАЧ! Знакомство с именами ячеек и диапазонов в Excel Создавать и применять формулы в Excel гораздо проще, когда вместо адресов ячеек и диапазонов в них используются имена. Имя выступает как бы идентификатором какого-либо элемента рабочей книги. Имя может присваиваться ячейкам, диапазонам, таблицам, диаграммам, фигурам и т.д. Мы же рассмотрим только имена, назначаемые ячейкам, диапазонам и константам применительно к формулам Excel. Приведем небольшой пример. Представим, что мы продаем элитную косметику и получаем фиксированный процент комиссионных с продаж. На рисунке ниже представлена таблица, которая содержит объем продаж по месяцам, а в ячейке D2 хранится процент комиссионных. Наша задача подсчитать сколько мы заработали за прошедший год.  Рис. 182 - Знакомство с именами ячеек и диапазонов в Excel Для того чтобы подсчитать наш заработок, необходимо просуммировать объемы продаж за весь год, а затем полученный результат умножить на комиссионные. Наша формула будет выглядеть следующим образом:  Рис. 183 - Знакомство с именами ячеек и диапазонов в Excel Такая формула будет вычислять правильный результат, но аргументы, используемые в ней, не совсем очевидны. Чтобы формула стала более понятной, необходимо назначить областям, содержащим данные, описательные имена. Например, назначим диапазону B2:В13 имя Продажи_по_месяцам, а ячейке В4 имя Комиссионные. Теперь нашу формулу можно записать в следующем виде:  Рис. 184 - Знакомство с именами ячеек и диапазонов в Excel Как видите, новая форма записи формулы стала более очевидной и простой для восприятия. Можно пойти еще дальше и для значения комиссионных создать именованную константу. В этом случае исчезнет необходимость выделять под нее отдельную ячейку на рабочем листе Excel. |