Auditportal.ru

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

Как искать / находить и заменять значения в выделенных или выбранных ячейках в Excel?

Как использовать функцию LOOKUP в Excel для поиска информации

Функция LOOKUP в Excel имеет две формы: векторную и форму массива.

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

Отличие заключается вот в чем:

  1. С помощью VLOOKUP и HLOOKUP вы можете выбрать, из какого столбца или строки возвращать значение данных, в то время как LOOKUP всегда возвращает значение из последней строки или столбца в массиве.
  2. При попытке найти совпадение для указанного значения – известного как Lookup_value – VLOOKUP выполняет поиск только в первом столбце данных, а HLOOKUP – только в первой строке, а функция LOOKUP – в первой строке или в столбце. в зависимости от формы массива.

Функция LOOKUP и форма массива

Форма массива – будь то квадрат (равное количество столбцов и строк) или прямоугольник (неравное количество столбцов и строк) – влияет на то, где функция LOOKUP ищет данные:

  • Если массив квадратный или представляет собой высокий прямоугольник (больше ширины), LOOKUP предполагает, что данные находятся в столбцах, и поэтому ищет соответствие Lookup_value в первом столбце массива.
  • Если массив представляет собой широкий прямоугольник (шире, чем его высота), LOOKUP предполагает, что данные расположены в строках, и поэтому ищет соответствие Lookup_value в первой строке массива.

Синтаксис и аргументы функции LOOKUP – форма массива

Синтаксис формы массива функции LOOKUP

Lookup_value (обязательно) – значение, которое функция ищет в массиве. Значение Lookup_value может быть числом, текстом, логическим значением или ссылкой на имя или ячейку, которая ссылается на значение.

Массив (обязательно) – ячейки диапазона, в которых функция ищет значение Lookup_value. Данные могут быть текстовыми, числовыми или логическими значениями.

Для LOOKUP необходимо использовать отсортированный аргумент массива в порядке возрастания (как от A до Z или от наименьшего к наибольшему для чисел).

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

Если значение Lookup_value отсутствует или меньше всех значений в массиве, функция LOOKUP вернет ошибку # N/A

Пример использования массива формы функции LOOKUP

На основании рисунка выше, в этом примере будет использована форма массива функции LOOKUP для поиска цены Whachamacallit в инвентарном списке.

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

Сортировка данных

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

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

  1. Выберите ячейки A4 — C10 на листе
  2. Перейдите на вкладку Данные в меню ленты.
  3. Нажмите кнопку Сортировка в центре ленты, чтобы открыть диалоговое окно Сортировка
  4. Под заголовком Колонка диалогового окна выберите в раскрывающемся списке опцию Сортировать по деталям.
  5. При необходимости под заголовком Сортировать по выберите в раскрывающемся списке Значения.
  6. При необходимости под заголовком «Заказ» выберите из выпадающего списка пункт «От А до Я».
  7. Нажмите OK, чтобы отсортировать данные и закрыть диалоговое окно.
  8. Теперь порядок данных должен быть таким, как показано на рисунке выше.
Читайте так же:
Как изменить отрицательные числа на положительные в Excel?

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

Хотя можно ввести функцию LOOKUP

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

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

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

Как найти, посчитать и убрать повторяющиеся значения в Эксель

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

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

Как найти и выделить

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

Повторяющиеся значения в Excel

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

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

Теперь в Excel можно сортировать по цвету ячеек и цвету текста и удалять найденные дубликаты.

Как удалить

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

Затем установите флажок «Мои данные содержат заголовки». Если вы выбрали таблицу с заголовками. Выберите столбцы, в которых вы хотите найти повторения, а затем нажмите «ОК».

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

Чтобы удалить дубликаты, используйте фильтр. Выберите нужные столбцы вместе с заголовком. Кнопку «Сортировка и фильтр» в группе «Данные» можно найти, нажав на кнопку «Дополнительно».

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

В новой таблице не будет дублирующих строк.

Если у вас большая исходная таблица, вы можете создать аналогичную таблицу с отдельными записями на другом листе Excel. Для получения дополнительной информации читайте статью: Фильтрация в Excel.

Как посчитать

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

Читайте так же:
Как найти и перечислить все ссылки (внешние ссылки) в Excel?

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

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

В следующем окне ячейки диапазона уже указаны, отметьте «На новый лист» и нажмите кнопку «OK».

С правой стороны перетащите первые три поля в область «Row Names». Перетащите поле «Код» в область «Значения».

В результате получится перекрестная таблица, в которой не будет дубликатов, а в поле «Код» будут числа, представляющие повторяющиеся значения в исходной таблице — как часто встречалась эта строка.

