Как в экселе впр: инструкция на примере / Skillbox Media

Как использовать функцию ВПР (VLOOKUP) в Excel

Функция VLOOKUP (рус. — ВПР) входит в топ-3 самых популярных в Excel — после SUM и AVERAGE. Ее задача — найти нужное значение в таблице данных и вывести его в заданную ячейку.

VLOOKUP ищет значение по вертикали, то есть среди строчек (на это указывает первая буква V — Vertical). Кроме того, в Excel есть функция HLOOKUP, которая делает горизонтальный поиск, по столбцам. Но нашему глазу понятнее, когда данные представлены несколькими заглавными столбцами, а основная информация записана построчно, поэтому VLOOKUP — более популярна. Рассмотрим эту функцию детальнее. 

*В статье используются формулы из английской версии MS Excel. 

Зачем нужна функция VLOOKUP 

Синтаксис функции VLOOKUP одинаковый как в Excel, так и в Google Spreadsheets. Он выглядит так:

= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

= VLOOKUP (искомое значение, массив таблицы, номер столбца, [интервальный просмотр]), где

  • искомое значение (что ищем?) — искомое значение или ссылка на ячейку с искомым значением
  • массив таблицы (где ищем?) — ссылка на диапазон ячеек, в первом столбце которого будет поиск искомого значения
  • номер столбца — номер столбца в диапазоне, из которого будет возвращено значение
  • [интервальный просмотр] — не обязательный, но важный параметр, который отвечает на вопрос «отсортирован ли по возрастанию первый столбец диапазона поиска?». Если отсортирован — мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. Если этот параметр опущен — он по умолчанию равен 1.

Рассмотрим пример. У нас есть таблица с тремя столбцами — артикул, наименование товара и его стоимость. 

Допустим, нам нужно найти стоимость товара с артикулом 1101. На скрине интуитивно понятно, что она равна 58, но когда в таблице тысячи строк — поиск усложняется. С функцией VLOOKUP разобраться будет легче.

Чтобы найти стоимость артикула 1101 — в ячейке, куда необходимо вывести значение, прописываем формулу 

= VLOOKUP

  • <что ищем?> — искомое значение может быть прописано как ссылка на ячейку (в нашем случае ячейка F2) либо как числовое значение 1101 (если нужно найти текстовое значение — его выделяем кавычками “”)
  • <где ищем?> — прописываем диапазон таблицы: он может быть записан как A2:C7 либо как именованный диапазон
  • <номер столбца> — в нашем случае стоимость товара прописана в третьем по счету столбце

*Результат формулы, записанной тремя разными способами, будет одинаковым.  

В этом примере 4-й параметр — [интервальный просмотр] — пропущен, и функция по умолчанию решила, что наша таблица отсортирована по возрастанию номера артикула. В примере выше это оказалось правдой — потому мы получили значение, которое ожидали. Но дальше рассмотрим пример, где подобное бы не сработало.

Как устроена логика VLOOKUP: функция начнет поиск искомого значения с первой строки и будет продолжать его построчно. Она завершит поиск, как только найдет значение большее, чем мы запрашивали, — и вернет предыдущее. 

Например, нам нужно найти стоимость артикула 1104 (на скрине ниже он находится на 7-й строке). Функция начнет искать построчно, найдет артикул 1105 (то есть больший, чем искомый) и завершит поиск, вернув нам значение предыдущего артикула — 1103. 

То есть мы указали, что столбец отсортирован — значит, артикул 1104 не может находиться ниже, чем 1105. 

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

Если вы отметите, что 4-й параметр не обязательный, это ускорит работу. Когда таблица содержит тысячи строк, отсортированных по возрастанию, функции нет смысла проходить по всему набору данных. Ей достаточно остановиться на первом найденном искомом значении. Поэтому, если вы уверенны в корректной сортировке своих данных, то 4-й параметр можно упустить, в противном случае — укажите FALSE либо 0.

Сортировка по возрастанию относится не только к числовым, но и к текстовым выражениям — функция может делать поиск в алфавитном порядке. 

