Excel: надстройка для функции оборотного ВПР, либо как ВПР; сходить влево

Excel: надстройка для функции оборотного ВПР, либо как ВПР "сходить влево"

Хоть какой юзер, который работает с функцией ВПР (LOOKUP), через некое время соображает, что не весьма комфортно работать с функцией, которая производит поиск лишь справа от столбца, в каком находится разыскиваемое значение. Функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) могут разрешить эту делему, но в целом формула будет намного длиннее и сложнее по сопоставлению с наиболее обычной ВПР.

Пользовательская надстройка rLOOKUP работает как ВПР, но дозволяет создавать оборотный поиск. Функция просто употребляет способы указательным и матч в VBA. Она предоставляется в Excel надстройка для простоты использования.

Скачать надстройку

Загрузите надстройку: .xlam файл (15 Кб).

Сохраните файл на компе и установить его в Excel, выбрав Файл -> Характеристики -> Надстройки. Нажмите на клавишу Перейти . , в открывшемся окне изберите Обзор. Изберите сохраненный файл и нажмите клавишу OK, чтоб окончить установку. Опосля установки покажется сообщение «Reverse_Lookup» в перечне доступных надстроек и функция rLOOKUP становится доступной во всех книжках.

rLU

Опосля установки надстройки функцию rLOOKUP можна отыскать Формулы -> Вставить функцию -> Категория: Определенные юзером -> rLOOKUP.

Описание функции

Аргументы для функции rLOOKUP такие же, как и для функции ВПР, см. снимки экрана ниже. Чтоб выполнить оборотный поиск, просто введите отрицательное число в аргумент ‘Col_Index_Num’ (смотри примеры ниже). Формула будет Lookup в направлении на лево.

Одно из различий меж этими 2-мя функциями, что если Интервальный_просмотр опущен в ВПР, то результата буквально не будет. Но оборотное правильно для rLOOKUP, по дефлоту эта функция возвращает четкое значение. Снова же, это поэтому, что почаще всего юзеры желают получить значение в итоге поиска. Значения ошибок, возвращаемые функцией rLOOKUP подобны тем, которые возвращает ВПР.

Интересно почитать: В excel какая функция

Пример функции

Последующие данные Excel указывает адреса кабинетов, имена служащих и должность.

Для ответа на этот запрос «Кто работает кабинете B43?»мы можем употреблять функцию ВПР:

ВПР ( «B43»; A1:C6;2; ЛОЖЬ) возвращает «Dilbert».

Тем не наименее, если мы желаем знать, «Где находится кабинет Дилберта?», то ВПР не будет работать. Вот здесь понадобится функция rLOOKUP:

rLOOKUP ( «Dilbert»; A1:B6, -2).

Направьте внимание, в аргументах номер столбца указывается отрицательным число -2, что дозволяет создавать поиск в колонке слева.

Совершенно, функция работает в обоих направлениях:

rLOOKUP ( «B43»;A1:C6;2) возвращает «Dilbert» и

rLOOKUP ( «Dilbert»; A1:B6; -2) возвращает «B43».

Остальные примеры приведены ниже:

Не забудьте поначалу установить надстройку, в неприятном случае будет ворачиваться ошибка #NAME?.

Как работает функция

Функция употребляет функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) для пуска оборотного поиска.

Подмена Excel на Python

Проведя практически 10 лет с моей первой любовью к Excel, настало время двигаться далее и находить наилучшую половину того, кто в собственных ежедневных делах со мной, еще лучше и резвее, и кто может отдать мне преимущество в сложные технологические времена, когда новейшие технологии весьма стремительно угнетаются кое-чем новеньким. Мысль заключается в том, чтоб воспроизвести практически все многофункциональные способности Excel в Python, будь то обычной фильтр либо непростая задачка сотворения массива данных из строк и обработки их для получения умопомрачительных результатов.

Применяемый тут подход заключается в том, чтоб начать с обычных задач и перейти к сложным вычислительным задачкам. Я призываю вас повторить шаги без помощи других для наилучшего осознания.

Источником вдохновения для сотворения чего-то подобного послужило отсутствие бесплатного учебного пособия, которое практически дает все. Я пристально читаю и смотрю за документацией по Python, и вы отыщите много вдохновения на этом веб-сайте.