Для удобства отсортируем все значения в столбце «Сумма по полю Код» по убыванию.

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

Выпадающий список уникальных значений. Автоматическое обновление выпадающего списка

Зависимый выпадающий список в Excel и Google таблицах

20 июля 2021 г. 5996

Введение

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

В качестве примера рассмотрим особенности создания выпадающих списков:

Исходные данные:

  • Перечень адресов в различных городах.

Вызов:

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

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

Рабочие файлы по ссылке ниже

Смотрите ниже обзорное видео о работе с выпадающими списками в Excel и Google Spreadsheets. Приятного просмотра!

Как сделать выпадающий список в Excel?

Давайте начнем с основ. Чтобы создать выпадающий список, вам понадобится список данных и инструмент проверки данных.

Выберите ячейку, в которой нужно создать выпадающий список. Затем перейдите к инструменту «Проверка данных», тип данных — «Список». В поле «Источник» укажите диапазон списка.

Выпадающий список готов к настройке.

Выпадающие списки можно использовать для отображения обычного диапазона. (Названия городов повторяются в диапазоне A2:A16, а выпадающий список имеет те же названия, что и список). Это, конечно, не очень удобно. О том, как сделать выпадающий список с уникальными значениями, мы поговорим позже. Давайте остановимся на этом.

Как создать зависимый выпадающий список в Excel?

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

Именованный диапазон в Excel — это ячейка (или диапазон ячеек), которой присвоено имя.

При использовании функции DWSSYL Excel преобразует текст в ссылку.

Способ 1: именованные диапазоны + функция ДВССЫЛ

Создайте именованные диапазоны и сначала дайте им адреса. Дайте каждому из них свое название города.

Читайте так же:
Как найти и найти именованные диапазоны в Excel?

Алгоритм создания именованного диапазона: выберите диапазон, затем «Формулы» — «Установить имя».

У нас названы 5 регионов: Волгоград, Воронеж, Краснодар, Москва и Ростов-на-Дону.

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

Поэтому вместо дефиса перед названием Ростова-на-Дону мы используем знак подчеркивания.

Диапазоны имен уже готовы.

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

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

Теперь можно выбрать иждивенцев из раскрывающегося списка.

Измените списки в ячейке E2, изменив значения в ячейке D2. За исключением города Ростов-на-Дону. В выпадающем меню для городов (ячейка D2) используются дефисы, а в диапазоне — подчеркивания.

Для устранения этого несоответствия значения можно обработать с помощью функции SUBSTITUTE перед использованием функции DVSYLL.

Функция SUBSTITUTE заменяет указанный текст в текстовой строке новым значением. Вместо: =DWSYL(D2), укажите: =DWSYL(SUBSTITUTE(D2;»-«;»_»))

Мы предварительно обрабатываем значения, чтобы они соответствовали правилам написания имен. В названии города не будет пробелов, если есть дефисы.

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

Как автоматически обновить выпадающий список в Excel, при добавлении новых данных?

Начнем с создания умной таблицы Excel из диапазона данных. Это можно сделать, нажав Ctrl+T .

Одной из полезных особенностей умного стола является диапазон растягивания. Это означает, что если мы добавим новые строки, они будут автоматически добавлены в выпадающий список. Например, добавьте новый город — Санкт-Петербург. И вот он уже появился в нашем первом выпадающем списке.

Как сделать выпадающий список уникальных значений в Excel?

Видеть одни и те же названия снова и снова — это неприятно. Давайте реализуем выпадающий список так, чтобы названия городов в нем не повторялись. Этого можно добиться, добавив слева вспомогательный столбец. Мы назвали его «Уникальный».

И включите новый столбец в диапазон умной таблицы. «Дизайнер» — «Размер таблицы». Вместо =$B$1:$C$17 укажите: =$A$1:$C$17

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

Наша формула массива для ячейки А2 создаст список городов:

Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter .

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

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

«Данные проверки» — «Список». В источнике данных вместо приведенного выше диапазона с названиями городов =$B$2:$B$18 задайте название — =Unique.

Читайте так же:
Как использовать проверку данных, чтобы в столбце Excel не оставались пустые ячейки?

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

Чтобы удалить их, уточним именованный диапазон «Уникальный». В менеджере имен вместо диапазона =Table1[Unique] используем: =MEMBERSHIP(Sheet1!$A$2;0;0;0;ACCOUNT(Table1[Unique])-CALCULATEPUTS(Table1[Unique]))

Где Sheet1!$A$2 — ячейка со значением первого элемента в списке уникальных значений.

Table1[Unique] — колонка со списком всех элементов списка

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

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

Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

Из D VSSYL можно удалить именованные диапазоны, которые мы использовали с ним до сих пор; в будущем они нам не понадобятся. Создание автоматически обновляющегося выпадающего списка может быть приемлемым вариантом.