Кроме того, VLOOKUP допускает неточный поиск значения — вы можете опустить какой-то из символов, если он неизвестен. Для этого используйте символы подстановки: 

  • ? — заменяет один символ в строке 
  • * — заменяет любое количество символов

Например, нужно найти продукт, в последней букве которого мы не уверены. Для этого подставим знак «?» — «BA?» — и функция вернет нам стоимость продукта BAC:

Если же нужно найти именно вопросительный знак или звездочку — введите значок тильды (~) перед искомым символом.

Кроме того, функцию VLOOKUP можно использовать для объединения двух таблиц, у которых есть один общий ключ — в нашем случае это артикул.

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

На скриншоте выше артикул 1106 вернулся с ошибкой #N/A — это означает, что поиск по номеру не дал результата, поскольку артикула с таким номером нет во второй таблице. Чтобы не выводить в ячейки значение #N/A, можно доработать формулу функцией IFERROR:

Недостатки функции VLOOKUP

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

В примерах выше этот недостаток не проявлялся, поскольку искомое значение (артикул) было в первом столбце диапазона, а остальные значения находились справа от него. Но если столбец с искомыми значениями находится в середине таблицы, то вывести значение мы можем только из столбцов правее. Например:

В таком случае мы сможем вывести только стоимость наименования, поскольку этот столбец (С) находится правее от столбца с названием (B). Код (A) находится левее, поэтому функция VLOOKUP в подобном случае бессильна. 

Второй недостаток VLOOKUP заключается в том, что функция перестает работать, если убрать или добавить столбец в таблицу поиска. Вставленный или удаленный элемент изменит результат формулы, поскольку синтаксис функции требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Кроме того, функция VLOOKUP имеет ограничение по длине искомого значения в 255 символов, иначе будет возвращена ошибка #VALUE! (#ЗНАЧ!). 

Какие есть аналоги VLOOKUP

Чтобы обойти ограничение по поиску в первом столбце диапазона, в Excel появилась функция XLOOKUP. Она доступна только для пользователей Microsoft 365 и Excel 2021.  

Синтаксис функции выглядит так:

=XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

=XLOOKUP (искомое значение, просматриваемый массив, возвращаемый массив, [если не найдено], [тип сопоставления], [режим поиска]), где

  • искомое значение (что ищем?) — искомое значение или ссылка на ячейку, содержащую искомое значение
  • просматриваемый массив (где ищем?) — массив или диапазон, в котором вы ищете искомое значение
  • возвращаемый массив — массив или диапазон, из которого вы хотите получить значение
  • [если не найдено] (не обязательно) — возвращаемое значение, если совпадение не найдено
  • [тип сопоставления] (не обязательно) — как сопоставлять искомое значение со значениями в искомом массиве:
    • 0 (по умолчанию) = точное совпадение. Если совпадений не найдено, вернуть #N/A
    • -1 = точное совпадение. Если совпадений не найдено, вернуть следующее меньшее значение
    • 1 = точное совпадение. Если совпадений не найдено, вернуть следующее большее значение
    • 2 = частичное совпадение
  • [режим поиска] (не обязательно) — порядок поиска:
    • 1 (по умолчанию) = искать искомое значение от первого до последнего элемента в искомом массиве
    • -1 = искать искомое значение от последнего до первого элемента

Таким образом, чтобы получить значение (артикул), которое находится слева от столбца с искомыми значениями, можно использовать следующую функцию:

В Excel предыдущих версий эту задачу можно решить, используя комбинацию из функций INDEX+MATCH. Синтаксис выглядит так:

= INDEX (column to return a value from, MATCH (lookup value, column to look up against, [match_mode]) [column number])

= INDEX (возвращаемый массив, MATCH (искомое значение, просматриваемый массив, [тип сопоставления]), [номер столбца]), где 

  • возвращаемый массив — столбец, из которого нужно извлечь данные
  • искомое значение  (что ищем?)
  • просматриваемый массив  (где ищем?)
  • [тип сопоставления] (не обязательно) — для точного совпадения следует указать 0
  • [номер столбца] (не обязательно)

