Как в excel из одного столбца убрать значения другого столбца
И форум смотрел и в интернете, и сам пробовал. НЕ получается
Ситуация такая, есть 2 столбца, Столбец А - 22 тысячи строк, Столбец B - 2004 строк (в прикрепленном к теме примере, столбец B для формулы, я специальной вставкой транспорировал в строку). НУ и сделал значений 30 штук, для удобства и понимания логики действий с большим массивом.
Нужно следующее, взять каждое значение из Диапазона Ячеек - B1:BYC и проверить их на наличие в столбце A1:A20000.
Если в столбце A1:A20000 в какой-то ячейке встречается любое значение из диапазона B1:BYC, то ячейку в А1:A20000 удаляем, либо пишем туда какой-то символ, который потом найдем фильтром и массово удалим.
Что-то подобное делал вот такой формулой: =--ЕЧИСЛО(ПОИСК($B$1;$A$2:$A$4945)) - вставляешь в ячейку B2 и тянешь до низу. Есть вхождение - 1, нет вхождения -0
Но тогда таблица была небольшая и удобно было.
В данном же случае, таблица огромнейшая получится. И тянуть каждый столбец нереально на 2000 столбцов
Можно еще попробовать неким перебором: в ячейку С2 вставить формулу с условием: если ни одного значения из диапазона B1:BYC, не встречается в диапазоне A1:A20000, то в ячейку СN, вставляем значение ячейки AN, если же, какое-то значение из B1:BYC, встречается в диапазоне A1:A20000, то в ячейку СN - ставим любой символ. Потом по фильтру удалил.
Не могу формулу сделать здесь. Этот вариант наверняка будет быстрее, и проще, если конечно так можно сделать.
Надеюсь понятно описал
Пример прилагаю.
Спасибо
И форум смотрел и в интернете, и сам пробовал. НЕ получается
Ситуация такая, есть 2 столбца, Столбец А - 22 тысячи строк, Столбец B - 2004 строк (в прикрепленном к теме примере, столбец B для формулы, я специальной вставкой транспорировал в строку). НУ и сделал значений 30 штук, для удобства и понимания логики действий с большим массивом.
Нужно следующее, взять каждое значение из Диапазона Ячеек - B1:BYC и проверить их на наличие в столбце A1:A20000.
Если в столбце A1:A20000 в какой-то ячейке встречается любое значение из диапазона B1:BYC, то ячейку в А1:A20000 удаляем, либо пишем туда какой-то символ, который потом найдем фильтром и массово удалим.
Что-то подобное делал вот такой формулой: =--ЕЧИСЛО(ПОИСК($B$1;$A$2:$A$4945)) - вставляешь в ячейку B2 и тянешь до низу. Есть вхождение - 1, нет вхождения -0
Но тогда таблица была небольшая и удобно было.
В данном же случае, таблица огромнейшая получится. И тянуть каждый столбец нереально на 2000 столбцов
Можно еще попробовать неким перебором: в ячейку С2 вставить формулу с условием: если ни одного значения из диапазона B1:BYC, не встречается в диапазоне A1:A20000, то в ячейку СN, вставляем значение ячейки AN, если же, какое-то значение из B1:BYC, встречается в диапазоне A1:A20000, то в ячейку СN - ставим любой символ. Потом по фильтру удалил.
Не могу формулу сделать здесь. Этот вариант наверняка будет быстрее, и проще, если конечно так можно сделать.
Надеюсь понятно описал
Пример прилагаю.
Спасибо Limyh
И форум смотрел и в интернете, и сам пробовал. НЕ получается
Ситуация такая, есть 2 столбца, Столбец А - 22 тысячи строк, Столбец B - 2004 строк (в прикрепленном к теме примере, столбец B для формулы, я специальной вставкой транспорировал в строку). НУ и сделал значений 30 штук, для удобства и понимания логики действий с большим массивом.
Нужно следующее, взять каждое значение из Диапазона Ячеек - B1:BYC и проверить их на наличие в столбце A1:A20000.
Если в столбце A1:A20000 в какой-то ячейке встречается любое значение из диапазона B1:BYC, то ячейку в А1:A20000 удаляем, либо пишем туда какой-то символ, который потом найдем фильтром и массово удалим.
Что-то подобное делал вот такой формулой: =--ЕЧИСЛО(ПОИСК($B$1;$A$2:$A$4945)) - вставляешь в ячейку B2 и тянешь до низу. Есть вхождение - 1, нет вхождения -0
Но тогда таблица была небольшая и удобно было.
В данном же случае, таблица огромнейшая получится. И тянуть каждый столбец нереально на 2000 столбцов
Можно еще попробовать неким перебором: в ячейку С2 вставить формулу с условием: если ни одного значения из диапазона B1:BYC, не встречается в диапазоне A1:A20000, то в ячейку СN, вставляем значение ячейки AN, если же, какое-то значение из B1:BYC, встречается в диапазоне A1:A20000, то в ячейку СN - ставим любой символ. Потом по фильтру удалил.
Не могу формулу сделать здесь. Этот вариант наверняка будет быстрее, и проще, если конечно так можно сделать.
Надеюсь понятно описал
Пример прилагаю.
Спасибо Автор - Limyh
Дата добавления - 06.12.2015 в 16:12
Я сделал копию столбца A, чтобы можно было удалять данные, которые находятся в столбце A.
В ячейке B2 находится формула. Скорректируйте ее: в этом месте "C2:F2", вместо F укажите последний столбец, до которого нужно просматривать. Чтобы вставить эту формулу во все ячейки столбца B, скопируйте ячейку B2, выделите нужный фрагмент в столбце B и вставьте скопированное.
В ячейке C2 есть формула. Чтобы ее вставить во все столбцы, скопируйте C2, выделите ячейки, например C2:F10 и вставьте.
После этого в столбце B будет отображаться фраза "какой-то символ", если в столбце A есть слово или будет отображаться текст из столбца A, если нет слова.
Я сделал копию столбца A, чтобы можно было удалять данные, которые находятся в столбце A.
В ячейке B2 находится формула. Скорректируйте ее: в этом месте "C2:F2", вместо F укажите последний столбец, до которого нужно просматривать. Чтобы вставить эту формулу во все ячейки столбца B, скопируйте ячейку B2, выделите нужный фрагмент в столбце B и вставьте скопированное.
В ячейке C2 есть формула. Чтобы ее вставить во все столбцы, скопируйте C2, выделите ячейки, например C2:F10 и вставьте.
После этого в столбце B будет отображаться фраза "какой-то символ", если в столбце A есть слово или будет отображаться текст из столбца A, если нет слова. Karataev
Предположим, у вас есть два списка данных, как показано на скриншоте слева. Теперь вам нужно удалить или исключить имена в столбце A, если имя существует в столбце D. Как этого добиться? А что, если два списка находятся на двух разных листах? Эта статья предлагает вам два метода.
Easily exclude values in one list from another in Excel:
The Select Same & Different Cells utility of Kutools for Excel can help you quickly selecting all same cells in one list based on values in another column. And you can remove these same cell values manually by pressing the Delete key to exclude them in the list. See screenshot:
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Исключить значения в одном списке из другого с помощью формулы
Для этого вы можете применить следующие формулы. Пожалуйста, сделайте следующее.
1. Выберите пустую ячейку, которая находится рядом с первой ячейкой списка, который вы хотите удалить, затем введите формулу. = СЧЁТЕСЛИ ($ D $ 2: $ D $ 6; A2) в панель формул, а затем нажмите Enter ключ. Смотрите скриншот:
Внимание: В формуле $ D $ 2: $ D $ 6 - это список, на основе которого вы удаляете значения, A2 - это первая ячейка списка, который вы собираетесь удалить. Пожалуйста, измените их по своему усмотрению.
2. Продолжая выбирать ячейку результата, перетащите маркер заполнения вниз, пока он не достигнет последней ячейки списка. Смотрите скриншот:
3. Продолжайте выбирать список результатов, затем щелкните Данные > Сортировка от А до Я.
Затем вы можете увидеть, что список отсортирован, как показано на скриншоте ниже.
4. Теперь выберите все строки имен с результатом 1, щелкните правой кнопкой мыши выбранный диапазон и нажмите Удалить чтобы удалить их.
Теперь вы исключили значения из одного списка на основе другого.
Внимание: Если «список для удаления» находится в диапазоне A2: A6 другого листа, такого как Sheet2, примените эту формулу = IF (ISERROR (VLOOKUP (A2; Sheet2! $ A $ 2: $ A $ 6,1; FALSE)), «Сохранить», «Удалить») получить все Сохранить и Удалить результатов, отсортируйте список результатов от A до Z, а затем вручную удалите все строки имен, содержащие результат удаления на текущем листе.
Быстро исключать значения в одном списке из другого с помощью Kutools for Excel
Этот раздел будет рекомендовать Выберите одинаковые и разные ячейки полезности Kutools for Excel чтобы решить эту проблему. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки. Смотрите скриншот:
2. в Выберите одинаковые и разные ячейки диалоговое окно, вам необходимо:
- 2.1 Выберите список, из которого вы удалите значения в Найдите значения в коробка;
- 2.2 Выберите список, значения которого вы удалите, на основе Согласно информации коробка;
- 2.3 выберите Однокамерная вариант в на основании раздел;
- 2.4 Щелкните значок OK кнопка. Смотрите скриншот:
3. Затем появляется диалоговое окно, в котором указывается, сколько ячеек было выбрано, нажмите OK кнопку.
4. Теперь значения в столбце A выбираются, если они существуют в столбце D. Вы можете нажать кнопку Удалить клавишу, чтобы удалить их вручную.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
1. содержит много данных (слова, цифры, сочетания букв и т.д.)
2. содержит меньше данных и какие-то данные встречающиеся в первом
Нужно: удалить из столбца 1 данные которые дублирующиеся в столбце 2. Данные в столбце 2 при этом остаются неизменными
т.к. данную процедуру мне нужно будет проводить регулярно, мне нужна простая формула, а не какой-то навороченный костыль на один раз.
Буду очень благодарна за помощь.
[moder]Показывайте файл
1. содержит много данных (слова, цифры, сочетания букв и т.д.)
2. содержит меньше данных и какие-то данные встречающиеся в первом
Нужно: удалить из столбца 1 данные которые дублирующиеся в столбце 2. Данные в столбце 2 при этом остаются неизменными
т.к. данную процедуру мне нужно будет проводить регулярно, мне нужна простая формула, а не какой-то навороченный костыль на один раз.
Буду очень благодарна за помощь.
[moder]Показывайте файл twini
1. содержит много данных (слова, цифры, сочетания букв и т.д.)
2. содержит меньше данных и какие-то данные встречающиеся в первом
Нужно: удалить из столбца 1 данные которые дублирующиеся в столбце 2. Данные в столбце 2 при этом остаются неизменными
т.к. данную процедуру мне нужно будет проводить регулярно, мне нужна простая формула, а не какой-то навороченный костыль на один раз.
Буду очень благодарна за помощь.
[moder]Показывайте файл Автор - twini
Дата добавления - 20.10.2014 в 18:48
С Уважением, Richman
Данные со второго столбца скопируйте вниз первого столбца, после выделите столбец1 и во вкладке данные-удалить дубликаты
Данные со второго столбца скопируйте вниз первого столбца, после выделите столбец1 и во вкладке данные-удалить дубликаты Richman
С Уважением, Richman
Richman, вы не поняли. Удалять дубликаты я умею. Но в данном случае мне надо сделать не это.
1 ст.
слон
кот
бегемот
тигр
лягушка
2 ст.
слон
гепард
заяц
волк
тигр
В результате операции должно получится
1 ст.
кот
бегемот
лягушка
2 ст. - БЕЗ изменений!
Richman, вы не поняли. Удалять дубликаты я умею. Но в данном случае мне надо сделать не это.
1 ст.
слон
кот
бегемот
тигр
лягушка
2 ст.
слон
гепард
заяц
волк
тигр
В результате операции должно получится
1 ст.
кот
бегемот
лягушка
2 ст. - БЕЗ изменений! twini
1 ст.
слон
кот
бегемот
тигр
лягушка
2 ст.
слон
гепард
заяц
волк
тигр
В результате операции должно получится
1 ст.
кот
бегемот
лягушка
2 ст. - БЕЗ изменений! Автор - twini
Дата добавления - 21.10.2014 в 15:45
Richman, это неверно
Например, если в (2) есть данные, которых нет в (1) - то после ваших действий они добавятся в (1). И если в (1) есть дубли, но их, тем не менее, нет в (2) - то их удалять не надо.
twini, вас же просили приложить файл с примером, а не нарисовать пример
Впрочем поймите такую вещь - если вы в столбец 1 добавляете данные вручную - то как туда прописать формулы? Формулы не умеют изменять ячейки "вне себя". Формулами вашу задачу можно решить - но, например, результат будет находиться в отдельном столбце. Полученные значения которого вы затем можете использовать для обработки "первого" столбца.
Либо можно решать данную задачу макросом. Что проще и быстрее.
Richman, это неверно
Например, если в (2) есть данные, которых нет в (1) - то после ваших действий они добавятся в (1). И если в (1) есть дубли, но их, тем не менее, нет в (2) - то их удалять не надо.
twini, вас же просили приложить файл с примером, а не нарисовать пример
Впрочем поймите такую вещь - если вы в столбец 1 добавляете данные вручную - то как туда прописать формулы? Формулы не умеют изменять ячейки "вне себя". Формулами вашу задачу можно решить - но, например, результат будет находиться в отдельном столбце. Полученные значения которого вы затем можете использовать для обработки "первого" столбца.
Либо можно решать данную задачу макросом. Что проще и быстрее. AndreTM
Skype: andre.tm.007
Donate: Q iwi: 9517375010
twini, вас же просили приложить файл с примером, а не нарисовать пример
Впрочем поймите такую вещь - если вы в столбец 1 добавляете данные вручную - то как туда прописать формулы? Формулы не умеют изменять ячейки "вне себя". Формулами вашу задачу можно решить - но, например, результат будет находиться в отдельном столбце. Полученные значения которого вы затем можете использовать для обработки "первого" столбца.
Либо можно решать данную задачу макросом. Что проще и быстрее. Автор - AndreTM
Дата добавления - 21.10.2014 в 15:50
Признаюсь, с экселем раньше плотно не общался, больше с его онлайновым аналогом в гугл.
Там, в гугл докс нужная мне операция выполняется очень просто.
Есть колонка с данными, мне нужно, чтоб в другой колонке отображались эти данные. Я ставлю курсор на верхнюю ячейку, и ввожу "=А2", потом подвожу курсор к правому нижнему краю и тяну вниз и все "копируется". Т.е. в нижних ячейках появляется формула "=A3", "=A4" и т.д.
Если в дальнейшем я поменяю значение в какой-нибудь ячейке в столце A все поменяется и в стобце D - именно поэтому я не использую просто ctrl+C/ctrl+V.
У меня в экселе все почему-то происходит иначе, по необъяснимой для меня логике.
Проделываю ту же самую операцию, и вот что получается.
Если просмотреть формулы в ячейках в столбце C - то можно увидеть, что они вприсались по тому же принципу - "=A3", "=A4", но значение - везде стоит 1, как в А1.
В чем проблема?
Признаюсь, с экселем раньше плотно не общался, больше с его онлайновым аналогом в гугл.
Там, в гугл докс нужная мне операция выполняется очень просто.
Есть колонка с данными, мне нужно, чтоб в другой колонке отображались эти данные. Я ставлю курсор на верхнюю ячейку, и ввожу "=А2", потом подвожу курсор к правому нижнему краю и тяну вниз и все "копируется". Т.е. в нижних ячейках появляется формула "=A3", "=A4" и т.д.
Если в дальнейшем я поменяю значение в какой-нибудь ячейке в столце A все поменяется и в стобце D - именно поэтому я не использую просто ctrl+C/ctrl+V.
У меня в экселе все почему-то происходит иначе, по необъяснимой для меня логике.
Проделываю ту же самую операцию, и вот что получается.
Если просмотреть формулы в ячейках в столбце C - то можно увидеть, что они вприсались по тому же принципу - "=A3", "=A4", но значение - везде стоит 1, как в А1.
В чем проблема? konkretno
У меня в экселе все почему-то происходит иначе, по необъяснимой для меня логике.
Проделываю ту же самую операцию, и вот что получается.
Если просмотреть формулы в ячейках в столбце C - то можно увидеть, что они вприсались по тому же принципу - "=A3", "=A4", но значение - везде стоит 1, как в А1.
В чем проблема? Автор - konkretno
Дата добавления - 14.08.2014 в 16:08
Начиная с 2007-й версии функция удаления дубликатов является стандартной - найти ее можно на вкладке Данные - Удаление дубликатов (Data - Remove Duplicates) :
В открывшемся окне нужно с помощью флажков задать те столбцы, по которым необходимо обеспечивать уникальность. Т.е. если включить все флажки, то будут удалены только полностью совпадающие строки. Если включить только флажок заказчик, то останется только по одной строке для каждого заказчика и т.д.
Способ 2. Расширенный фильтр
Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data) .
Предположим, что у нас имеется вот такой список беспорядочно повторяющихся названий компаний:
Выбираем в меню Данные - Фильтр - Расширенный фильтр (Data - Filter - Advanced Filter) . Получаем окно:
- Выделяем наш список компаний в Исходный диапазон (List Range) .
- Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
- Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.
Получите список без дубликатов:
Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE) :
Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.
Способ 3. Выборка уникальных записей формулой
Чуть более сложный способ, чем первые два, но зато - динамический, т.е. с автоматическим пересчетом, т.е. если список редактируется или в него дописываются еще элементы, то они автоматически проверяются на уникальность и отбираются. В предыдущих способах при изменении исходного списка нужно будет заново запускать Расширенный фильтр или жать на кнопку Удаление дубликатов.
Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:
Первая задача - пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"")
В английской версии это будет:
=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;"")
Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз - дает ему последовательно возрастающий номер.
Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы - Диспетчер имен (Formulas - Name manager) или в старых версиях - через меню Вставка - Имя - Присвоить (Insert - Name - Define) :
- диапазону номеров (A1:A100) - имя NameCount
- всему списку с номерами (A1:B100) - имя NameList
Теперь осталось выбрать из списка NameList все элементы имеющие номер - это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:
или в английской версии Excel:
=IF(MAX(NameCount)
Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:
Читайте также: