Вопрос или проблема
Я создаю таблицу, которая ссылается на другую таблицу. Ссылка на таблицу – это блоки данных, которые повторяются каждые 25 строк.
В детали:
Я хочу, чтобы первая ячейка была: =IF(RFA!B40>0,RFA!B40,"")
в следующей строке вниз в том же столбце я установил её на =IF(RFA!B65>0,RFA!B65,"") (разница 25)
Я бы хотел повторить этот шаблон на 22 строки вниз. Когда я выбираю 1 поле и перетаскиваю вниз, оно идет вниз на одну (40, 41, 42 и т. д.)
Если я выберу 2 исходных поля с формулой и перетяну вниз, третья строка идет на 42, а четвертая на 67.
Что я хочу, чтобы это делало, таково:
=IF(Sheet1!A45="","",Sheet1!A45)
=IF(Sheet1!A60="","",Sheet1!A60)
=IF(Sheet1!A75="","",Sheet1!A85)
=IF(Sheet1!A90="","",Sheet1!A110)
…
Это возможно? Как я мог бы это сделать?
Если у вас есть доступ к WRAPROWS(), вы можете использовать следующую формулу, и не нужно копировать вниз, так как она будет заполнять весь массив:
=TAKE(WRAPROWS(RFA!A40:A90,15),,1)
Или более старые версии могут использовать:
=IFERROR(INDEX(RFA!A$40:A$90,COLUMN(A1)+(ROW(A1)-1)*15),"")
Используя INDEX и немного математики:
=IF(INDEX(Sheet1!A:A,(ROW($ZZ1)-1)*15+45)="","",INDEX(Sheet1!A:A,(ROW($ZZ1)-1)*15+45))
Затем, когда это перетаскивается вниз, множитель строки изменяется, и следующая ссылка находится на 15 строк ниже.
Ответ или решение
Использование формул в Excel для ссылок на каждую n-ю строку данных из другого листа может быть полезным, особенно когда данные организованы в блоки, как в вашем случае. Ваша задача заключается в том, чтобы осуществлять ссылки на данные, которые повторяются через каждые 25 строк, и сделать это так, чтобы при заполнении столбца формулы ссылки увеличивались корректно. Ниже я объясню, как можно достичь этого с помощью различных методов.
Решение с помощью функции INDEX
Один из наиболее эффективных способов обработки этой задачи заключается в использовании функции INDEX с арифметическими вычислениями. Формула может выглядеть следующим образом:
=IF(INDEX(Sheet1!A:A, (ROW($ZZ1)-1)*25+40)="", "", INDEX(Sheet1!A:A, (ROW($ZZ1)-1)*25+40))Пояснение работы формулы
- INDEX – эта функция позволяет получить значение из массива данных по указанной позиции.
- ROW($ZZ1) – эта функция возвращает номер строки для ячейки, в которой вы написали формулу. Когда вы ведете вниз, это значение будет увеличиваться на 1 на каждой строке.
- *(ROW($ZZ1)-1)25+40** – данное выражение позволяет вычислить адрес строки, которую вы хотите получить. Каждый раз, когда вы копируете формулу вниз, значение
ROW($ZZ1)увеличивается, что приводит к увеличению конечного адреса на 25 (то есть вы получаете 40, 65, 90 и так далее). - IF(…="", "", …) – эта часть позволяет избежать отображения нуля или ошибки в случае, если ячейка с данными пуста.
Заполнение формулы на несколько строк
После того как вы введете формулу в первую ячейку вашего столбца, вы можете просто перетащить или двойным кликом на углу ячейки распространить её вниз на остальные ячейки. Это обеспечит последовательные ссылки на каждые 25 строк, начиная с указанной начальной строки (в вашем случае строки 40).
Альтернативный способ: Использование функции IFERROR и INDEX для старых версий Excel
Если у вас более старая версия Excel, где нет доступа к новым функциям, вы можете использовать аналогичный подход с помощью более старых функций:
=IFERROR(INDEX(RFA!A$40:A$90, COLUMN(A1) + (ROW(A1)-1)*25), "")Заключение
Эти методы позволяют экономить время, избегая ручного ввода данных для каждой строки. Используя формулы, вы сможете легко управлять огромными массивами данных. Не забудьте проверить вашу формулу и протестировать её на небольшом наборе данных, прежде чем переносить её на полные блоки. Таким образом, вы избежите возможных ошибок и упростите процесс обработки данных в Excel.
Выберите подходящий вам метод, исходя из доступных функций в вашей версии Excel, и применяйте его для оптимизации работы с данными!