Функция вернет такие же значения, как и XLOOKUP:

Таким образом, связка функций INDEX и MATCH фактически полностью заменяет функцию VLOOKUP. Но в то же время у такой комбинации нет недостатков VLOOKUP, которые вытекают из синтаксиса самой функции, и этот вариант доступен для пользователей ранних версий Excel.

Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!

#MS Excel

#Программы

#Саморазвитие

#Гайд

#Продуктивность

Последние материалы

Статья

«Здесь и сейчас»: как управлять финансами бизнеса в кризис

5 инструментов для финансистов и предпринимателей.

Читать

Статья

6 способов повысить самомотивацию

Советы ученых из Стэнфорда и Нью-Йоркского университета.

Читать

Статья

«Google, закажи гречку»: 7 ритейл-трендов из США и Китая

Оплата ладонью, заказ голосом и распознавания лица для рекламы в магазине.

Читать

Читать онлайн «ВПР и Сводные таблицы Excel», Сергей Заидович Карамов – ЛитРес

Редактор Ольга Владимировна Карамова

Корректор Татьяна Сергеевна Карамова

© Сергей Заидович Карамов, 2021

ISBN 978-5-0055-7394-0

Создано в интеллектуальной издательской системе Ridero

Введение

Уважаемый читатель!

Представляю Вашему вниманию книгу «ВПР и Сводные таблицы Excel».

Публикация преследует несколько целей.

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

Во-вторых, дать знания тем, кто уверен в том, что ВПР и Сводные таблицы – это зеленый свет при приеме на работу.

В-третьих, получить обратную связь с читателями на материале этой книги для оценки того, стоит ли публиковать большую по объему книгу по Excel под рабочим названием «Базовый курс Excel».

Книга написана на основе индивидуальных занятий со взрослыми слушателями по обучению их уверенной работе в Excel.

В книгу включены главы, дающие общие понятия об управлении ячейками, о ссылках и формулах в Excel, для того чтобы подготовить читателей к работе с формулой ВПР.

Основное внимание уделено практическим вопросам, т. е. решению практических задач и ответам на вопросы, которые были заданы моими учениками.

Для усвоения материала учебника рекомендую не просто читать текст, а открыть Excel и сразу выполнять все описываемые операции.

Операции с ячейками листа Excel

Ячейки листа

На пересечении конкретных столбца и строки активного листа Excel расположена ячейка. Пример такого пересечения показан на Рис. 1.

Рис. 1. Ячейка листа как пересечение строки и столбца

Буквенная «нумерация» столбца и цифровая нумерация строки задают однозначное положение ячейки на листе – ее координаты. Например, выделенная ячейка на имеет координаты C5. Сначала идет буква – столбец, а затем без пробела цифра – номер строки. Все буквы латинские.

Координаты ячейки – очень важное понятие в Excel. Все операции в Excel проводятся с использованием координат ячеек путем формализованных указаний. Например, число из ячейки A3 сложить с числом из ячейки В3.

Что же такое ячейка и для чего она нужна? Ячейка – это главный элемент Excel.

В обыденном понимании ячейки – это элементы, из которых состоит другой, более крупный объект. Например, когда говорят банковская ячейка – мы понимаем, что это такое. В банковской ячейке клиенты хранят какие-то вещественные ценности. А в Excel в ячейке хранятся данные. Причем, эти данные могут различаться в зависимости от определенных признаков или свойств.

Чтобы что-то хранилось в банковской ячейке, нужно это что-то туда положить. Так же и в Excel.

Для того, чтобы в ячейку разместить (ввести) информацию или данные, сначала необходимо ячейку выделить. Для выделения ячейки достаточно навести на нее курсор и один (не два) раз нажать левую кнопку мыши. Затем с клавиатуры ввести информацию (например, записать фамилию – Иванов) и нажать кнопку Ввод (Enter). Готово. Информация введена и будет храниться в ячейке до тех пор, пока пользователю это необходимо.

Последовательность этих действий надо запомнить и постоянно первое время контролировать себя для правильного ввода данных: выделили ячейку, с клавиатуры ввели данные, нажали Enter. Ни в коем случае нельзя заканчивать

