Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
В случае, если данные хранятся в столбцах: Например, если известен Код страны-изготовителя (критерий) и необходимо из таблицы получить данные столбца Компания-Изготовитель, которые находятся правее критерия, то удобней воспользоваться функцией ВПР (применяется для вертикальных таблиц). Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 20 Название страны Код страны-изготовителя Компания-изготовитель RUSSIA RU НПО «Знак» JAPAN JP Casio FRANCE FR Tefal ПОИСКПОЗ+ИНДЕКС КРИТЕРИЙ ВПР Если по Коду страны-изготовителя (критерий) требуется определить Название страны, которое находится левее критерия, то стоит воспользоваться функциями ПОИСКПОЗ и ИНДЕКС. В случае, если данные хранятся в строках: Например, если известен Код страны-изготовителя (критерий) и необходимо из таблицы получить данные строки Компания-Изготовитель, которые находятся ниже критерия, то удобней воспользоваться функцией ГПР (применяется для горизонтальных таблиц). Название страны RUSSIA JAPAN FRANCE ПОИСКПОЗ+ИНДЕКС Код страны-изготовителя RU JP FR КРИТЕРИЙ Компания-изготовитель НПО «Знак» Casio Tefal ГПР Если по Коду страны-изготовителя (критерий) требуется определить Название страны, которое находится выше критерия, то стоит воспользоваться функциями ПОИСКПОЗ и ИНДЕКС. ВПР (Искомое_значение;Таблица;Номер_столбца;Интервальный_просмотр) – ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. VLOOKUP( Lookup_value;Table_array;Col_index_num;Range_lookup ) Искомое_значение [Lookup_value] –. значение, по которому ищем совпадение в первом столбце другой таблицы. Таблица [Table_array] – таблица, в которой в первом столбце осуществляется поиск искомого значения. Необходимо выделить таблицу таким образом, чтобы в первом левом столбце было искомое значение и правее, включая столбец для ответа по задаче. Как правило, таблица при копировании формулы должна оставаться неизменной, поэтому она должна быть в абсолютной адресации. Номер_столбца [Col_index_lookup] – номер столбца-ответа по задаче (целое число), считается в выделенной таблице. Просматриваемый массив [Range_lookup] – число 0 или 1. 0 (Ложь [False]) – ищет первое точное совпадение при просмотре сверху вниз (если не находит – #Н/Д [#N/A]). Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 21 1 (Истина [True]) – если нет совпадения, то выдает max_значение < искомого, при этом выделенная таблица должна быть отсортирована по первому столбцу по возрастанию. ПРИМЕР: Определить значение процента бонуса каждого сотрудника в зависимости от его кода. =ВПР(D2;$H$2:$J$11;3;0) – ищет значение IVN (ячейка D2) в ячейках 1-го столбца (H) указанной таблицы. Результат формулы – значение ячейки 3-го столбца (J) выделенной таблицы строки со значением IVN. ПРАКТИКУМ: Открыть файл 01_4 Функции Ссылки и массивы. На листе ВПР1, Товары 2010, Товары 2011 ВПР2 выполнить задание. Сохранить изменения в файле. ГПР (Искомое_значение;Таблица;Номер_строки;Интервальный_просмотр) – ищет значение в крайней верхней строке таблицы и возвращает значение в том же столбце из указанной строки таблицы. HLOOKUP( Lookup_value;Table_array;Row_index_num; Range_lookup ) Искомое_значение [Lookup_value] –. значение, которое должно быть найдено в первой строке другой таблицы. Таблица [Table_array] – таблица, в которой в первой стоке осуществляется поиск искомого значения. Необходимо выделить таблицу таким образом, чтобы в первом стоке было искомое значение и ниже строки, включая строку ответа по задаче. Номер_стоки [Row_index_lookup] – номер строки-ответа по задаче (целое число), считается в выделенной таблице. Просматриваемый массив [Range_lookup] – число 0 или 1. 0 (Ложь [False]) – ищет первое точное совпадение (если не находит - #Н/Д [#N/A]). 1 (Истина [True]) – если нет совпадения, то выдает max_значение < искомого условие отбора (критерий), при этом выделенная таблица должна быть отсортирована по первой строке по возрастанию. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 22 ПРИМЕР: Определить для указанного Кода Заказа 10250 значение Кода Сотрудника из таблицы, расположенной в строках с 1-й по 4. =ГПР(A8;1:3;3;0) – ищет значение кода заказа 10250 (ячейка A8) в ячейках 1-й строки (Код заказа) указанной таблицы. Результат формулы – значение ячейки 3-й строки таблицы (Код Сотрудника) столбца со значением кода заказа 10259. ПРАКТИКУМ: Открыть файл 01_4 Функции Ссылки и массивы. На листе ГПР выполнить задание. Сохранить изменения в файле. ПОИСКПОЗ (Искомое_значение;Просматриваемый_массив;Тип_сопоставления) – находит относительное положение элемента в диапазоне данных (поиск позиции). MATCH( Lookup_value; Lookup_array; Match_type ) Искомое_значение [Lookup_value] – значение, для которого определяется относительное положение в диапазоне данных. Просматриваемый_массив [Lookup_array] – диапазон ячеек, в котором производится поиск. Чаще один столбец или одна строка; если указать несколько, то ищет совпадения в каждом. Тип_сопоставления [Match_type] – может принимать значения 1, 0 и -1. Определяет, каким образом Искомое_значение сопоставляется со значениями в аргументе Просматриваемый_массив. 0 1 (значение по умолчанию) -1 Первое точное совпадение при просмотре сверху вниз (слева направо) Max_значение≤Искомое, сортировка по возрастанию Min_значение ≥ Искомое, сортировка по убыванию 10 Пример: 30→2 25→# Н/Д 10 Пример: 25→2 30→3 30 Пример: 25→1 10→3 30 20 20 20 30 10 Если функция ПОИСКПОЗ не находит соответствующего значения при точном совпадении, то возвращается значение ошибки #Н/Д [#N/A]. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 23 ПРИМЕР: Определить номер строки в таблице, в которой находится значение Кода заказа 10250. =ПОИСКПОЗ(G2;D:D;0) – находит для значения из ячейки G2 (Код заказа 10250) относительную позицию в просматриваемом массиве – в столбце D (Код Заказа). ИНДЕКС (Массив;Номер_строки;Номер_столбца) – возвращает значение ячейки из диапазона, заданной номером строки и номером столбца. INDEX( Array;Row_num;Column_num ) Массив [Array] – таблица (массив), состоит из строк и столбцов. Если Массив содержит только один столбец (строку), то соответствующий аргумент Номер_строки или Номер столбца не является обязательным. Номер_строки [Row_num] – номер строки в массиве, из которой нужно определить значение. Если значение не указано, то требуется указать номера столбца. Номер_столбца [Column_num] – номер столбца в массиве, из которого определяется значение. Если значение не указано, то требуется указать номер строки. ПРИМЕР: Определить значение Получателя, если известен номер строки, в которой он расположен. =ИНДЕКС(B:B;G3)– определение значения Получателя в таблице (столбец В) с заданным номером строки (значение ячейки G3). Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 24 Функции ПОИСКПОЗ [MATCH] и ИНДЕКС[INDEX], применяемые последовательно, позволяют по найденному значению в одном столбце найти соответствующее значение из другого столбца. ПРИМЕР: Определить значение Получателя для указанного Кода Заказа 10250. =ИНДЕКС(B:B;ПОИСКПОЗ(G2;D:D;0))– определение Получателя (данные столбца В) в номере строки, вычисляемым в столбце D для указанного Кода Заказа (значение ячейки G2). ПРАКТИКУМ: Открыть файл 01_4 Функции Ссылки и массивы. На листе ИНДЕКС выполнить задание. Сохранить изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 4-1, 4-2 и 4-3. Логические функции Логические функции являются неотъемлемыми компонентами многих формул. Они используются в случаях, когда результат обработки зависит от выполнения некоторого условия, заданного в виде логического выражения. ЕСЛИ (Лог_выражение;Значение_если_истина;Значение_если_ложь) – возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. IF( Logical_test;Value_if_true;Value_if_false ) Лог_выражение [Logical_test] – выражение, относительно которого можно судить: истина это или ложь. Необходимо задать условие, используя ссылки на адреса ячеек: >, >=, <, <=, <>, =. Можно использовать функции: И [AND], ИЛИ [OR]. Значение_если_истина [Value_if_True] – ввести текст, число или формулу для определения значения в случае, если условие будет выполнено. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 25 Значение_если_ложь [Value_if_False] – ввести текст, число или формулу для определения значения в случае, если условие не будет выполнено. ПРИМЕР: Рассчитать премию сотрудникам, исходя из условия. Если стаж сотрудника более 8 лет, то премия составляет 30% от оклада, в противном случае –5000 р. =ЕСЛИ(C2>8;D2*30%;5000) – в зависимости от стажа работы (ячейка C2) вычисляется премия: если стаж работы более 8 лет, то премия равна 30% от оклада (ячейка D2), в противном случае – 5000 р. ПРАКТИКУМ: Открыть файл 01_5 Логические функции. На листе Если1 выполнить задание. Сохранить сделанные изменения. Функции, объединяющие несколько условий в одно. Если все условия должны быть выполнены одновременно, то стоит воспользоваться функцией И. И (Логическое_значение1;Логическое_значение2;) – проверяет, все ли аргументы имеют значение ИСТИНА, и возвращает значение ИСТИНА, если истинны все аргументы. AND( Logical1;Logical2; ) Если достаточно выполнения только одного из указанных условий, то стоит воспользоваться функцией ИЛИ. ИЛИ (Логическое_значение1;Логическое_значение2;) – проверяет, имеет ли хотя бы один из аргументов значение ИСТИНА. Значение ЛОЖЬ возвращается только в том случае, если все аргументы имеют значение ЛОЖЬ. OR( Logical1;Logical2; ) =И(D2>10;E2<=100) → ИСТИНА =ИЛИ(D2>10;E2<=100) → ИСТИНА =И(D3>10;E3<=100) → ЛОЖЬ =ИЛИ(D3>10;E3<=100) → ИСТИНА =И(D4>10;E4<=100) → ЛОЖЬ =ИЛИ(D4>10;E4<=100) → ЛОЖЬ Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 26 Использование только функций И и ИЛИ позволяет получить ответ в ячейке как ИСТИНА или ЛОЖЬ, поэтому их часто используют в логической функции ЕСЛИ, чтобы задать более сложные условия. ПРИМЕР: Выдать Бонус в размере 55 тыс. р. только тем сотрудникам, стаж работы которых не менее 10 лет и коэффициент надежности при этом более 0,8. =ЕСЛИ(И(B2>=10;D2>0,8);55000;0) – в зависимости от стажа работы (ячейка В2) и коэффициента надежности (ячейка D2) вычисляется Бонус: если стаж работы не менее 10 лет и при этом коэффициент надежности более 0,8, то размер Бонуса 55000 р., иначе бонуса нет – 0 р. ПРИМЕР: Определить каким Клиентам полагается подарок. Подарок выдается только в случаях, если Стоимость заказа не менее 1000$ или Количество наименований в заказе не менее 10. =ЕСЛИ(ИЛИ(B2>=10000;C2>=10);"ПОДАРОК";"") – в зависимости от стоимости заказа (ячейка В2) и количества наименований в заказе (ячейка C2) определяется наличие/отсутствие Подарка. Вложенность функции ЕСЛИ ПРИМЕР: Вычислить годовую премию сотрудникам как процент от оклада в зависимости от стажа работы: 100% при стаже менее 5 лет, 150% при стаже от 5 до 10 лет включительно и 200% при стаже свыше 10 лет. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 27 Логическая схема решения задачи с вложенной функцией ЕСЛИ: Сперва с помощью функции ЕСЛИ определяется % премии: =ЕСЛИ(C3<=5;100%;ЕСЛИ(C3<=10;150%;200%)), где C3 – ячейка со значение стажа работы. Затем всё умножается на значение оклада D3 и конечная формула определения премии: =ЕСЛИ(C3<=5;100%;ЕСЛИ(C3<=10;150%;200%))*D3. Альтернативный варианты: =ЕСЛИ(C3<=5;100%;ЕСЛИ(И(C3>=5;C3<=10);150%;200%))*D3 =ЕСЛИ(C3<=5;D3*100%;ЕСЛИ(C3<=10;D3*150%;D3*200%)) ПРАКТИКУМ: Открыть файл 01_5 Логические функции. На листе Если2 выполнить задание. Сохранить сделанные изменения. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 5-1, 5-2, 5-3, 5-4. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 28 ЕСЛИОШИБКА (Значение;Значение_если_ошибка) – возвращает указанное значение, если вычисление по формуле вызывает ошибку, в противном случае возвращает результат формулы. IFERROR( Value;Value_if_error ) Значение [Value] – обязательный аргумент (формула), проверяемый на возникновение ошибок. Значение_если_ошибка [Value_if_error] – обязательный аргумент. Значение, возвращаемое при ошибке при вычислении по формуле – может быть числом, текстом или формулой для вычисления. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!. ПРИМЕР: Вычислить среднюю сумму заказа. В случае, если сотрудник не оформил не один заказ, то вывести текст в ячейке «Заказов нет» =ЕСЛИОШИБКА(B2/C2;"Заказов нет") – при возникновении ошибки (деление на ноль), в ячейке выводится текст "Заказов нет". ПРАКТИКУМ: Открыть файл 01_5 Логические функции. На листе Если3 выполнить задание. На листе Если4 выполнить задание самостоятельно. Сохранить сделанные изменения. Текстовые функции Помимо анализа числовых данных, так же встречаются и задачи по обработке текстовых данных. СЦЕПИТЬ (Текст1;Текст2;) – объединение несколько текстовых строк в одну. CONCATENATE( Text1;Text2; ) Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 29 =СЦЕПИТЬ(A2;" ";B2;" ";C2) – объединение фамилии (ячейка А2) с именем (ячейка В2) и отчеством (ячейка С2) с разделителями – пробелами " ". Альтернативный способ объединения текстовых ячеек с разделителями пробелами " " по формуле: =A2&" "&B2&" "&C2. СЖПРОБЕЛЫ (Текст) – удаляет из текста лишние пробелы, кроме одиночных между словами. TRIM( Text ) ПРИМЕР: Удалить лишние пробелы из исходной строки. В программе Excel нет команды изменения регистра для текстовых данных, однако существуют функции, которые помогают решить эту проблему. ПРОПИСН (Текст) – делает все буквы в строке текста прописными. UPPER( Text ) ПРОПИСН("Специалист") → СПЕЦИАЛИСТ СТРОЧН (Текст) – делает все буквы в строке текста строчными. LOWER( Text ) СТРОЧН("СПЕЦИАЛИСТ") → специалист ПРОПНАЧ (Текст) – делает прописную первую букву в каждом слове текста, преобразуя все другие буквы в строчные. PROPER( Text ) |