Замените =DVSSSIL(E2;»-«;»_») в ячейке F2 (зависимый раскрывающийся список адресов) на: =MESSAGE($B$2;SEARCH(E2;$B$2:$B$18;0)-1;1;READ($B$2;$B$18;E2);1)

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

Аргументы функции:

Ссылка — мы берем первую ячейку в нашем списке, то есть $B$2.

Смещение строки вычисляется функцией ПОИСК, которая выдает порядковый номер ячейки с выбранным городом (E2) в указанном диапазоне (от $B$2 до $B$18).

Смещение колонки = 1, поскольку мы хотим обращаться к адресам в соседней колонке (C)

Высота над уровнем моря — рассчитывается функцией CALCULATION , которая подсчитывает количество вхождений в диапазон ( $B$2:$B$18 ) нужных значений — названий городов (E2)

Ширина = 1, так как нам нужен один столбец с адресами

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

Выпадающие элементы в таблицах Google работают несколько иначе.

Как сделать выпадающий список в Google таблицах?

Валидация данных, еще один инструмент в Google Spreadsheets для создания выпадающих списков, очень похож.

Выберите ячейку, в которую мы поместим раскрывающийся список.

Данные» — «Настройка проверки данных» — «Значение интервала» — «Данные» — «Настройки проверки данных» — «Значение интервала».

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

Как создать зависимый выпадающий список в Google таблицах?

Теперь давайте вернемся к двум основным способам, которые мы видели в Excel.

Способ 1: именованные диапазоны + ДВССЫЛ

Настройте именованный диапазон с адресами. Назовите каждый из них в соответствии с городом.

Выделить ячейки — «Данные» — «Настроить именованные диапазоны»

Введите имя и нажмите «Готово». У нас есть 5 названных рангов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Как и в Excel, в Google Таблицах есть список требований к названиям диапазонов.

Поэтому вместо дефисов в названии города Ростов-на-Дону укажите допустимый символ — подчеркивание.

Читайте так же:
Как изменить ось глубины 3D-диаграммы в Excel?

Как и в Excel, мы не сможем настроить функцию ДИКЦИОНАР в инструменте проверки данных Google электронных таблиц. Результат функции DIVSYL будет помещен в пустые ячейки справа. DELETE должна обрабатывать дефис. Мы обсуждали, почему это необходимо, ранее в примере Excel.

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»_»;»-«))

Последним штрихом для создания зависимого выпадающего списка является выбор диапазона из столбца F:F в разделе «Настроить проверку данных. «.

Его можно спрятать для дальнейшей работы. Недостатком этого метода является отсутствие динамичности. Дополнения к городу и адресу не будут появляться в выпадающих списках. Но эту проблему можно решить!

Как автоматически обновить выпадающий список в Google таблицах при добавлении новых данных?

В выпадающем списке городов просто расширьте диапазон и вместо =$A$2:$A$16 укажите: =$A$2:$A . При добавлении нового города он теперь будет автоматически появляться в выпадающем списке.

Как автоматически обновить зависимый выпадающий список в Google таблицах при добавлении новых данных?

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

Я чувствовал G6 angiver vi:

Этот метод работает только тогда, когда данные в столбце «Город» отсортированы от A до Z или от Z до A. Более подробно о том, как работает функция СООБЩЕНИЕ в этом случае, см. пример Excel выше.

На последнем этапе результаты функции HUMMING помещаются в диапазон выпадающего списка.

Для удобства вспомогательные колонки можно спрятать.

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

Заключение

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

Вы можете научиться работать с Excel на наших онлайн-курсах Excel: онлайн-курсы Excel

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

Полезные текстовые функции в Excel и Google таблицах

2 ноября 2021 г. 442 MS Excel Google Sheets Video File

Как собрать телефонные номера в общий формат? Как сократить длинные ссылки? Как вы извлекаете домены? Ответы на эти и другие подобные вопросы можно найти в этой статье.

Поиск объединённых ячеек в Excel и Google таблицах

2 марта, 2021 2592 MS Excel Google Sheets Видео

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

Тип и формат данных в Excel и Google таблицах

21 июня 2021 1122 MS Excel Электронная таблица Видеофайл Google

В этой статье мы расскажем вам о стандартных вариантах форматирования ячеек и научим вас создавать собственный формат отображения данных.

ABC-XYZ анализ. Как определить самые ходовые товары

24 октября 2021 1079 Инфостатистика Аналитика MS Excel

Анализ матрицы ABC-XYZ в Excel. Узнайте, зачем нужно проводить анализ ABC-XYZ и как интерпретировать его результаты

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