ввод данных путем щелчка на другую ячейку. В дальнейшем такая привычка будет приводить к постоянным ошибкам при работе с формулами или другими операциями с данными.

Чтобы удалить информацию из ячейки, нужно выделить ячейку и нажать клавишу Delete. Для удаления данных можно использовать кнопку панели инструментов «Очистить содержимое».

Если необходимо удалить информацию из группы ячеек (например, расположенных в одном столбце), можно выделить эту группу ячеек и нажать Delete, а можно выделить весь столбец и нажать Delete. Конечно, Вы должны понимать, что, выделив весь столбец, Вы удалите данные из всех ячеек столбца.

Кроме конкретной информации, хранящейся в ячейке, сама ячейка имеет ряд свойств или определенных характеристик, присвоенных ей по умолчанию или установленных пользователем.

Например, размер ячейки, размер шрифта текста, расположение текста в ячейке, цвет шрифта, количество десятичных знаков числа и т. д. Все эти свойства называются форматами ячейки. Операция по изменению форматов ячейки называется форматированием.

Кроме хранения данных, в ячейке могут совершаться операции над данными из других ячеек.

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

Операции с ячейками листа

Если выделить конкретную ячейку, например С5 и вызвать контекстное меню (правой кнопкой мыши), появится список операций, которые можно проводить с выделенной ячейкой. Контекстное меню выделенной ячейки показано на Рис. 2.

Рис. 2. Контекстное меню выделенной ячейки со списком операций

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

Для этого нужно выделить ячейку C5, вызвать контекстное меню правой кнопкой мыши, выбрать из списка позицию «Копировать», затем выделить ячейку F5, снова вызвать контекстное меню и нажать… Возникает вопрос, как правильно вставить данные? Что надо нажать?

В связи с тем, что ячейка может иметь различные форматы, т. е. кроме размещенных в ней данных иметь и другие свойства, в Excel предусмотрены разные варианты вставки скопированных данных.

Итак, копирование – это однотипная операция независимо от того, что нужно будет затем вставить. А вот вставка – это та операция, которая определяет, что из скопированного нужно вставить: вставить все (форматы и данные), вставить только данные, вставить только форматы или только формулы, или только значения без формул и т. д.

Выбор варианта вставки производится в контекстном меню в разделе «Параметры вставки». На Рис. 3 хорошо видно, что в контекстном меню возможен выбор из шести вариантов вставки.

Рис. 3. Параметры вставки данных

Пока нас будет интересовать самая первая слева позиция. Если навести на эту позицию курсор, появится название «Вставить». В дальнейшем разберемся со всеми другими вариантами вставки.

На Рис. 4 показан результат нашей операции по копированию – вставке данных.

Рис. 4. Копирование и вставка информации

Шесть позиций вставки, показанных на – это далеко не все возможные варианты вставки данных в Excel. Чтобы получить представления о том, какие возможны варианты вставки скопированных данных, нужно выбрать в контекстном меню позицию «Специальная вставка». Вид окна «Специальная вставка» показан на Рис. 5.

Рис. 5. Вид окна управления вставкой данных

Кроме различных вариантов вставки данных, во вкладке «Специальная Вставка» существует возможность одновременно со вставкой числовых данных проводить арифметические операции с данными: сложение, вычитание, умножение и деление.

В Excel практически для всех операций существует альтернатива. Описанный выше способ копирования-вставки не единственный и иногда не самый эффективный в практической работе с Excel. Очень часто пользуются методом копирования перетягиванием.

Если навести курсор на нижний правый угол выделенной ячейки, то курсор изменит свой вид с белого крестика на маленький черный крестик. На Рис. 6 это позиция №2.

Рис. 6. Виды курсора

Нажав левую клавишу мыши и удерживая ее, можно потянуть ячейку как за хвост, например вниз, или вправо в зависимости от того, куда Вы хотите скопировать данные. Где Вы остановитесь и отпустите клавишу мыши, там закончится копирование. И во все ячейки, через которые Вы тянули первоначальную ячейку, скопируются данные этой ячейки.

