Как вернуть значение ячейки каждые пять или n-ю строку в Excel?

Скопируйте каждую n-ю строку с одного листа на другой

Электронная таблица в Excel имеет 700 строк, 1 столбец. Я забочусь о каждой 7-й строке. Было бы утомительно заходить и удалять 6 строк между каждой интересующей меня строкой. Чтобы решить эту проблему, я создал другой рабочий лист, в котором я ссылаюсь на каждую ячейку.

У меня нет времени вводить все эти формулы. 100 раз. Первоначально я думал, что он поймет, что я пытаюсь сделать, если я выберу три и перетащу поле, но не повезло.

Есть идеи, как сделать его элегантным/эффективным?

Поместите это в А1 вашего нового листа:

. и копировать. Начните со строки 1 или другой ячейки в строке 1, затем измените ROW() на ROW (A1) или другую ячейку в строке 1.

Если вы хотите скопировать n-ую строку, но несколько столбцов, используйте формулу:

Ее также можно скопировать.

Если бы я извлекал каждую седьмую строку, я бы «вставил» столбец перед столбцом «A». Затем (если строка 1 имеет строку заголовка) я бы написал числа 1,2,3,4,5,6,7,7 в строке 2,3,4,5,6,7,8, извлек 1,2,3,4,5,6,7 и вставил этот блок в конец листа (700 строк). Результат будет следующим: 1,23,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7. Теперь выполните сортировку по возрастанию данных в столбце «A». После сортировки все первые будут первыми в серии, а все седьмые — седьмыми.

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

Подход OFFSET

F FSET требует 3 обязательных аргумента. Первый — это заданная ячейка, от которой мы хотим произвести компенсацию. Следующие два указывают, сколько строк и столбцов мы хотим сместить (влево и вправо). OFFNET возвращает содержимое ячейки, к которой ведет. OFFSET(A1, 1, 2), например, возвращает содержимое ячейки C2, потому что A1 — это 1. Тогда, если мы прибавим к этому 1, то получим 2. Что соответствует ячейке C2.

R OW() можно использовать для возврата n-й строки из другого столбца. Если эта функция вызывается без аргументов, она возвращает номер строки текущей ячейки. При сочетании OFFSET и ROW можно создать функцию, которая возвращает любую n-ю ячейку, добавляя множитель к значению, возвращаемому ROW. Например, OFFSET(A$1,ROW()*3,0) . Обратите внимание на использование $1 в целевой ячейке. Он будет смещен относительно других ячеек, эффективно увеличивая множитель на 1.

Подход ADDRESS + INDIRECT

ADDRESS принимает два целочисленных входных сигнала и возвращает адрес/имя ячейки в виде строки. Например, ADDRESS(1,1) возвращает «$A$1». INDIRECT принимает адрес ячейки и возвращает ее содержимое. Например, INDIRECT(«A1») возвращает содержимое ячейки A1 (также принимает записи $). Если мы используем ROW внутри ADDRESS с множителем, мы можем получить адрес каждой n-ой ячейки. Например, ADDRESS(ROW(), 1) в строке 1 вернет «$A$1», строка 2 вернет «$A$1», строка 2 вернет «$A$2». строка 2 вернет «$A$2» и так далее. Таким образом, если мы поместим это в INDIRECT, мы сможем получить содержимое каждой n-ой ячейки. Например, INDIRECT(ADDRESS(1*Row()*3,1)) при перетаскивании вниз вернет содержимое каждой третьей ячейки в первом столбце.

Пример

Рассмотрим следующий скриншот таблицы. Заголовки (первая строка) содержат вызов, используемый в строках ниже. Столбец A содержит данные нашего примера. В этом случае это просто положительные целые числа (подсчет продолжается за пределами отображаемой области). Это те значения, которые мы хотим получить каждый третий, то есть мы хотим получить 1, 4, 7, 10 и т.д.

Мы забыли использовать $, когда использовали подход OFFSET в столбце B. Как вы можете видеть, умножая на 3, мы получаем каждую четвертую строчку.

Колонка C содержит неправильную попытку использовать подход OFFSET, когда мы помним, что нужно использовать $, но забываем вычесть. Таким образом, хотя мы получаем каждое третье значение, мы пропускаем некоторые (1 и 4).

Столбец D содержит правильную функцию, используя подход OFFSET.

Колонка E содержит неправильную попытку использовать подход DIRECT + INDRECT, но мы забыли вычесть. В результате мы сначала пропустили некоторые ряды. Та же проблема, что и в колонке C.

Метод ADDRESS + INDRECT описывает правильную функцию в колонке F.

Трюк №69. Как в Excel 2010 просуммировать каждую вторую, третью или n-ую строку или ячейку

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

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

Функция СТРОКА (ROW) возвращает номер строки для заданной ссылки на ячейку: ROW(reference), в русской версии Excel СТРОКА(ссылка).
Функция ОСТАТ (MOD) возвращает остаток от деления числа на делитель: MOD(number;divisor), в русской версии Excel ОСТАТ(число;делитель).

Поместите функцию ROW в функцию MOD (для передачи числового аргумента), разделите на 2 (для добавления каждой второй ячейки) и проверьте, равен ли результат нулю. Если да, то ячейка добавляется. Эти функции можно использовать по-разному, причем некоторые из них дают лучшие результаты, чем другие. Например, табличная формула для сложения всех остальных ячеек в диапазоне $A$1:$A$100 будет выглядеть так: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$A$1:$A$500;0)) В русском Excel =SUM(IF(ROW($A$1:$A$500);2)=0;$A$1:$A$500;0)) .

Вот еще одна формула, которая представляет собой немного лучший вариант: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) В русском Excel =SUMMPRODUCT((ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) .

Однако обратите внимание, что эта формула вернет #Значение! (#Значение!), если какие-либо ячейки в диапазоне содержат текст вместо чисел. Эта формула, хотя и не является табличной формулой, также замедляет работу Excel, если используется слишком часто или если каждый раз ссылается на большой диапазон.

Хорошая новость заключается в том, что существует более эффективная и гибкая альтернатива. Она требует использования функции DSUM. В этом примере мы использовали диапазон A1:A500 в качестве диапазона, в котором необходимо просуммировать каждую n-ую ячейку.

В ячейку Е1 введите слово Criteria. В ячейку Е2 введите следующую формулу: =MOD(ROW(A2)-$C$2-1;$C$2)=0 , в русской версии Excel =ОСТАТ(СТРОКА(А2)-$С$2-1;$С$2)=0 . Выделите ячейку С2 и выберите команду Данные → Проверка (Data → Validation).

В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. В ячейке С1 введите текст SUM every…. В любой ячейке, кроме строки 1, введите следующую формулу: =DSUM($A:$A;1;$E$1:$E$2) , в русской версии Excel =БДСУММ($А:$А;1;$Е$1:$Е$2) .

В ячейке непосредственно над той, где вы ввели функцию БДСУММ (DSUM), введите текст =»Summing Every» & $С$2 & CHOOSE($C$2;»st»;»nd»;»rd»;»th»;»th»;»th»;»th»;»th»;»th»;»th») & «Cell» . Теперь осталось только выбрать нужное число в ячейке С2, а остальное сделает функция БДСУММ (DSUM).

D SUM суммирует данные за интервал, указанный пользователем. Функция DSUM намного эффективнее, чем формула массива или функция SUMPRODUCT. Хотя на ее настройку требуется некоторое время, она является примером того, как тяжелые тренировки становятся легкими в бою.