Auditportal.ru

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

Как исключить значения в одном списке из другого в Excel?

Как в Экселе исправить ошибку Н/Д

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

Часто функции поиска возвращают ошибку #N/D. Это означает, что программа не смогла найти нужное значение. Это неприятно, потому что все остальные расчеты становятся невозможными. Многих пугает эта ошибка, потому что они мало что о ней знают. Но это не так.

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

Отсутствие значения в исходной таблице маловероятно или исключено

Если значение должно присутствовать, а его нет, необходимо проверить его:

  1. Если формула написана правильно и нет недостающих аргументов;
  2. Если вы правильно используете функцию. Прочитайте, как он работает, и проверьте, как вы его используете. Список полезных статей о функциях поиска я приложу в конце этого материала;
  3. Оцените таблицу с исходными данными, если это возможно, визуально оцените ее полноту, сверьтесь с первоисточником, если он есть. Например, сравните количество строк;
  4. Проверьте, правильно ли записано искомое значение. Эти данные часто содержат лишние пробелы и непечатаемые символы. Их трудно заметить сразу, но они могут быть причиной ошибки #N/D;
  5. Аналогично проверьте, правильно ли записаны искомые значения в таблице данных. Существует высокая вероятность появления непечатаемых символов при загрузке таблиц из базы данных;
  6. Диапазон поиска, указанный в функции, не охватывает всю таблицу, некоторые строки или столбцы находятся «за его пределами». Это может быть результатом механической ошибки или неправильного использования относительных ссылок. В целом, относительные и абсолютные ссылки очень полезны, но они часто являются источником ошибок для новичков. Узнайте о них, если хотите хорошо работать в Excel, это простой и важный предмет. Вот статья, которая поможет вам;
  7. Значение, которое вы ищете, и значение, которое вы проверяете в таблице, имеют одинаковый тип. Часто при сохранении таблиц в базе данных числа сохраняются в виде текста. Сопоставление данных разных типов приводит к #N/D;
  8. Используете ли вы правильный режим поиска в функциях? Необоснованный выбор режимов может привести к ошибкам или вернуть неверные результаты. В последнем случае вы можете даже не понять, что что-то пошло не так. Для получения дополнительной информации о режимах поиска см. статьи, перечисленные ниже;
  9. Рассмотрите другие возможные причины. Если возможно, обратитесь за помощью к более опытным пользователям.
Читайте так же:
Как извлечь изображения из комментариев в Excel?

Отсутствие значения в исходной таблице возможно

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

Обход #Н/Д с помощью функции ЕСЛИОШИБКА

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

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

В приведенном ниже примере мы ищем человека в списке и выводим его возраст. Если имя не найдено, отображается ошибка #N/D:

Вы можете использовать IF ERROR, чтобы вместо ошибки вывести «NOT found»:

Вам не кажется, что это выглядит лучше, чем первое?

Перехват функцией ЕСНД

Предыдущий способ хорош, но имеет недостаток: он обрабатывает ошибки всех типов. Значит, мы можем случайно скрыть проблемы, которые должны были обработать вручную, разобраться. Получаем непредсказуемый результат. Потому, лучше использовать функцию ЕСНД. Она работает так же, как и предыдущая, но «отлавливает» лишь одну ошибку — #Н/Д:

Не выводить ничего в случае ошибки

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

И последнее: ND() — это именно та функция, которую следует использовать, если по каким-то причинам вам нужно принудительно вернуть #H/D. Что касается того, почему это может быть полезно, я не использовал ее раньше. Есть ли какой-то способ использовать эту функцию? Дайте мне знать в комментариях! Если что-то из этого материала вызвало у вас затруднения — пишите, я отвечу!

Читайте так же:
Как изменить значение ячейки, нажав на ячейку?

Как исключить значения в одном списке из другого в Excel?

На этом шаге мы увидим автоматическую фильтрацию списка.

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

  • Автофильтр используется для фильтрации по простым критериям.
  • Расширенный фильтр применяется для фильтрации по более сложным критериям.

Помещение табличного курсора на ячейку списка автоматически отфильтрует ее. Затем выполните команду Данные | Фильтр | Автофильтр . На рис.1 показано, как Excel анализирует список и добавляет кнопки раскрывающегося списка (кнопки автофильтра) в строку заголовка поля.

Рисунок 1: кнопки автофильтра

Когда вы щелкаете по выпадающему списку, он открывается и представляет все значения, которые помещаются в этот столбец (рис. 1) Если вы отметите какое-либо значение, Excel скроет все строки, кроме тех, которые включают это значение.

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

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

  • Все. Отображает все элементы столбца. Используется для отмены фильтрации столбца.
  • Первые 10. Выбирает десять элементов списка.
  • Условие. Позволяет фильтровать список по нескольким условиям.
  • Пустые. Фильтрует список, отображая только строки с пустыми ячейками в данном столбце.
  • Непустые. Фильтрует список, показывая только строки с непустыми ячейками в данном столбце.

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

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

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

