Съдържание
Вчера на маратона 30 Excel функции за 30 дни забавлявахме се с функцията REP (ПОВТОРЕНИЕ), като създавате диаграми в клетка и я използвате за просто броене. Понеделник е и отново е време да си сложим шапките за мислители.
На 16-ия ден от маратона ще проучим функцията ПОГЛЕДНИ НАГОРЕ (ПРЕГЛЕД). Това е близък приятел ВОЛОКУП (VLOOKUP) и ПРЕГЛЕД (GPR), но работи малко по-различно.
Така че, нека изучим теорията и да тестваме функцията на практика ПОГЛЕДНИ НАГОРЕ (ПРЕГЛЕД). Ако имате допълнителна информация или примери как да използвате тази функция, моля, споделете ги в коментарите.
Функция 16: ТЪРСЕНЕ
функция ПОГЛЕДНИ НАГОРЕ (LOOKUP) връща стойност от един ред, една колона или от масив.
Как мога да използвам функцията LOOKUP?
функция ПОГЛЕДНИ НАГОРЕ (LOOKUP) връща резултата в зависимост от стойността, която търсите. С негова помощ вие ще можете:
- Намерете последната стойност в колона.
- Намерете последния месец с отрицателни продажби.
- Преобразувайте постиженията на учениците от проценти в буквени оценки.
ТЪРСЕНЕ НА СИНТАКСИС
функция ПОГЛЕДНИ НАГОРЕ (LOOKUP) има две синтактични форми – вектор и масив. Във векторна форма функцията търси стойността в дадена колона или ред, а във формата на масив тя търси стойността в първия ред или колона на масива.
Векторната форма има следния синтаксис:
LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
- търсена_стойност (търсена_стойност) – Може да бъде текст, число, булева стойност, име или връзка.
- търсещ_вектор (lookup_vector) – Диапазон, състоящ се от един ред или една колона.
- резултат_вектор (result_vector) – диапазон, състоящ се от един ред или една колона.
- диапазони на аргументи търсещ_вектор (търсен_вектор) и резултат_вектор (result_vector) трябва да бъде с еднакъв размер.
Формата на масива има следния синтаксис:
LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)
- търсена_стойност (търсена_стойност) – Може да бъде текст, число, булева стойност, име или връзка.
- търсенето се извършва по размерността на масива:
- ако масивът има повече колони отколкото редове, тогава търсенето се извършва в първия ред;
- ако броят на редовете и колоните е еднакъв или има повече редове, тогава търсенето се извършва в първата колона.
- функцията връща последната стойност от намерения ред/колона.
Капани ТЪРСЕНЕ (ПРЕГЛЕД)
- Във функция ПОГЛЕДНИ НАГОРЕ (ПРЕГЛЕД) няма опция за търсене на точно съвпадение, което е в ВОЛОКУП (VLOOKUP) и в ПРЕГЛЕД (GPR). Ако няма търсена стойност, тогава функцията ще върне максималната стойност, която не надвишава търсената стойност.
- Търсеният масив или вектор трябва да бъдат сортирани във възходящ ред, в противен случай функцията може да върне неправилен резултат.
- Ако първата стойност в търсения масив/вектор е по-голяма от търсената стойност, тогава функцията ще генерира съобщение за грешка #AT (#N/A).
Пример 1: Намиране на последната стойност в колона
Под формата на функция масив ПОГЛЕДНИ НАГОРЕ (LOOKUP) може да се използва за намиране на последната стойност в колона.
Помощ за Excel цитира стойност 9,99999999999999Е + 307 като най-голямото число, което може да бъде записано в клетка. В нашата формула тя ще бъде зададена като желаната стойност. Предполага се, че такова голямо число няма да бъде намерено, така че функцията ще върне последната стойност в колона D.
В този пример е разрешено числата в колона D да не се сортират, освен това могат да се срещнат текстови стойности.
=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)
Пример 2: Намерете последния месец с отрицателна стойност
В този пример ще използваме векторната форма ПОГЛЕДНИ НАГОРЕ (ПРЕГЛЕД). Колона D съдържа стойностите на продажбите, а колона E съдържа имената на месеците. В някои месеци нещата не вървяха добре и в клетките със стойности на продажбите се появяваха отрицателни числа.
За да намерите последния месец с отрицателно число, формулата с ПОГЛЕДНИ НАГОРЕ (LOOKUP) ще провери за всяка продажна стойност, че е по-малка от 0 (неравенство във формулата). След това разделяме 1 в резултата стигаме до едно от двете 1, или съобщение за грешка #DIV/0 (#СЕКЦИЯ/0).
Тъй като желаната стойност е 2 не е намерен, функцията ще избере последния намерен 1и връща съответната стойност от колона E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)
Обяснение: В тази формула, вместо аргумента търсещ_вектор (търсен_вектор) заместен израз 1/(D2:D8<0), който образува масив в RAM паметта на компютъра, състоящ се от 1 и стойности на грешки #DIV/0 (#СЕКЦИЯ/0). 1 показва, че съответната клетка в диапазона D2:D8 съдържа стойност, по-малка от 0, и грешката #DIV/0 (#DIV/0) – какво е по-голямо или равно на 0. В резултат на това нашата задача е да намерим последния 1 в създадения виртуален масив и въз основа на това върнете името на месеца от диапазона E2:E8.
Пример 3: Преобразуване на постиженията на учениците от проценти в буквени оценки
Преди това вече сме решавали подобен проблем с помощта на функцията ВОЛОКУП (VPR). Днес ще използваме функцията ПОГЛЕДНИ НАГОРЕ (VIEW) във векторна форма за преобразуване на постиженията на учениците от проценти в буквени оценки. За разлика от ВОЛОКУП (VLOOKUP) за функция ПОГЛЕДНИ НАГОРЕ (ИЗГЛЕД) Няма значение дали процентите са в първата колона на таблицата. Можете да изберете абсолютно всяка колона.
В следващия пример резултатите са в колона D, сортирани във възходящ ред, а съответните им букви са в колона C, отляво на колоната, в която се търси.
=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)