Для примера протянем ячейку С5 с фамилией Иванов вниз на 5 строк. Результат наших действий иллюстрирует Рис. 7.

Рис. 7. Копирование ячейки методом перетягивания

Копировать можно как одну ячейку, так и группу выделенных ячеек.

Чтобы скопировать группу ячеек, надо сначала их выделить. Для выделения одной ячейки мы просто наводили на нее курсор и щелкали левой кнопкой мышки. Для выделения группы ячеек нужно также навести курсор на первую ячейку (ближе к середине, чтобы курсор был в виде белого крестика), нажать левую кнопку мыши и не отпуская кнопки (как и в случае копирования перетягиванием) протянуть курсор до последней ячейки из группы, которую Вы хотите выделить. Вид выделенных ячеек показан на Рис. 8.

Рис. 8. Выделение группы ячеек

Выделенные ячейки должны стать серого цвета (за исключением первой). Внизу справа от всей выделенной группы ячеек появится утолщение (как и при выделении одной ячейки). Далее можно работать с этой группой ячеек как с одной ячейкой. Давайте протянем группу выделенных ячеек вправо на три столбца. Должен получиться результат, показанный на Рис. 9.

 

Рис. 9. Результат копирования перетягиванием группы ячеек

Овладев операцией копирования перетягиванием, можно перейти к рассмотрению некоторых интересных возможностей Excel. Выполните на листе Excel следующие действия:

– введите в любую ячейку дату 01.01.2022 и протяните ее на десять строк вниз;

– введите в соседнем столбце в ячейку слово Январь и протяните на 10 строк вниз;

– введите в ячейку слово понедельник и протяните на 10 строк вниз.

При перетягивании некоторых данных (обратите внимание – только некоторых, а не любых) Excel сам изменяет данные, что существенно облегчает практическую работу. В результате должна получиться таблица, как на Рис.10.

Рис. 10. Примеры работы режима автозаполнения

С помощью перетягивания можно создавать автонумерацию. Единственным отличием от вышеуказанных примеров является то, что для автонумерации необходимо ввести в соответствующие ячейки 1 и 2, затем выделить их как группу ячеек, и эту группу протянуть вниз.

Все эти примеры показывают возможности режима работы Excel, который называется автозаполнение. Возможности автозаполнения достаточно широки. Давайте еще раз введем в верхнюю ячейку столбца E дату и скопирует перетягиванием вниз. Справа внизу нового столбца появится информационный квадратик, который называется «Параметры автозаполнения», показанный на Рис.11. Это кнопка раскрывающегося списка.

Рис. 11. Кнопка выбора параметров автозаполнения

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

С другой стороны, бывают ситуации, когда необходимо заполнить ячейки одним и тем же значением. Такой режим тоже возможен при выборе в параметрах автозаполнения позиции «Копировать ячейки».

Разобравшись с автозаполнением, давайте рассмотрим еще одно практическое действие или операцию. Если навести курсор на границу выделенной ячейки (границу группы выделенных ячеек), вид курсора изменится и примет вид, показанный на под цифрой 3 (тонкий крестик со стрелками). В этом случае операция перетягивания станет равносильна операции вырезать-вставить. Это операция на практике применяется не так часто, как операция копирования перетягиванием, но знать ее надо. Иногда случайно можно выполнить эту операцию вместо другой операции.

С помощью операции вырезания перетягиванием можно менять местоположение таблицы, менять местами столбцы таблицы, строки таблицы (в том числе и строки сводной таблицы) и т. д.

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

Горячие клавиши

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

Такие комбинации клавиш называются горячими клавишами. Комбинаций очень много – несколько сотен. Комбинации могут состоять из двух, трех и более клавиш, которые надо нажать одновременно. Запомнить все комбинации горячих клавиш трудно, да и необходимости такой, как правило, нет.

В каких случаях необходимо хорошо знать комбинации горячих клавиш и практически их постоянно использовать? Только тогда, когда пользователю Excel на своем рабочем месте приходится выполнять техническую работу, когда каждый переход от мышки к клавиатуре – это потеря времени. Использование горячих клавиш позволяет экономить время выполнения однотипных ежедневных операций.

