3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функции поиска данных: разыскивается информация

Функции поиска данных: разыскивается информация!

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

Поиск в таблице Эксель, функции ВПР и ГПР

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

Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

  • Искомое значение – значение, которое будем искать. Это обязательный аргумент;
  • Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
  • Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
  • Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Эксель

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Эксель

Нестрогий поиск ВПР в Excel

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :

  • Искомое значение – данные или ссылка на данные, которые нужно искать;
  • Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
  • Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Функция "ПРОСМОТР" в Microsoft Excel

Функция «ПРОСМОТР» в Microsoft Excel

Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

  • Искомое значение – обязательный аргумент
  • Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
  • Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.
Читайте так же:
Sculptris Alpha 6

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]) :

  • Массив – аргумент указывает из какого массива ячеек нужно выбрать значение
  • Номер строки – указываете порядковый номер строки (начиная с первой ячейки массива), которую нужно вывести. Здесь можно записать значение вручную, либо использовать результат вычисления другой функции. Например, ПОИСКПОЗ.
  • Номер столбца – необязательный аргумент, указывается, если массив состоит из нескольких столбцов. Если аргумент упущен, формула использует первый столбец таблицы.

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Excel

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Поиск в программе Microsoft Excel

Поиск в Microsoft Excel

В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.

Поисковая функция в Excel

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

Способ 1: простой поиск

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

  1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.

Переход к поиску в Microsoft Excel

Обычный поиск в Microsoft Excel

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

Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».

Результат обычного поиска в Microsoft Excel

Найти всё в Microsoft Excel

Способ 2: поиск по указанному интервалу ячеек

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

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

Выделение интервала в Microsoft Excel

Поиск по интервалу в Microsoft Excel

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

Результат поиска в Microsoft Excel

Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

    После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».

Переход в параметры поиска в Microsoft Excel

Параметры поиска по умолчанию в Microsoft Excel

По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

Настройки поиска в Microsoft Excel

По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.

Область поиска в Microsoft Excel

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

Читайте так же:
Импорт контактов в Outlook

Содержимое поиска в Microsoft Excel

В графе «Область поиска» определяется, среди каких конкретно элементов производится поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке отображаются в строке формул. Это может быть слово, число или ссылка на ячейку. При этом, программа, выполняя поиск, видит только ссылку, а не результат. Об этом эффекте велась речь выше. Для того, чтобы производить поиск именно по результатам, по тем данным, которые отображаются в ячейке, а не в строке формул, нужно переставить переключатель из позиции «Формулы» в позицию «Значения». Кроме того, существует возможность поиска по примечаниям. В этом случае, переключатель переставляем в позицию «Примечания».

Область поиска в программе Microsoft Excel

Ещё более точно поиск можно задать, нажав на кнопку «Формат».

Переход к формату поиска в Microsoft Excel

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

Формат поиска в Microsoft Excel

Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

Переход к выбору ячейки для установки формата в Microsoft Excel

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

Выбор ячейки для установки формата в Microsoft Excel

После того, как формат поиска настроен, жмем на кнопку «OK».

Установка формата поиска в Microsoft Excel

Поиск по отдельным словам в Microsoft Excel

Запуск расширенного поиска в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12369 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

2. Рекомендации по работе с Таблицами и Excel

Работать с файлами Microsoft Excel можно разными способами. Вы можете:

  • Импортировать данные Excel в Таблицы. Подробнее…
  • Преобразовывать файлы Excel в формат Таблиц. Подробнее…
  • Редактировать файлы Excel на Диске, не изменяя их формат. Подробнее…
  • Преобразовывать файлы Таблиц в Excel или другие форматы. Подробнее…

2.2. Как использовать Excel вместе с Таблицами

Если ваша команда работает и с Microsoft Excel, и с Таблицами, ознакомьтесь с рекомендациями о том, в каких случаях лучше выбрать тот или иной продукт.

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

2.3. Как редактировать файлы Excel в Таблицах

Откройте файл Excel на Диске и отредактируйте его в Таблицах. Все внесенные изменения будут автоматически сохраняться в исходном формате Excel.