Автоматическая фильтрация по значениям в нескольких колонках

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

Рисунок 2: пример списка

Предположим, что вам нужны записи о продаже модемов в феврале. Тогда вы должны исключить все записи, кроме тех, где в поле «Месяц» указан февраль, а в поле «Товар» — модем. Для этого необходимо включить режим автофильтра. Затем нажмите кнопку раскрывающегося списка в поле Месяц и выберите Февраль . Выберите из списка записи с полем Месяц, установленным на Февраль. Из раскрывающегося списка в поле Товар выберите Модуль. Список снова будет отфильтрован по значениям в двух столбцах (рис. 3).

Рисунок 3: Список отфильтрованный по значениям в двух колонках

Автоматическая фильтрация обычно включает выбор одного значения в одном или нескольких столбцах. Если выбрать Условие из выпадающего списка, можно осуществлять более гибкую фильтрацию. Когда вы выбираете Условие, появляется диалоговое окно Пользовательский автофильтр (Рисунок 4).

Рис.4. Диалоговое окно пользовательского автофильтра

  • Значение больше или меньше установленного. Например, можно выбрать записи, указывающие на объем продаж, превышающие 10 000 .
  • Значения в интервале. Например, отобрать все записи, указывающие на объем продаж, превышающие 10 000 И не превышающие 50 000 .
  • Два отдельных значения. Например, отобрать записи, в которых находится информация об объеме продаж в городах Москва ИЛИ Курган .
  • Выборка по шаблону. Можно использовать символы подстановки "*" и "?" , чтобы отфильтровать список более гибким способом. Например, чтобы вывести на экран записи только о тех клиентах, фамилии которых начинаются с буквы B , используйте шаблон B* .

Условия в накладном списке

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

Название Первые 10 — это просто общепринятый термин, действие же опции не ограничивается нахождением только 10 "наибольших" элементов. На самом деле ее действие даже не ограничивается нахождением только наибольших элементов. При выборе опции Первые 10 появится диалоговое окно Наложение условия по списку (рис. 5).

Рис. 5. Наложение условия на диалоговое окно списка

С его помощью можно выбрать самые большие или самые маленькие элементы в списке и узнать их количество. В этом диалоговом окне можно также выбрать опцию Элементы из списка или % от числа элементов .

Построение диаграммы из отфильтрованных данных списка

Для создания некоторых многоцелевых диаграмм используются данные отфильтрованного списка. Этот способ очень полезен, так как только диаграмма позволяет наглядно увидеть картину, представленную теми или иными данными. При изменении критерия автофильтра автоматически изменяется и показывает значения, взятые только из "видимых" ячеек.

Убедитесь, что на вкладке Диаграмма диалогового окна Параметры (рис. 6) включена опция Отображать только видимые ячейки.

Как удалить значения ячеек? Выборочное удаление значений

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

Для наших целей запомним, что ячейка — это часть таблицы, которая образуется на пересечении строки и столбца. Ячейки занимают числа, текст, формулы и так далее, но они также могут содержать информацию о шрифтах, их размере и цвете, заливках, границах и так далее. Таким образом, можно удалить как сами ячейки со всем содержимым (при этом новая ячейка займет место удаленной, а пространство таблицы будет смещено вверх или влево), так и только часть информации, например, только значения или только форматы. Целью данной статьи является описание способов удаления значений, при которых сами ячейки остаются на своих местах, а информация об их формате остается неизменной.

Стандартные средства для удаления значений ячеек

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

Чтобы освободить место в таблице для добавления новых значений, можно поочередно выделить нужные диапазоны ячеек и нажать кнопку «Delete» на клавиатуре, как говорится, медленно, но верно. Проделав такую операцию полдюжины раз непреднамеренно, человек задается вопросом: «А можно ли ехать немного быстрее? Конечно, да. Вы должны это сделать. В Microsoft Excel 2007 и более поздних версиях для этой цели служит такой инструмент, как «Выделение группы ячеек». Для этого выберите вкладку Главная на ленте Excel, перейдите в группу Правка, нажмите кнопку Найти и выделить и выберите команду «Выделить группу ячеек». Появится одноименное диалоговое окно, с помощью которого можно выделить определенные ячейки таблицы и затем удалить их значения.

vyborochnoe-vydelenie-yacheek-excel

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

Дополнительные инструменты для выборочного удаления значений

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

Использование дополнительного модуля позволяет сделать это:

1. Удаление значений из ячеек, содержащих числовые значения;

2. Удаление значений из ячеек с текстовыми значениями

3. удалить значения формул из ячеек формул;

4. Удалите значения из ячеек, содержащих примечания;

5. удалите значения всех ячеек с цветной заливкой;

6. удалить значения из ячеек, имеющих только один цвет заливки;

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