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

Обновлено: 03.07.2024

У меня есть диапазон, равный группе ячеек (скажем, A1:E5) — назовите его rngSquare. Я
повторил это и выбрал, скажем, 15 несмежных ячеек, чтобы создать
другой диапазон — назовем его rngWork. Теперь давайте добавим диапазоны для каждой строки
квадрата - rngRow1 - rngRow5.

Я знаю, что могу пересекать и объединять эти диапазоны (например, каждую ячейку в
rngRow1, которая также находится в rngWork), чтобы сузить миссию поиска и выбора.
Но скажем, я хочу искать каждую ячейку в rngWork, **кроме** rngRow1. Должен ли я
перебирать все диапазоны, такие как:
Union(Intersect(rngWork, rngRow2), Intersect(rngWork, rngRow3) и т. д. . .
или есть более простой способ сделайте что-то вроде:
Intersect(rngWork, Not rngRow1).

"Эд" написал в сообщении
news:es5DERSFGHA.1124@TK2MSFTNGP10.phx.gbl.
>У меня есть диапазон, равный группе ячеек (скажем, A1:E5) — назовите его rngSquare.
>Я
> повторил это и выбрал, скажем, 15 несмежных ячеек, чтобы создать
> другой диапазон — назовем его rngWork. Теперь подкинем диапазоны для каждой строки
> из
> квадрата - rngRow1 - rngRow5.
>
> Я знаю, что могу пересекать и объединять эти диапазоны (например, каждую ячейку в
> rngRow1, которая также находится в rngWork), чтобы сузить область поиска и выбора
> миссия.
> Но скажем, я хочу искать каждую ячейку в rngWork, **кроме** rngRow1. Должен ли я
> перебирать все диапазоны, например:
> Union(Intersect(rngWork, rngRow2), Intersect(rngWork, rngRow3) и т. д. . .
> или существует более простой способ сделать что-то вроде:
> Intersect(rngWork, Not rngRow1).
>
> Ed
>
>

С первого поста я почувствовал, как глубокие воды смыкаются у меня над головой!! 8>)
Я кое-что почерпнул из первых двух постов - после этого это пошло далеко
за пределы моего понимания. (Как и мои подростки, слушающие меня!) Итак, я думаю, что приму
общее предположение, что да, это можно сделать, но не мной в данный момент,
и буду ковылять свой "неуклюжий" путь до конца. пока мое понимание не станет
больше.

Спасибо, что откликнулись.
Эд

"Норман Джонс" написал в сообщении
news:O6xNtZSFGHA.644@TK2MSFTNGP09.phx.gbl.
> Привет, Эд,
>
> См.:
>
> http://tinyurl.com/agpz9
>
> < br />> ---
> С уважением,
> Норман
>
>
>
> "Эд" написал в сообщении
> новости: es5DERSFGHA.1124@TK2MSFTNGP10.phx.gbl.
> >У меня есть диапазон, заданный для группы ячеек (скажем, A1:E5) — назовите его rngSquare.
> >Я
> > повторил это и выбрал, скажем, 15 несмежных ячеек, чтобы
создать
> > другой диапазон — назовем его rngWork. Теперь подкинем диапазоны для каждой строки
>> из
>> квадрата - rngRow1 - rngRow5.
>>>
> > Я знаю, что могу пересекать и объединять эти диапазоны (например, каждую ячейку в
> > rngRow1, которая также находится в rngWork), чтобы сузить область поиска и выбора.
> Миссия.
> > Но скажем, я хочу искать каждую ячейку в rngWork, **кроме** rngRow1. Должен ли я
> > перебирать все диапазоны, например:
> > Union(Intersect(rngWork, rngRow2), Intersect(rngWork, rngRow3) и т. д. . .
..
> > или есть более простой способ сделать что-то вроде:
> > Intersect(rngWork, Not rngRow1).
> >
> > Ed
> >
> >
>
>

Следующая ветка также посвящена этой теме, она короче и,
возможно, даст вам что-то полезное:

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

Я пытаюсь создать диапазон, состоящий из всех ячеек в rng1, кроме ячеек
в rng2. Я перебрал все ячейки rng1, создал значение из
строки и столбца ячейки и прочитал его в aryRng1. Повторяется с rng2 и
aryRng2. Затем, используя код Берни, я удалил все значения в ayrRng2
из aryRng1. Затем я повторил пересмотренный aryRng1 и установил диапазон
в .Cells(Left(aryRng1(x), Right(aryRng1(x)), используя Union для добавления к
диапазону.

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

Спасибо, Уолт, Том, Норман и Берни!
Ред

Dim aryRng1 As Variant 'ячейки большого диапазона
Dim aryRng2 As Variant 'ячейки должны быть исключены

Dim rng1 As Range 'основной диапазон
Dim rng2 As Range 'исключаемый диапазон
Dim rng3 As Range 'новый сформированный диапазон
Dim rngCl As Range 'временной диапазон для ячеек

Размерьте x до длины, y до длины, z до длины
Размерьте a до длины, b до длины, c до длины

Размять рабочие листы как рабочую книгу
Размять рабочие листы как рабочие листы

Set wkb = ActiveWorkbook
Set wks = wkb.Sheets("Sheet1")

Установить rng1 = wks.Range("A1:E5")
Установить rng2 = wks.Range("B1:B5")

x = rng1.Cells.Count
y = rng2.Cells.Count

' Считать ссылки на ячейки из rng1 в массив
ReDim aryRng1(1 To x) As Variant
c = 0
Для каждого rngCl в rng1
c = c + 1 < br />aryRng1(c) = rngCl.Row & rngCl.Column
Следующий rngCl

' Считать ссылки на ячейки из rng2 в массив
ReDim aryRng2(1 To y) As Variant
c = 0
Для каждого rngCl в rng2
c = c + 1 < br />aryRng2(c) = rngCl.Row & rngCl.Column
Следующий rngCl

' Следующий код принадлежит Берни Дейтрику
' с сайта microsoft.public.excel.programming NG.
' Он удаляет любое значение в aryRng2 из aryRng1.
' Результатом являются все ссылки на позиции ячеек в rng1
' кроме тех, которые также ссылаются на ячейки в rng2.
При ошибке возобновить следующий
Для x = 1 To UBound(aryRng2)
aryRng1(Application.Match(aryRng2(x), aryRng1, False)) = ""
Следующий x

Для x = UBound(aryRng1) To 1 Step -1
Если aryRng1(x) = "" Тогда
z = z - 1
Для y = x To UBound(aryRng1) - 1
aryRng1(y) = aryRng1(y + 1)
Следующий y
Конец, если
Следующий x

В Microsoft Excel мы используем формулу СРЗНАЧ, чтобы определить среднее значение определенных значений из диапазона данных. Чтобы извлечь точное значение среднего, нам нужно знать, как исключить ячейку в формуле Excel СРЗНАЧ. Потому что иногда набор данных содержит нулевые или пустые ячейки, и Excel учитывает эту ячейку для расчета среднего значения. Таким образом, это дает отклонение от действительного среднего значения, которое мы предполагаем.

Скачать рабочую тетрадь

Вы можете скачать учебную тетрадь отсюда.

4 метода исключения ячейки в формуле Excel AVERAGE

В этой статье мы расскажем о 4 методах исключения ячейки из формулы Excel СРЕДНЕЕ. Чтобы проиллюстрировать вам эти методы, мы можем использовать разные наборы данных для разных методов. Мы рассмотрим несколько примеров, чтобы прояснить все методы в этой статье.

1. Введите аргумент вручную, чтобы исключить ячейку в формуле Excel AVERAGE

Самый простой и простой способ исключить ячейку из формулы Excel СРЗНАЧ — это вручную ввести аргумент в формулу СРЗНАЧ. Поскольку мы вводим аргументы вручную, можно легко исключить ячейки, которые мы не хотим включать в формулу СРЗНАЧ. В следующем наборе данных у нас есть суммы продаж для разных людей в разные дни недели. Мы видим, что в наборе данных есть нулевые и пустые ячейки. Если мы введем диапазон (B5:E15) в качестве аргумента формулы СРЗНАЧ, будет рассчитано среднее значение с учетом нулевых и пустых ячеек. Но мы хотим исключить нулевые и пустые ячейки из расчета среднего.

Итак, давайте взглянем на шаги по вводу аргумента вручную для исключения ячейки в формуле СРЕДНЕГО Excel.

ШАГИ:

Сначала выберите ячейку D12, в которую мы хотим вернуть среднее значение.
Затем вставьте в эту ячейку следующую формулу:

Чтобы исключить нулевые и пустые ячейки, удерживайте клавишу Ctrl и выберите ячейки, которые вы хотите использовать в качестве аргументов в формуле СРЗНАЧ.
После этого нажмите Enter.

Наконец, вышеуказанные действия дают нам среднее значение только для выбранных ячеек в ячейке D12. Таким образом, при ручном выборе аргументов мы можем легко исключить ячейки из формулы СРЗНАЧ.

2. Игнорировать пустые/нулевые ячейки, чтобы исключить ячейку в формуле Excel AVERAGE

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

Теперь давайте взглянем на шаги Игнорировать пустые/нулевые ячейки в формуле Excel AVERAGE.

ШАГИ:

Сначала выберите ячейку D13.
Затем вставьте в эту ячейку следующую формулу:

Итак, мы получаем среднее значение для диапазона значений (C6:E10). Если мы заметим приведенный выше расчет, формула СРЗНАЧ по умолчанию исключает пустые ячейки. Но он считает нулевые ячейки. Вот почему среднее значение отличается от предыдущего примера.

Во-вторых, мы перейдем ко второму случаю. Перейдите на вкладку "Файл".

Затем выберите параметр «Дополнительно» в появившемся диалоговом окне. Прокрутите вниз и снимите флажок «Показывать ноль в ячейках с нулевым значением» в разделе «Параметры отображения для этого листа».
Теперь нажмите OK.

Итак, мы видим, что это удалит нулевые значения из набора данных.

В-третьих, выберите ячейку E14 и вставьте в нее следующую формулу:

Затем нажмите Enter.
Наконец, мы видим, что результат одинаков для обоих случаев. Итак, если у нас есть нулевые значения в наборе данных, мы должны удалить их вручную, чтобы исключить их из расчета формулы СРЗНАЧ.

Похожие чтения:

3. Используйте формулу СРЕДНЕГО для игнорирования ячеек с ошибками

Иногда в нашем наборе данных будут значения ошибок. Если мы включим эти ячейки с ошибками в нашу формулу Excel, мы также получим ошибку в нашем результате. В следующем наборе данных у нас есть три ячейки ошибок. Чтобы разъяснить вам этот метод, сначала мы рассчитаем среднее значение для диапазона данных, включая ячейки ошибок. Во-вторых, мы сделаем то же самое, но на этот раз исключим ошибочные ячейки с помощью функции ЕСЛИОШИБКА.

Итак, давайте посмотрим, как использовать формулу СРЗНАЧ для игнорирования ячеек с ошибками в Excel.

ШАГИ:

В начале выберите ячейку D13 и вставьте следующую формулу:

Итак, мы видим, что приведенная выше команда возвращает ошибку в ячейке D13.

Далее выберите ячейку D14 и вставьте следующую формулу:

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

🔎 Как работает формула?

ЕСЛИОШИБКА(C6:E10,""). Эта часть проверяет наличие ошибок в диапазоне данных (C6:C10) и возвращает значения, исключая ошибочные ячейки.
СРЗНАЧ(ЕСЛИОШИБКА(C6:E10,"")): возвращает среднее значение для ячеек в диапазоне данных (C6:C10), исключая ячейки с ошибками.

4. Сочетание функций Excel SUM и COUNT для исключения ячеек

В этом примере мы будем использовать комбинацию функций СУММ и СЧЕТ, чтобы исключить ячейку из формулы СРЗНАЧ. В следующем наборе данных мы рассчитаем средние продажи в разные дни недели. Во время расчета среднего мы исключим ячейки, содержащие нулевые значения.

Давайте посмотрим, как это сделать, выполнив несколько простых шагов.

ШАГИ:

Сначала выберите ячейку C11 и вставьте следующую формулу:

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

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

Приведенное выше действие скопирует формулу ячейки C13 в ячейки D13 и E13 и вернет средние значения суммы продаж за вторник и среду.

🔎 Как работает формула?

COUNT(C6:C10)-2: подсчитывает общее количество ячеек от C6 до C10 и игнорирует две самые нижние ячейки, которые являются нулевыми ячейками.
СУММ(C6:C10): возвращает итоги для ячеек с C6 по C10.
СУММ(C6:C10)-МАЛЕНЬКИЙ(C6:C10,1)-МАЛЕНЬКИЙ(C6:C10,2): Здесь функция МАЛЕНЬКИЙ определяет две младшие части диапазона значений (C6:C10). Затем из общей суммы вычитаются два самых низких значения.
СУММ(C6:C10)-МАЛЕНЬКИЙ(C6:C10,1)-МАЛЕНЬКИЙ(C6:C10,2))/(СЧЁТ(C6:C10)-2: возвращает среднее значение, за исключением двух нулевых ячеек.

Заключение

В конце этой статьи вы узнаете, как исключить ячейку из формулы Excel СРЗНАЧ. Чтобы попрактиковаться, загрузите рабочую тетрадь, добавленную к этой статье. Если вы чувствуете какую-либо путаницу, просто оставьте комментарий в поле ниже. Мы постараемся ответить как можно скорее. Оставайтесь с нами, чтобы узнать о других интересных решениях проблем с Microsoft Excel.

Подготовьте два столбца, например столбец A и столбец B, в столбце A перечислены некоторые значения, а в столбце B перечислены некоторые значения, существующие в столбце A, но их необходимо удалить из столбца A. Если мы хотим исключить эти совпадающие значения из столбца A , мы можем использовать функцию ВПР или функцию СЧЁТЕСЛИ, чтобы найти их. В этой статье будет показан метод исключения значений из одного столбца, ссылающихся на другой столбец с помощью функций ВПР и СЧЁТЕСЛИ.

Предварительное условие:

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

Способ 1. Исключение значений из одного столбца с помощью функции ВПР

Шаг 1. Вставьте новый столбец между столбцами B и C. Затем в столбце C2 введите формулу

Шаг 2. Нажмите «Ввод». Убедитесь, что в этой ячейке отображается Ада.

Шаг 3. Перетащите маркер заполнения вниз. Убедитесь, что C2:C11 заполнены возвращаемыми значениями функции ВПР.

На снимке экрана видно, что значения, которые необходимо удалить, отображаются в столбце C.

Шаг 4. Создайте фильтр на C1. Выберите строку 1, нажмите «Данные» на ленте, выберите «Фильтр».

Затем значения, которые необходимо исключить, фильтруются в столбце B.

Шаг 6. Теперь вы можете выделить их или удалить по вашему запросу. Если вы хотите выделить их, просто выберите диапазон, отмеченный красным на снимке экрана выше, и нажмите кнопку «Главная» -> «Цвет заливки», чтобы выделить их.

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

Затем удалите эти отфильтрованные ячейки (нажмите F5, чтобы вызвать диалоговое окно «Перейти», а затем нажмите кнопку «Специальная», отметьте параметр «Только видимые ячейки» и нажмите «ОК», все видимые ячейки будут выбраны, после чего вы сможете их удалить). Скопируйте и снова вставьте обновленный столбец в столбец B.

Метод 2. Исключение значений из одного столбца с помощью функции СЧЁТЕСЛИ

Действия аналогичны функции ВПР.

Шаг 1. Вставьте новый столбец между столбцами B и C. Затем в столбце C2 введите формулу

=СЧЁТЕСЛИ($D$2:$D$6,B2). Функция COUNTIF используется для подсчета того, сколько раз B2 встречается в диапазоне $D$2:$D$6. Если значение B2 появляется в выбранном диапазоне, СЧЁТЕСЛИ возвращает время появления B2, если B2 не появляется, СЧЁТЕСЛИ возвращает 0.

Шаг 2. Перетащите маркер заполнения вниз, пока не дойдете до последней ячейки в столбце C. Убедитесь, что ячейки C2:C11 заполнены возвращаемым значением 1 или 0.

Для значений в столбце B, если в соседней ячейке столбца C отображается 1, это значение следует исключить.

Связанные функции

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

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

Функция Excel XLOOKUP была добавлена в Excel в качестве бета-функции в августе 2019 г. и теперь доступна исключительно в Microsoft 365 (по состоянию на июль 2021 г.). Однако если вы попадаете в эту категорию и часто имеете дело с большими наборами файлов .

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

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

ВПР — одна из ключевых функций среди всех функций поиска и ссылок в Excel. Сегодня мы покажем вам применение функции ВПР при наличии двух интерполяционных таблиц. ПРИМЕР В таблицах 1 и 2 указаны курсы на 2020 год.

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

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

Как выделить весь столбец, кроме одной ячейки?

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

Сначала откройте диалоговое окно «Найти замену» и на вкладке «Заменить» сделайте все ячейки, содержащие значения NULL, пустыми. затем нажмите F5 и выберите параметр «Пустой», теперь щелкните правой кнопкой мыши активный лист и выберите «Удалить», затем параметр «Вся строка». он удалит все эти строки на основе значения ячейки, содержащего слово NULL.

Как заставить формулу игнорировать скрытые ячейки?

Выберите пустую ячейку, в которую вы поместите результат подсчета, введите в нее формулу =ПРОМЕЖУТОЧНЫЙ ИТОГ(102,C2:C22) (C2:C22 — это диапазон, в котором вы хотите подсчитать, игнорируя скрытые вручную ячейки и строки) и нажмите клавишу Enter.

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

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

Выберите ячейку C2.
Введите формулу =ЕСЛИ(И(ЧИСЛО(A2),ЧИСЛО(B2)),A2*B2", ")
Нажмите Enter на клавиатуре.
Функция вернет 3 в ячейке C2, так как обе ячейки содержат числа.

Почему я не могу удалить одну ячейку в Excel?

Здравствуйте! Вы пытаетесь сдвинуть ячейки влево, нажав Ctrl+- (Ctrl и минус). Поле «Удалить целые строки» появляется, потому что где-то на листе есть активный автофильтр. Щелкните значок автоматического фильтра, чтобы удалить автоматический фильтр на листе, а затем нажмите Ctrl+-.

Как удалить содержимое из ячейки?

Если вы хотите удалить ячейки с рабочего листа и сдвинуть окружающие ячейки, чтобы заполнить пространство, вы можете выбрать ячейки и удалить их. На вкладке "Главная" в группе "Ячейки" щелкните стрелку рядом с кнопкой "Удалить" и выберите "Удалить ячейки".

Как выделить все ячейки, кроме первой строки?

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

Как выбрать все значения в столбце в Excel?

Выберите одну или несколько строк и столбцов. Нажмите букву вверху, чтобы выбрать весь столбец. Или щелкните любую ячейку в столбце, а затем нажмите Ctrl + пробел. Выберите номер строки, чтобы выбрать всю строку. Или щелкните любую ячейку в строке, а затем нажмите Shift + Space.

Как снять выделение с ячейки в Excel?

Чтобы отменить выделение выбранной ячейки, удерживайте нажатой клавишу CTRL (или Command на Mac) и щелкните ячейки, выделение которых нужно отменить. Чтобы отменить выбор диапазона выбранных ячеек, удерживайте нажатой клавишу CTRL (или Command для Mac) и перетащите диапазон, который хотите отменить, начиная с выбранного диапазона. Отменить выделение выбранной ячейки в Excel.

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

Чтобы отменить выбор диапазона выбранных ячеек, удерживайте нажатой клавишу CTRL (или Command для Mac) и перетащите диапазон, выделение которого нужно отменить, начиная с выделенного диапазона. Отменить выделение выбранной ячейки в Excel Как выбрать несколько ячеек или диапазонов Это не изменилось, вы по-прежнему можете использовать клавишу CTRL (или Command на Mac), чтобы щелкнуть невыбранную ячейку, чтобы выбрать ее.

Как отменить выбор диапазона ячеек на Mac?

Чтобы отменить выделение выбранной ячейки, удерживайте нажатой клавишу CTRL (или Command на Mac) и щелкните ячейки, выделение которых нужно отменить. Чтобы отменить выбор диапазона выделенных ячеек, удерживайте нажатой клавишу CTRL (или Command для Mac) и перетащите диапазон, выделение которого нужно отменить, начиная с выделенного диапазона.

Как удалить ячейку из диапазона в Java?

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

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