Интересно почитать: Функция счет если в excel по нескольким критериям

Ссылка на репозиторий GitHub

Импорт файлов Excel в DataFrame Pandas

Первым шагом является импорт файлов Excel в DataFrame, чтоб мы могли делать с ним все наши задачки. Я буду показыватьread_excelспособ панд, который поддерживаетXLSтакжеXLSXРасширения файлов.
read_csvтак же, как внедрениеread_excelМы не будем углубляться, но я поделюсь примером.

Хотяread_excelСпособ содержит в себе миллион аргументов, но я ознакомлю вас с более всераспространенными аргументами, которые весьма понадобятся в ежедневных операциях.

Хотя способ read_excel содержит миллион аргументов, я ознакомлю вас с более всераспространенными из их, которые весьма понадобятся в ежедневных операциях.
Я буду употреблять эталон набора данных Iris, который свободно доступен в Вебе для образовательных целей.

Пожалуйста, перейдите по ссылке ниже, чтоб загрузить набор данных и сохранить его в той же папке, где вы сохраняете собственный файл Python

1-ый шаг — импортировать нужные библиотеки в Python.

Мы можем импортировать данные электрической таблицы в Python, используя последующий код:

pandas.read_excel (io, sheet_name = 0, header = 0, names = None, index_col = None, parse_cols = None, usecols = None, squeeze = False, dtype = None, engine = None, конвертеры = None, true_values = None, false_values = нет, skiprows = нет, nrows = нет, na_values = нет, keep_default_na = True, многоречиво = False, parse_dates = False, date_parser = нет, тыщи = нет, комментарий = нет, skip_footer = 0, skipfooter = 0, convert_float = 0 True, mangle_dupe_cols = True, ** kwds)

Так как имеется огромное количество доступных аргументов, давайте разглядим более нередко применяемые аргументы.

Принципиальные функции read_excel для Pandas

Если мы используем путь для нашего локального файла по дефлоту, он разбит знаком «», но python воспринимает «/», потому сделайте так, чтоб поменять косую черту либо просто добавить файл в ту же папку, где находится ваш файл python. Если для вас требуется подробное разъяснение выше, обратитесь к статье ниже среднего.https://medium.com/@ageitgey/python-3-quick-tip-the-easy-way-to-deal-with-file-paths-on-windows-mac-and-linux-11a072b58d5f

Интересно почитать: Функция среднего значения в excel

Мы можем употреблять Python для сканирования файлов в каталоге и избрать те, которые нам необходимы.

Импортировать определенный лист

По дефлоту 1-ый лист в файле импортируется в фрейм данных как есть.
Используя аргумент sheet_name, мы можем очевидно указать лист, который мы желаем импортировать. Значение по дефлоту 0, другими словами 1-ый лист в файле.
Мы можем или упомянуть имя листа (ов), или передать целочисленное значение для ссылки на индекс листа

Внедрение столбца из листа в качестве индекса

Если очевидно не обозначено другое, в DataFrame добавляется столбец индекса, который по дефлоту начинается с 0.
Используя аргумент index_col, мы можем манипулировать столбцом индекса в нашем фрейме данных, если мы установим значение 0 из none, он будет употреблять 1-ый столбец в качестве нашего индекса.

Пропустить строчки и столбцы

Характеристики read_excel по дефлоту подразумевают, что 1-ая строчка представляет собой перечень имен столбцов, который автоматом врубается как метки столбцов в DataFrame.
Используя такие аргументы, как skiprows и header, мы можем манипулировать поведением импортированного DataFrame.

Импортировать определенные столбцы

Используя аргумент usecols, мы можем указать, необходимо ли импортировать определенный столбец в наш DataFrame.

Это не конец доступных функций, но это начало, и вы сможете поиграть с ними в согласовании с вашими требованиями

Давайте поглядим на данные с 10 000 футов

Так как сейчас у нас есть наш DataFrame, давайте поглядим на данные с различных точек зрения, просто чтоб освоить их /
У панд есть огромное количество доступных функций, которые мы можем употреблять. Мы используем некие из их, чтоб получить представление о нашем наборе данных.