Что лучше: работа мышкой или с помощью горячих клавиш? Это решает для себя каждый пользователь в зависимости от вида работ, выполняемых в Excel. Нужно найти золотую середину, и освоить для начала только те горячие клавиши, которые действительно будут помогать в работе и экономить время.

Актуальных комбинаций горячих клавиш, действительно полезных и нужных в практической работе, не так уж и много. Порядка 30—40. И запомнить их не сложно.

Комбинации клавиш обозначают следующим образом. Например, Ctrl +Enter значит, что для выполнения определенной операции необходимо одновременно нажать две клавиши на клавиатуре – Ctrl и Enter. Сначала одну, потом удерживая ее, нажать вторую. Не надо пытаться нажать их одновременно.

Так вот, операции копирования и вставки можно выполнить с помощью горячих клавиш Ctrl+C (копировать), Ctrl+V (вставить). Если нужна специальная вставка – Ctrl+Alt+V. Выбирать нужные опции специальной вставки можно с помощью стрелок на клавиатуре и кнопки Таб. Закончив выбор, нужно нажать Enter.

Двигаться от одной ячейки до другой можно с помощью стрелок. Стрелки можно тоже считать горячими клавишами.

Выделение группы ячеек производится с помощью комбинации клавиш Shift +Стрелка. Чтобы выделить группу ячеек надо с помощью стрелок встать на первую ячейку, нажать Shift и удерживая эту кнопку с помощью стрелок выделить весь нужный диапазон. Попробуйте, потренируйтесь.

Еще одна полезная клавиша. F1. Это вызов справки Excel.

Пока горячих клавиш достаточно.

Придумайте себе задание, в котором надо из заранее подготовленного материала сделать что-то другое, задействуя перечисленные выше горячие клавиши. Каждый день начинайте с выполнения этого задания. Совсем скоро Вы заметите, что делать задание стало легко, а все горячие клавиши руки нажимают автоматически.

Пример такого задания показан на Рис.12. Необходимо информацию из таблицы 1 сгруппировать в виде таблицы 2 без помощи мышки, используя горячие клавиши, рассмотренные выше. Если задачу сформулировать проще, необходимо из таблицы 1 сделать таблицу 2 с помощью горячих клавиш.

Рис. 12. Задание на тренировку работы с горячими клавишами

Если все получилось, значит несколько полезных горячих клавиш уже изучены, а главное появилось понимание того, что такое горячие клавиши и для чего они нужны.

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

Как можно решить эту задачу?

1 Вариант. Копируем ячейку A2 и вставляем в ячейку G2. Копируем ячейку A3 и вставляем в ячейку G3 и т. д. пока все ячейки не будут скопированы в другое место. Это самый примитивный и долгий путь.

2 Вариант. Выделяем и копируем диапазон ячеек A2:А5. Вставляем в ячейку G2. Выделяем и копируем диапазон ячеек С2:С5. Вставляем в ячейку Н2. Выделяем и копируем диапазон ячеек E2:E5. Вставляем в ячейку G2.

3 Вариант. Выделяем и копируем всю таблицу 1, вставляем в ячейку G2. Выделяем и удаляем пустые столбцы.

4 Вариант. Выделяем всю таблицу, заходим в меню «Главная» -«Найти и выделить» – «Выделить группу ячеек». Устанавливаем галочки, как показано на Рис. 13 и нажимаем Enter. Затем копируем выделенные ячейки и вставляем на новое место. Задача решена.

Рис. 13. Выбор позиций в окне управления группой ячеек

Выше в тексте были приведены некоторые обозначения, на которые необходимо обратить внимание.

A2:А5 – это обозначение диапазона ячеек. Любой диапазон ячеек в Excel задается своими крайними значениями через двоеточие. В нашем случае это все ячейки от А2 до А5. Это столбец значений. Так можно определить любой диапазон ячеек: строку, столбец, таблицу. Например, данные нашей исходной таблицы 1 (см.) находятся в диапазоне A2:Е5. Таблица всегда однозначно определяется верхней левой и нижней правой ячейками.

