Как исключить ячейку из диапазона excel

Обновлено: 03.07.2024

как добавить ячейку (диапазон) Х в диапазон А понятно
set A = Application.Union(A, X)

А есть простой способ как ИСКЛЮЧИТЬ ячейку Х из диапазона А.

т.е. на входе диапазон и какая-то ячейка из него, на выходе тот же диапазон, но уже без указанной ячейки (ни сама ячейка на листе, ни данные в ней не должны пострадать). Может есть стандартные средства VBA для этого?

41001804815208 - ЮMoney бывш.Яндекс-кошелек благодарности за удачные советы и решения можно отправлять прямо сюда)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете Вроде кто-то из гуру делал это, и даже не для одной ячейки. Стандартных средств точно нет.
Если все же делать: можно перебрать области диапазона и проверять intersect с ячейкой. Если пересечения нет, область добавляется к выходному диапазону. Если пересечение есть, ячейка делит эту область на 4 диапазона (в общем случае): сверху, снизу, строка слева и строка справа. Или слева, справа, столбец сверху и столбец снизу - можно выбрать этот вариант, если область имеет .rows.count > .columns.count.
Координаты диапазонов вычисляются арифметически, и диапазоны добавляются к выходному диапазону. Ну ни как не хотел VBA такое имя пропускать: DeLCeLL Последний раз редактировалось maksim_serg; 06.02.2014 в 19:24 . Причина: Немного изменил. Удаляет диапозон ячеек из диапозона ячеек Если диапазон небольшой, можно, конечно, и по ячейкам. Но попробуйте таким способом исключить что-нибудь из целого столбца в 2007 Если диапазон небольшой, можно, конечно, и по ячейкам. Но попробуйте таким способом исключить что-нибудь из целого столбца в 2007

maksim_serg, знаете что MsgBox показал? 61 сек.

а теперь представте так, по одной, надо удалить ячейки из всего столбика
= 61/2*1048000/60/60/24 = 369 дней.
чуть больше года работы и готово!

моя процедура делает это за 3-4 минуты это где-то в 200тыс.раз быстрее, чем 369 дней.

41001804815208 - ЮMoney бывш.Яндекс-кошелек благодарности за удачные советы и решения можно отправлять прямо сюда)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете Если из диапазона "A" требуется получить диапазон с "вырезанной" ячейкой "X", то перебирать поячеечно, это слишком долго. Предлагаю, примерно так:

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

ячейка, обозначена Я разбивает поле листа на 4 диапазона

могут отсутсвовать один или два диапазона, когда ячейка находится в первой (последней) строке (колонкке) листа.
эти 4-е диапазона обьединялись в один, пересечение обьединенного диапазона и исходного давало исходный без ячейки Я.

но в ситуации, когда отмечена колонка без 1 ячейки и надо инвертировать отметку и этот алгоритм работал несколько минут (3-4-5) не засекал точно, но долго - это факт.

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

Исключить значения в одном списке из другого с помощью формулы

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

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. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки. Смотрите скриншот:

2. в Выберите одинаковые и разные ячейки диалоговое окно, вам необходимо:

2.1 Выберите список, из которого вы удалите значения в Найдите значения в коробка;
2.2 Выберите список, значения которого вы удалите, на основе Согласно информации коробка;
2.3 выберите Однокамерная вариант в на основании раздел;
2.4 Щелкните значок OK кнопка. Смотрите скриншот:

3. Затем появляется диалоговое окно, в котором указывается, сколько ячеек было выбрано, нажмите OK кнопку.

4. Теперь значения в столбце A выбираются, если они существуют в столбце D. Вы можете нажать кнопку Удалить клавишу, чтобы удалить их вручную.

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

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

Среднее с игнорированием / исключением пустых ячеек с формулой
Среднее значение только для ячеек со значениями (исключая пустые ячейки) с помощью Kutools for Excel (всего 1 шаг)

Среднее с игнорированием / исключением пустых ячеек с формулой

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

На самом деле мы можем исключить ячейки с нулями, чтобы решить эту проблему. Например, вы хотите усреднить диапазон A1: D13 с игнорированием / исключением пустых ячеек и нулей, вы можете применить формулу = СРЗНАЧЕСЛИ (A1: D13; «<> 0») как показано ниже:

Среднее значение только для ячеек со значениями (исключая пустые ячейки) с помощью Kutools for Excel

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

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Выберите диапазон, в котором вы хотите усреднить, за исключением пустых ячеек, и щелкните значок Kutools > Выберите > Выберите Un blank Cells.

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

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Имеем диапазон ячеек с данными, в котором есть пустые ячейки:

Задача - удалить пустые ячейки, оставив только ячейки с информацией.

Способ 1. Грубо и быстро

Выделяем исходный диапазон
Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.

Способ 2. Формула массива

Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)

Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 - НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.

Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:

В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),"",INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>"",ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))

Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) - и мы получим исходный диапазон, но без пустых ячеек:

Способ 3. Пользовательская функция на VBA

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

Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этой функции:

Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:

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