Только для браузера Chrome

  1. Убедитесь, что расширение «Редактирование файлов Office» не установлено:
    1. Откройте браузер Chrome и в правом верхнем углу нажмите «Ещё» Дополнительные инструментыРасширения.
    2. Если вы видите в списке расширение «Редактирование файлов Office», нажмите Удалить.

    Он откроется в окне предварительного просмотра.

    Теперь вы можете просматривать историю версий, редактировать файл, открывать к нему доступ и работать над ним вместе с другими пользователями в режиме реального времени. Все изменения сохраняются в исходном файле Microsoft Office.

    Теперь вы можете просматривать историю версий, редактировать файл, открывать к нему доступ и работать над ним вместе с другими пользователями в режиме реального времени. Все изменения сохраняются в исходном файле Microsoft Office.

    2.4. Как импортировать данные Excel в Таблицы

    Вы можете импортировать данные из файла Excel в новый или существующий файл Таблиц. При этом файл Excel не изменится, даже если вы отредактируете соответствующий документ в Таблицах.

    1. Создайте в Таблицах новый файл или откройте существующий.
    2. Нажмите ФайлИмпортировать.
    3. Найдите файл Excel и нажмите Выбрать.
    4. Выберите вариант импорта:
      • Создать таблицу
      • Вставить лист(ы)
      • Заменить таблицу

    2.5. Как преобразовать файл Excel в формат Таблиц

    Вы можете преобразовать файл Excel в формат Таблиц и продолжить работать над ним в этом сервисе, не импортируя данные. При этом файл Excel изменен не будет.

    1. На Диске дважды нажмите на файл Excel.

      Он откроется в окне предварительного просмотра.

    2. Вверху страницы нажмите Открыть в Google Таблицах.
    3. Нажмите Файл Сохранить как таблицу Google.

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

    1. Откройте Диск и в верхней части экрана выберите Настройки.
    2. В разделе «Загруженные файлы» установите флажок Преобразовывать загруженные файлы в формат Google.

    Примечание. Ранее загруженные файлы не будут преобразованы.

    Совет. Чтобы вам было проще различать файлы Таблиц и Excel, на Диске используются следующие значки:

    2.6. Как поделиться копией файла Таблиц в формате Excel

    Вы можете поработать над файлом в Таблицах, а затем отправить его копию по электронной почте в формате Excel или PDF.

    1. Откройте файл в Таблицах.
    2. Нажмите ФайлОтправить по электронной почте.
    3. В разделе Прикрепить как выберите Microsoft Excel.

    4. Введите адрес электронной почты, тему и сообщение.
    5. При необходимости установите флажок Отправить мне копию.
    6. Нажмите Отправить.

    Файл Таблиц также можно скачать в формате Excel, PDF, CSV, ODS и других.

    1. В Таблицах нажмите ФайлСкачать.
    2. Выберите формат файла.

    Google, Google Workspace, а также другие связанные знаки и логотипы являются товарными знаками компании Google LLC. Все другие названия компаний и продуктов являются товарными знаками соответствующих компаний.

    Выполняем поиск в Excel с помощью функции ВПР

    Элен Брэдли объясняет тонкости работы с функцией ВПР в Microsoft Excel при поиске данных в таблице.

    Когда необходимо найти информацию в таблице, функции поиска Excel помогут Вам справиться с этой задачей. В более ранних версиях Excel существовал Мастер подстановок, с помощью которого настроить поиск было достаточно просто, но в Excel 2010 его уже нет. Теперь, если Вам требуется формула поиска, придется создавать её вручную. В этой статье я покажу Вам, как обуздать мощь функций поиска в таблицах Excel.

    Основы

    В Microsoft Excel есть несколько функций поиска, среди них VLOOKUP (ВПР), очень похожая на нее функция HLOOKUP (ГПР) и LOOKUP (ПРОСМОТР). Функция ВПР используется для того, чтобы искать данные в таблице. Она выполняет поиск искомого значения по первому столбцу таблицы и возвращает соответствующее значение из другого столбца.

    Когда данные расположены по-другому, используйте ГПР, чтобы найти нужное значение в верхней строке таблицы и возвратить соответствующее значение из заданной строки, расположенной ниже. Функция ПРОСМОТР имеет две формы – векторную и массива, и может возвращать значение из одного столбца, одной строки или из массива (аналог ВПР и ГПР). Из этих трёх функций, вероятнее всего, Вы будете использовать ВПР гораздо чаще, чем остальные. Именно на ней я сфокусируюсь в этой статье. В целом, если Вы поймете и сможете применить функцию ВПР, то сможете справиться и с ГПР.

    Синтаксис функции ВПР

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

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

    У этой функции существует ещё один не обязательный аргумент, который позволяет искать приблизительное или точное совпадение искомого значения, причем первый режим используется по умолчанию. В случае если Вы устанавливаете режим поиска точного совпадения, т.е. последний аргумент равен FALSE (ЛОЖЬ), таблица может быть не отсортирована. Если же Вы устанавливаете режим поиска неточного совпадения, т.е. последний аргумент не указан или равен TRUE (ИСТИНА), то необходимо отсортировать таблицу в порядке возрастания, иначе функция может возвратить неправильный результат. При поиске неточного совпадения, Excel ищет значение равное искомому, а если его нет — использует ближайшее, которое меньше искомого.

    Итак, для примера, в этой таблице представлены значения веса в фунтах (Lbs weight), а также стоимость обработки и перевозки. Мы можем использовать функцию ВПР, чтобы найти значение веса и определить стоимость обработки (Handling) и перевозки (Shipping) партии товара такого веса. Конечно же, вес большинства партий товара не будет иметь такие же ровные значения, поэтому мы используем в качестве последнего аргумента TRUE (ИСТИНА), либо вовсе не указываем его. В таком случае наша формула найдёт результат, даже без точного совпадения. Не забываем сделать сортировку таблицы, чтобы данные в первом столбце располагались в порядке возрастания.

    ВПР в действии

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

    Чтобы для значения из ячейки B11, которое является весом партии товара в фунтах (в данном случае 1.5 фунта), возвратить стоимость обработки из столбца Handling, воспользуемся вот такой формулой:

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

    Поиск в Excel и функция ВПР

    Если Вы хотите скопировать формулу вниз по столбцу, не забудьте указать в ней абсолютные ссылки вот таким образом:

    Кроме этого, Вы можете присвоить своей таблице имя, для этого надо выделить ячейки от D1 до F7 и нажать Formulas (Формулы) > Define Name (Присвоить имя), затем ввести имя диапазона и нажать ОК. В нашем примере это имя shipping_and_handling.

    Поиск в Excel и функция ВПР

    Теперь при создании формулы Вы можете использовать имя диапазона. Вот здесь вместо адреса диапазона указано его имя:

    =VLOOKUP(B12,shipping_and_handling,2)
    =ВПР(B12; shipping_and_handling;2)

    Поиск в Excel и функция ВПР

    Мы можем адаптировать формулу из столбца Handling для расчёта значений в столбце Shipping. В данном случае поменяется только номер столбца. Для Shipping – это значение 3:

    Функция ГПР работает точно таким же образом. Точнее говоря, она также использует искомое значение и диапазон данных, но вместо номера столбца Вы задаете ей номер строки. Строки нумеруются 1, 2, 3 и так далее, где 1 – самая первая строка таблицы.

    Используя предыдущий пример, мы сможем найти соответствия для весов в 11, 12 или 25 фунтов, хоть они и отсутствуют в таблице. Возможность находить ближайшее значение, которое меньше искомого, выглядит очень привлекательной. Однако, существуют некоторые оговорки при использовании этой формулы. Одна из них – стартовое значение в таблице должно быть равно , что собственно и сделано у нас. Это позволяет исключить ошибки, когда используется вес, к примеру, меньше 1 фунта.

    Поиск в Excel и функция ВПР

    Работа с точными совпадениями

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

    Поиск в Excel и функция ВПР

    В данном примере мы ищем название в столбце A и возвращаем цену из столбца 2 или 3, в зависимости от указанного размера ковра – средний (M) или большой (L). В этой ситуации нам необходимо использовать функцию IF (ЕСЛИ), чтобы определить, какой номер столбца нужно использовать. Формула поиска будет выглядеть следующим образом:

    В данном случае мы ищем название ковра в столбце A и возвращаем цену из столбца B или C, в зависимости от выбранного размера ковра. Если точное совпадение не найдено, т.е. название ковра в заказе не соответствует ни одному из названий в столбце A, тогда будет возвращено сообщение об ошибке #N/A (#Н/Д). Функция IF (ЕСЛИ) составлена так, что если заданный размер ковра не будет соответствовать одному из двух имеющихся вариантов, то по умолчанию будет взят большой размер (L).

    Поиск в Excel и функция ВПР

    Используем проверку данных

    На практике желательно сделать так, чтобы пользователь гарантированно вводил правильное название ковра и его размер. Вы можете реализовать это, используя выпадающий список. Для этого выделите ячейки, в которые пользователь будет вводить свои заказы, например, столбец A или B. Перейдите Data > Data Validation > Data Validation (Данные > Проверка данных > Проверка данных). В появившемся диалоговом окне на вкладке Settings (Параметры) в поле Allow (Тип данных) выберите значение List (Список). Кликните в поле Source (Источник) и выделите ячейки от A2 до A4, в которых содержится список с названиями ковров. Нажмите ОК.

    Поиск в Excel и функция ВПР

    Таким же способом Вы можете создать выпадающий список для ввода размеров L или M, используя в качестве источника данных диапазон B1:C1.

    Теперь, когда пользователи будут выбирать ковер, они смогут указать нужные параметры из раскрывающихся списков. Это гарантирует, что название будет указано без ошибок, так как всегда будет выбрана одна из присутствующих в списке позиций. Кроме этого, если они передумают и решат выбрать другой ковер, то функция ВПР автоматически произведёт пересчет и вернет правильную стоимость.

    Сортируем данные

    Если Вы работаете с приблизительными совпадениями, то должны выполнить сортировку в таблице. Для этого выделите весь диапазон с данными, включая заголовки строк в первом столбце. Заголовки столбцов (шапку) можно не выделять. На вкладке Data (Данные) нажмите команду Sort (Сортировка), откроется одноименное диалоговое окно.

    В строке Sort By (Сортировать по) укажите параметры сортировки. В первом выпадающем списке выберите столбец, по которому необходимо выполнить сортировку, в нашем случае это первый столбец таблицы. Во втором выберите Values (Значения), а в третьем укажите порядок сортировки по возрастанию. Если вместе с данными, Вы выделили шапку таблицы, не забудьте поставить галочку My data has headers (Мои данные содержат заголовки). Нажмите ОК.

    Таблица с данными будет отсортирована так, что функция ВПР сможет работать с ней корректно.

    4 способа заменить точку на запятую в Excel

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

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

    Как в Excel заменить точку, на запятую?

    Выделите и скопируйте данные из ниже приведенной таблицы:

    № п/пВалютная параПересчетПродажаПокупкаСпрэд
    1EUR/USD11.13471.13500.0003
    2GBP/USD11.54381.54420.0004
    3USD/CHF10.95430.95470.0004
    4USD/JPY1001.19481.1945-0.0003

    Теперь перейдите на рабочий лист и щелкните правой кнопкой мышки по ячейке A1. Из появившегося контекстного меню выберите опцию «Специальная вставка». В диалоговом окне выберите «Текст в кодировке Unicode» и нажмите ОК.

    Текст в кодировке Unicode. Изменение шрифта по умолчанию.

    Как видно Excel распознает числа только в колонке C. Значения в этой колонке выровнены по правой стороне. В других колонках по левому краю. Во всех ячейках формат по умолчанию «Общий», а в ячейках D3, D5, E3, E5 вообще отображается формат «Дата». Мы копировали данные через специальную вставку и все форматы исходной таблицы устранены. Причина только одна – вместо запятой стоит точка. Такой тип данных не подготовлен и его нельзя использовать для вычислений.

    Примечание. Если копировать данные из других источников без специальной вставки, то вместе с данными копируется и формат. В таком случаи формат ячеек «Общий» (по умолчанию) может быть изменен. Тогда нельзя визуально отличить, где распознано число, а где текст.

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

    Чтобы заменить точку, на запятую в импортированных данных можно использовать 4 способа:

    Способ 1 замена точки на запятую в Excel через Блокнот

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

    1. Скопируйте данные из исходной таблички на этой странице. Откройте программу Блокнот Windows («Пуск»-«Все программы»-«Стандартные»-«Блокнот») и вставьте в него скопированные данные для подготовки. Вставка в Блокнот.
    2. Выберите в меню «Правка» опцию «Заменить» (или комбинацию горячих клавиш CTRL+H). В появившимся диалоговом окне, введите в поле: «Что» точку (.), а в поле «Чем» запятую (,). И нажмите кнопку «Заменить все».

    Программа Блокнот заменила все точки, на запятые. Теперь данные готовы для копирования и вставки на лист.

    Готово.

    Это весьма простой, но очень эффективный способ.

    Способ 2 временно меняем настройки Excel

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

    Для этого нужно открыть «Файл»-«Параметры»-«Дополнительно». В разделе «Параметры правки» следует временно убрать галочку «Использовать системные разделители». А в поле «Разделитель целой и дробной части» следует удалить запятую и ввести точку.

    Разделитель целой и дробной части.

    После выполнения вычислений настоятельно рекомендуется вернуть настройки по умолчанию.

    Внимание! Данный способ сработает, если сделать все изменения до импорта данных, а не после.

    Способ 3 временно меняем системные настройки Windows

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

    Открываем «Пуск»-«Панель управления»-«Языки и региональные стандарты». Нажимаем на кнопку «Дополнительно». В появившимся окне изменяем в первом поле «Разделитель целой и дробной части» — вводим нужное нам значение. Дальше ОК и ОК.

    Настройка формата в Windows.

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

    Способ 4 используем функцию найти и заменить в Excel.

    Данный способ похож на первый. Только здесь мы используем ту же функцию из Блокнота, но уже в самом Excel.

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

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

    1. Предварительно выделите столбцы где будут находится дробные числа с точкой в качестве разделителя. В данном случаи это 3 столбца D:F.
    2. Установите для выделенного диапазона текстовый формат ячеек, чтобы заранее избежать автоматического преобразования в формат даты некоторых чисел. Для этого выберите текстовый формат из выпадающего списка на закладке «Главная» в разделе «Число». Или нажмите CTRL+1 , в появившимся окне «Формат ячеек» выберите закладку «Число», а в разделе «Числовые форматы» укажите «Текстовый». Задать текстовый формат ячеек.
    3. Скопируйте таблицу и щелкните правой кнопкой мышки по ячейке A1. Из контекстного меню выберите опцию «Специальная вставка». Выберите «Текст в кодировке Unicode» и нажмите ОК. Обратите внимание, как теперь отображаются значения в ячейках: D3, D5, E3, E5, в отличии от самого первого копирования таблицы. Вставка таблицы.
    4. Нажмите на инструмент «Главная»-«Найти и выделить»-«Заменить» (или нажмите комбинацию CTRL+H). Найти и заменить в Excel.
    5. В появившемся окне введите в поле «Найти» — точку, а в во второе поле введите запятую. И нажмите «Заменить все».
    6. Снова выделите 3 столбца D:F и измените формат ячеек на «Числовой» CTRL+SHIFT+1. Не забудьте увеличить разрядность до 4-х, как описано в пункте №2.

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

    Вместо 4-го и 5-го пункта можно использовать в отдельной колонке формулу с функциями:

    Например, выделите диапазон ячеек G2:I5, введите эту формулу и нажмите CTRL+Enter. А потом переместите значения ячеек диапазона G2:I5 в диапазон D2:F5.

    Эта формула находит в тексте точку с помощью функции НАЙТИ. Потом вторая функция меняет ее на запятую. А функция ЗНАЧЕН преобразует полученный результат в число.

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector