Главная | Обратная связь | Поможем написать вашу работу!
МегаЛекции

Функции категории «Ссылки и массивы»




Ÿ ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)- ищет 8888 (пример)

Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

Просматриваемый_вектор - это интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.

Важно! Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.

Вектор_результатов - это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.

· Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение.

· Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

 

ВПР(искомое_значение;инфо_таблица;номер_столбца;интервальный_просмотр)

Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Функция ВПР используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных.

 

Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например, БазаДанных или Список.

 

· Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента инфо_таблица должны быть расположены в возрастающем порядке:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной.

  • Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.
  • Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента инфо_таблица, то функция ВПР возвращает значение ошибки #Н/Д.
  • Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д.

Номер_столбца - это номер столбца в массиве инфо_таблица, в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента инфо_таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента инфо_таблица и так далее. Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!.

 

Пример:

Поиск данных в таблице.

Таблица для выполнения поиска расположена в ячейках D2:F9.

  A B C D E F
  Номер участника     НОМЕР ИМЯ ВЗНОС
          Михаил 124,5
          Петр 254,3
  Имя Федор     Павел 45,8
  Взнос 589,6     Федор 589,6
          Александр 258,3
          Тимофей 25,87
          Евгений  

 

Рабочая таблица A2:B6 расположена т.о., чтобы пользователь мог ввести номер участника в ячейку B2 и в ячейках B4 и B5 получить необходимую информацию из исходной таблицы. Формулы имеют следующий вид:

 

В ячейке B5 è =ВПР($B$2;$D$2:$F$9;2)

В ячейке B6 è =ВПР($B$2;$D$2:$F$9;3)

Формула в ячейке В5 просматривает первый столбец таблицы D2:F9 в поисках величины введенной в ячейку В2 (это значение 205). Она выбирает соответствующее ему (205-ти) значение из столбца, номер которого задан в формуле (это столбец 2) и возвращает полученное значение (имя Федор) в ячейку В5. Аналогично работает формула в ячейке В6.

Если введенное значение не найдено в таблице, формула возвратит #Н/Д. С помощью функции ЕНД можно изменить формулу т.о., чтобы сообщение было более понятным:

=ЕСЛИ(ЕНД(ВПР($B$2;$D$2:$F$9;2;ЛОЖЬ));"Не найдено";ВПР($B$2;$D$2:$F$9;2;ЛОЖЬ)).

 

Функция ГПР работает точно так же, как и ВПР, с той лишь разницей, что просматривает значения ячеек первой строки таблицы (выполняет поиск по горизонтали).

Ÿ ВЫБОР(номер_индекса;значение1;значение2;…)

Использует номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР используется, чтобы выбрать одно значение из списка, в котором может быть до 29 значений. Например, если значения от значение1 до значение7 — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента номер_индекса.

Номер_индекса — это номер выбираемого аргумента-значения. Номер_индекса должен быть числом от 1 до 29, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 29.

· Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, то функция ВЫБОР возвращает значение2 и так далее.

· Если номер_индекса меньше 1 или больше, чем номер последнего значения в списке, то функция ВЫБОР возвращает значение ошибки #ЗНАЧ!.

· Если номер_индекса является дробным, то он усекается до меньшего целого.

Значение1, значение2... — это от 1 до 29 аргументов-значений, из которых ВЫБОР, используя номер_индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, именами, формулами, функциями или текстами.

· Если номер_индекса является массивом, то каждое значение вычисляется при выполнении функции ВЫБОР.

· Аргументы-значения функции ВЫБОР могут быть как ссылками на интервал, так и отдельными значениями. Например, формула:

СУММ(ВЫБОР(2;A1:A10;B1:B10;C1:C10))

эквивалентна формуле:

СУММ(B1:B10),

которая возвращает значение, вычисленное на основе значений в интервале ячеек B1:B10.

В этом примере сначала вычисляется функция ВЫБОР, которая возвращает ссылку на интервал B1:B10. Затем вычисляется функция СУММ, используя интервал B1:B10, то есть результат функции ВЫБОР, в качестве своего аргумента.

Примеры

ВЫБОР(2;"1-ый";"2-ой";"3-ий";"Последний") равняется "2-ой"

СУММ(A1:ВЫБОР(3;A10;A20;A30)) равняется СУММ(A1:A30)

Если ячейка A10 содержит 4, то:

ВЫБОР(A10;"Гвозди";"Винты";"Гайки";"Болты") равняется "Болты"

Если A10-3 равняется 3, то:

ВЫБОР(A10-3;"1-ый";"2-ой";"3-ий";"Последний") равняется "3-ий"

Если ПрошлыеПродажи - это имя, ссылающееся на значение 10 000, то:

ВЫБОР(2;НовыеПродажи;ПрошлыеПродажи;БюджетПродаж) равняется 10 000

Пример:

 

Расчет комиссионных менеджера по продажам. Исходные данные для расчета комиссионных приведены в таблице:

 

Объем продаж за месяц (у.е) Комиссионные (%)
0 - 99999  
10000 - 19999 10,5
20000 – 39999  
40000 и более  

 

Рассмотрим два способа расчета комиссионных:

 

1. С использованием функции ВПР

  A B C D E F G H I
  Объем продаж Комисс %   ФИО Стаж Объем продаж Комисс % Cумма комисс С учетом стажа
        Тормозов          
    10,5   Иванов         627,2
        Крутой     10,5   1270,5
        Умный     10,5   1590,75
        Простой         2786,4
        Кошкин          
        Фунтиков     10,5   1335,6
        Зайчик     10,5   1685,25
        Волк         5027,4
        Баранов          

Ÿ Для определения величины комиссионных (%) в столбце G5:G14 воспользуемся формулой:

Ÿ В ячейке G5 è =ВПР(F5;$A$4:$B$8;2);

Ÿ Скопируем эту формулу в ячейки G6:G14;

Ÿ Для определения суммы комиссионных воспользуемся формулой:

Ÿ В ячейке H5 è =F5*G5/100;

Ÿ Скопируем эту формулу в ячейки H6:H14;

Ÿ Для определения суммы комиссионных с учетом стажа воспользуемся формулой:

Ÿ В ячейке I5 è =H5+H5*E5/100

Ÿ Скопируем эту формулу в ячейки I6:I14;

 

2. С использованием функции ЕСЛИ:

  K L M N O
  ФИО Стаж Объем продаж Cумма комисс С учетом стажа
  Тормозов        
  Иванов       627,2
  Крутой       1270,5
  Умный       1590,75
  Простой       2786,4
  Кошкин        
  Фунтиков       1335,6
  Зайчик       1685,25
  Волк       5027,4
  Баранов        

 

В ячейку N5 внесем формулу:

Ÿ =ЕСЛИ(И(M5>=0;M5<=9999,99);M5*0,08;ЕСЛИ(M5<=19999,99;M5*0,105;

Ÿ ЕСЛИ(M5<=39999,99;M5*0,12;M5*0,14)));

Ÿ Скопируем эту формулу в ячейки N6:N14;

Ÿ В ячейку O5 внесем формулу:

§ =N5+N5*L5/100;

Ÿ Скопируем эту формулу в ячейки O6:O14;

 

 

Поделиться:





Читайте также:





Воспользуйтесь поиском по сайту:



©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...