Формула Excel LIKE, AND, IF, WILDCARDS

спросил

Изменено
1 год, 8 месяцев назад

Просмотрено
134 тыс. раз

У меня есть сценарий, в котором я использую несколько удобных для меня формул вместе и не получаю результата. Я хочу получить ЛЮБОЙ результат, где в ячейке присутствует «1». (1 является результатом формулы). А также там, где текст определенного столбца содержит &. («&/ИЛИ»)

Я пробовал пару формул

 =ЕСЛИ(И(I1=1,C2="*"&$Q$1&"*")),1," ")

— В этом я попытался поместить & в ячейку и сослаться на нее

 = ЕСЛИ (I1 = 1,1, " ")

и затем в новом столбце

 =ЕСЛИ(C2="*"&"/"&"*",1," ")

Затем объединить результаты двух? Кто-нибудь заметил, что с ним не так??

Подстановочные знаки не распознаются операторами сравнения, такими как = , например, если вы используете эту формулу

=A1="*&*"

, который будет рассматривать * как буквальные звездочки (не подстановочные знаки), поэтому будет возвращаться только TRUE , если A1 буквально содержит *&*

28 может использовать функцию

COUNTIF даже для одной ячейки, например.

=СЧЕТЕСЛИ(A1,"*&*")

Это вернет 1, если A1 содержит и , поэтому для ваших целей:

=ЕСЛИ(И(I1=1,СЧЁТЕСЛИ($G$1) ,"*&*")),1,"")

2

СЧЁТЕСЛИМН также можно использовать для проверки нескольких условий:

 =СЧЁТЕСЛИМН(A1,"слово1",A2,"слово2")

слово1, слово2 может содержать подстановочные знаки, если требуется

или что-то вроде

 =ЕСЛИ(И(I1=1,НЕ(ОШИБКА(НАЙТИ("&",$G$1)))),1," ")

любой вариант этого действительно…

0

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Функция Excel для выполнения SQL-подобных запросов к данным рабочего листа?

Задавать вопрос

спросил

Изменено
7 месяцев назад

Просмотрено
190 тысяч раз

У меня есть большая таблица на листе Excel:

 Column_1 | Столбец_2 | Столбец_3
ЗначениеA ЗначениеB ЗначениеC
. ...

Мне нужна функция, которая будет принимать в качестве входных данных диапазон и SQL-подобную строку запроса и возвращать диапазон строк, соответствующих запросу, например:

 =SQL_SELECT(A1:C1000, "SELECT * WHERE Column_1 = ValueH AND Column_3 = blah")

Существует ли нечто подобное? Или что было бы лучшим способом реализовать себя?

  • sql
  • excel
  • vba
  • функция

3

Вы можете использовать Получить внешние данные (несмотря на его название), расположенный на вкладке «Данные» в Excel 2010, чтобы настроить соединение в рабочей книге для запроса данных от самого себя. Используйте Из других источников Из Microsoft Query для подключения к Excel

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

Вот быстрый Sub для демонстрации доступа к объектам соединения

 Sub DemoConnection()
    Dim c As соединения
    Dim wb как рабочая книга
    Дим и пока
    Dim strSQL как строка
    
    Установите wb = ActiveWorkbook
    Установите c = wb.Connections
    Для i = 1 To c.Count
        ' Обновить данные
        c(i).Обновить
        ' просмотреть SQL-запрос
        strSQL = c(i).ODBCConnection.CommandText
        MsgBox стрSQL
    Следующий
Конец сабвуфера

7

Если вы можете сохранить книгу, у вас есть возможность использовать ADO и Jet/ACE для обработки книги как базы данных и выполнения SQL для листа.

Информацию MSDN о том, как попасть в Excel с помощью ADO, можно найти здесь.

3

Один из быстрых способов сделать это — создать столбец с формулой, которая дает значение true для интересующих вас строк, а затем отфильтровать значение TRUE в этом столбце.

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