Как пропустить строку в Excel
Обновлено: 21.11.2024
Как правильно сместить относительную формулу, чтобы пропустить строки
Я пытаюсь скопировать относительную формулу с увеличением по шаблону.
Мне нужна формула для копирования из ячейки листа 1 (RRP Clean) C100 в ячейку листа 2 (POS) A24. Затем мне нужно скопировать относительную формулу листа 2 вниз на 14 строк, в то время как относительная формула увеличивается только на 1 ячейку, т.е. копирование ячейки A38 листа 2 в ячейку C101 листа 1 (вместо ячейки C114) и т. д. для A52 и т. д.
Кто-нибудь знает, как это сделать. Я пытался использовать OFFSET, но не могу заставить его работать.
Заранее спасибо и приносим извинения за плохое объяснение. Надеюсь, фотографии помогут объяснить.
Гуру форума Дата регистрации 31 декабря 2011 г. Местоположение Уоррингтон, Англия MS-Off Ver Office 2019 (все еще учится) Сообщений 22 353
Вместо прикрепления изображений рабочего листа, который нельзя редактировать, было бы лучше, если бы вы прикрепили образец рабочей книги Excel.
Для этого нажмите «Дополнительно» (под окном «Редактировать») во время написания ответа, затем прокрутите вниз и нажмите «Управление вложениями», после чего откроется окно «Загрузить». Нажмите «Обзор» и перейдите (и дважды щелкните) к значку файла, который вы хотите прикрепить, затем нажмите «Загрузить», а затем «Закрыть это окно», чтобы вернуться в окно «Редактировать». Когда вы закончите создание сообщения, нажмите «Отправить сообщение». Не пытайтесь использовать значок скрепки, так как он не работает на этом форуме.
Надеюсь, это поможет.
Forum Expert Дата регистрации 27.11.2007 Местоположение Нью-Джерси, США MS-Off Ver 2013 Сообщений 1676
см. вложение.
Лист2 В ячейке C101 есть формула. Скопируйте его. Вы можете создать нечто подобное в соответствии со своими потребностями.
2. Отметьте свою тему как РЕШЕННУЮ, когда вопрос будет решен
Спасибо. Единственная проблема с этим - это обратное решение того, что мне нужно. как преобразовать это, чтобы лист1 имел последующие цифры, но лист2, содержащий формулу, пропускает 14 строк. Использовать отрицательное уравнение?
Forum Expert Дата регистрации 27.11.2007 Местоположение Нью-Джерси, США MS-Off Ver 2013 Сообщений 1676
Это сбивает с толку.
где ваши данные [источник]? и куда вы хотите получить результат [назначение]?.
По совету Пита, пожалуйста, прикрепите образец файла с данными, введите ожидаемый результат [вручную] и четко объясните логику и то, как вы хотите получить результат.
Я загрузил пример таблицы, чтобы показать, что я имею в виду.
На Листе1 у меня есть сетка цифр.
На Sheet2 у меня есть повторяющийся шаблон текста с цифрами каждые 14 строк.
Мне не нужно копировать 14 строк текста по вертикали и импортировать следующую цифру вниз с листа1 в числовые пробелы на листе2.
Али
Увлеченный самоучка, пользователь MS Excel, который постоянно учится!
Правила форума (обновлены в сентябре 2018 г.): ознакомьтесь с ними здесь.
Как использовать полученный код Power Query: помощь здесь. Подробнее о Power Suite здесь.
Не забудьте сказать "спасибо" тем, кто помог вам в вашей теме. Если хотите, вы также можете вознаградить их, нажав на звезду их репутации внизу слева.
Первоначальное сообщение от AliGW
Некоторые части текста будут введены вручную, а некоторые импортированы из других частей Листа1, но для этой формулы их можно игнорировать. Вставьте его только для того, чтобы показать, что пробелы между цифрами нельзя сделать пустыми.
Доволен своим ответом * Добавить репутацию.
Если вас устраивает решение, воспользуйтесь инструментами для создания тем и отметьте тему как РЕШЕННУЮ.
Спасибо. Я пытался импортировать это, но не могу заставить его работать, нужно ли мне изменять эту формулу после ее копирования?
Первоначальное сообщение от AliGW
Это не выполнение формулы, а просто ввод формулы в виде текста. в клетку. Я пытался изменить части формулы, чтобы они соотносились с конкретными ячейками и т. д., но я действительно не уверен в том, на что смотрю.
Первоначальное сообщение от AliGW
Первоначальное сообщение от KOKOSEK
Кстати, я действительно не верю, что с помощью формулы можно делать то, что вы хотите. Я бы посоветовал вам использовать решение VBA.
Это работает, пока я копирую все 14 строк одновременно. Кажется, мне осталось только решить, как заставить формулу начинаться с правильной ячейки.
С какой ячейки вы хотите начать?
Пожалуйста, не цитируйте целые сообщения, особенно когда вы отвечаете на сообщение, непосредственно предшествующее вашему собственному — это просто беспорядок.Можно цитировать, если вы отвечаете на сообщение не по порядку, но ограничьте цитируемый контент несколькими соответствующими строками, чтобы было понятно, кому и на что вы отвечаете. Спасибо!
Для обычных диалоговых ответов попробуйте использовать поле БЫСТРЫЙ ОТВЕТ ниже.
Чтобы копировать значения или генерировать ссылки с шаблоном, таким как каждая 3-я строка, каждая 7-я строка и т. д., вы можете использовать формулу, основанную на функциях OFFSET и ROW. В показанном примере формула в D5 выглядит следующим образом:
Который копирует значения из каждой 3-й строки в столбце B по мере копирования формулы.
В этом примере цель состоит в том, чтобы скопировать каждое n-е значение из столбца B, где n – это переменная, которую можно изменить по мере необходимости.
В Excel сложно создавать формулы, которые пропускают строки в соответствии с определенным шаблоном, потому что ссылки в формуле будут автоматически изменяться, когда формула копируется с шагом 1 в ячейках. Однако, немного поработав, можно создать ссылки на формулы, соответствующие определенным шаблонам. В этом примере мы используем функцию СМЕЩ, предназначенную для создания ссылок на другие ячейки или диапазоны ячеек на основе заданной начальной точки или источника.
Начиная с n
В показанном примере формула в D5, скопированная вниз, выглядит следующим образом:
которое копирует каждое третье значение из диапазона B5:B59, начиная с B7, которая является третьей ячейкой в диапазоне. Отправной точкой внутри функции OFFSET является аргумент reference, представленный как абсолютная ссылка:
Ссылка на B5 заблокирована, поэтому она не изменится при копировании формулы. Следующий аргумент — rows, который указывает желаемое смещение строки от начальной ссылки. Вместо типичного жестко заданного числа строки представлены в виде выражения, которое вычисляет требуемое смещение:
Здесь n указывается равным 3, чтобы копировать каждое третье значение. Здесь функция ROW используется для получения номера строки для ячейки D1. Мы начинаем с D1, потому что мы хотим начать с 1 для первого значения. Поскольку формула копируется вниз по столбцу, значение, возвращаемое ROW, увеличивается на 1, поскольку ссылка на D1 является относительной. Этот результат из ROW умножается на n, что создает шаблон «каждый n-й», в данном случае «каждый 3-й». По мере копирования формулы выражение оценивается следующим образом:
Эти числа могут показаться вам странными в контексте "каждого третьего значения", но помните, что это смещение, начиная с ячейки B5. Причина, по которой мы вычитаем 1, заключается в том, что функция OFFSET не включает ссылочную ячейку, когда применяется аргумент rows. Другими словами, смещение на одну строку от A1 возвращает A2:
Вычитание 1 учитывает это поведение.
Наконец, аргумент столбца предоставляется равным нулю (0), так как нам не нужно смещение столбца; мы хотим остаться в столбце B. Когда формула копируется вниз, она возвращает необходимые ссылки:
Число n можно изменить по мере необходимости. Например, если n изменить на 5, формула будет использовать каждое пятое значение.
Начиная с 1
Чтобы начать копирование с первой строки в заданном диапазоне, а затем следовать каждому n-му шаблону, вы можете изменить формулу следующим образом:
В этой версии мы вычитаем 1 непосредственно из результата функции ROW. Это «обнуляет» первый экземпляр строк, так что OFFSET возвращает ссылку на текущую ячейку. Формула в ячейке F5 использует следующий подход:
Я пытаюсь вставить данные с листа 1 на лист 2, но на листе 2 пропущено 5 ячеек. Мне нужно перетащить формулу вниз, чтобы:
Лист 1, ячейка B2 = лист 2, ячейка A2
Лист 1, ячейка B3 = лист 2, ячейка A7
Лист 1, ячейка B4 = лист 2, ячейка A12
И так вперед.
Затем в столбце B на листе 2 мне нужно перетащить формулу вниз, чтобы:
Лист 1, ячейка C2 = лист 2, ячейка B2
Лист 1, ячейка C3 = лист 2, ячейка B7
Лист 1, ячейка C4 = лист 2, ячейка B12
И так далее.
Я пытался использовать формулы, найденные в других сообщениях на форуме, но безрезультатно. Мы будем очень признательны за любую помощь!
Факты об Excel
Диспетчер имен, новое имя. Вчера =СЕГОДНЯ()-1. OK. Затем используйте =YESTERDAY в любой ячейке. Завтра может быть =СЕГОДНЯ()+1.
Эрик В
MrExcel MVP
Добро пожаловать на форум.
скопируйте это на C2 и скопируйте оба столбца вниз, насколько это необходимо.
MrExcel MVP, младший администратор
Добро пожаловать в Совет!
Поместите эту формулу в ячейку A2 на листе 2 и скопируйте вниз:
Крелия
Новый участник
MrExcel MVP, младший администратор
Крелия
Новый участник
Тот, который вы опубликовали (я не смог заставить работать первый опубликованный)
Однако я столкнулся с одной проблемой.На новом листе мне нужно продолжить формулу, чтобы закончить с того места, где я оставил на листе 2. Например, на листе 3 мне нужно:
Лист 1, ячейка A22 = лист 3, ячейка A2
Лист 1, ячейка A23 = лист 3, ячейка A7
Лист 1, ячейка A24 = лист 3, ячейка A12
В настоящее время, когда я ввожу формулу, она снова начинается с A2.
Эрик В
MrExcel MVP
MrExcel MVP, младший администратор
Однако я столкнулся с одной проблемой. На новом листе мне нужно продолжить формулу, чтобы закончить с того места, где я оставил на листе 2. Например, на листе 3 мне нужно:
Лист 1, ячейка A22 = лист 3, ячейка A2
Лист 1, ячейка A23 = лист 3, ячейка A7
Лист 1, ячейка A24 = лист 3, ячейка A12
В настоящее время, когда я ввожу формулу, она снова начинается с A2.
Итак, мы хотим, чтобы формула отображалась в строке с любым номером, так что при делении строки на 5 остаток равен 2. Таким образом, эта формула идентификатора проверяет, равен ли остаток двум. Если это так, он оценивает внутреннюю часть. Если это не так, он ничего не возвращает ("").
Это позаботится о пустых строках.
Теперь, всякий раз, когда мы хотим динамически создать ссылку на диапазон в Excel, мы должны использовать функцию ДВССЫЛ, чтобы Excel знал, что мы говорим о ссылке на диапазон, а не о буквальной текстовой строке. Таким образом, если мы хотим вернуть значение из Sheet1, ячейка B2, если мы запишем эту ссылку динамически, это будет выглядеть так:
=Sheet1!B2
Однако мы хотим построить формулу для возврата этого на основе на номер строки, в которой находится формула. Итак, нам нужно придумать эту формулу. Мы знаем, что часть «Sheet1!B» не изменится, изменится только номер строки. Вот тут-то и появляется эта часть:
(ROW()+8)/5
Поскольку на каждые 5 строк, которые мы опускаем, мы хотим, чтобы номер нашей строки увеличивался на 1, мы делим на 5. < br />(Если бы у нас была ситуация, когда для каждой строки, которую мы опускаем, мы хотим, чтобы наша ссылка на номер строки увеличивалась на 5, мы бы умножали на 5.
Таким образом, мы всегда будем умножать / делить на количество строк, которые мы "прыгаем".
Последняя часть, +8, — это просто число, которое вы подставляете, чтобы заставить его работать, и оно зависит от того, с какой строки вы начинаете. Так что вы можете вернуться назад. в этот номер.
Итак, когда мы подставляем 2 для ROW(), мы хотим вернуть 2.
Когда мы подставляем 3 для ROW(), мы хотим вернуть 3.
Когда мы подставляем в 4 для ROW() мы хотим вернуть 4.
и т. д.
Попробуем первый вариант:
(ROW()+?)/5=2
(2+?)/5=2
2+?=10
?=8
Если мы подставим 8 к следующему, мы получим:
(7+8)/5
15/5
3
И далее:
(12+8)/5
20/5
4
Итак, мы видим, что это работает!
Итак, обычно я подхожу к этому так: иду к ячейке, в которую я хочу вернуть свое значение с помощью формулы, и сначала работаю над правильной частью строки:
=(ROW()+8) /5
Если у меня все работает правильно, когда я копирую его вниз по строкам, он должен возвращать номер строки, который мне нужен.
Затем мы просто строим вокруг этого.
Иногда разработчики моделей обнаруживают, что им приходится обращаться к каждой N-й строке. Здесь мы представляем простой и безошибочный способ сделать это. Автор Лиам Бастик, директор SumProduct Pty Ltd.
Запрос
Я создаю сводную таблицу, в которой мне нужно создать блок выходных данных, где каждая строка относится к позициям, отстоящим друг от друга на N строк. Есть ли простой способ сделать это быстро?
Совет
Кажется, сейчас мы зациклены на N-х предметах, но это распространенный вопрос. Например, представьте, что у вас есть примеры бизнес-данных, подобных приведенным ниже (взятым из прикрепленного файла Excel):
Пример бизнес-данных
Возможно, вы захотите создать выходные данные, в которых суммируется доход по бизнес-подразделениям. Вам нужно будет построить такие формулы, как = «Бизнес-данные»! G10, = «Бизнес-данные»! G25, = «Бизнес-данные»! G40, … и т. д.
Если бы у вас было, скажем, 500 таких бизнес-подразделений, вам бы предстояло напряженное, но скучное утро. Наверняка есть более простой способ, не требующий реализации макросов?
На самом деле, я могу придумать два способа справиться с этим распространенным запросом, и ниже я привожу оба решения.
Метод 1: мало времени на текстовые сообщения
Этот подход требует, чтобы первые две формулы были введены в выходной лист как обычно, а именно.
Ввод первых двух формул
В нашем примере ячейка B2 содержит формулу = «Бизнес-данные»! G10, а ячейка B3 содержит формулу = «Бизнес-данные»! G25 (отображается).
Затем отредактируйте обе формулы, введя апостроф (‘) перед знаком равенства в каждой формуле:
Добавление апострофов
Теперь эти формулы обрабатываются как текст и отображаются в двух ячейках. Если затем вы выделите ячейки B2:B3 вместе и скопируете формулы вниз, функция автоматического заполнения Excel (дополнительную информацию см. в разделе «Автозаполнение при перетаскивании?») скопирует ячейки, подобные приведенным ниже:
Автоматическое заполнение формул
Теперь все, что нам нужно сделать, это удалить апострофы. Первая идея, которая приходит на ум, — использовать «Заменить…» (CTRL + H) и заменить «= на =». К сожалению, это работает не во всех версиях Excel, так как функция «Заменить…» в некоторых случаях не распознает апострофы.
Есть очень простой способ обойти эту проблему. Выбрав эти данные, нажмите кнопку «Текст в столбцы» в группе «Инструменты данных» на вкладке «Данные» на ленте (ALT + D + E для всех версий Excel или ALT + A + E в Excel). 2007 г.):
Текст в столбцы
Откроется диалоговое окно «Мастер преобразования текста в столбцы». На первом этапе убедитесь, что для параметра «…тип файла, который лучше всего описывает ваши данные…» установлено значение «С разделителями»:
Диалоговое окно мастера преобразования текста в столбцы
Затем просто нажмите кнопку "Готово". Электронная таблица восстановит формулы, а именно.
Формулы восстановлены
Метод 2: СМЕЩЕНИЕ начала
Приведенный выше подход довольно прост, но имеет два основных недостатка:
- Этот метод работает только со строками. Используя нотацию формулы R1C1, можно создать аналогичный подход для столбцов, но этот метод может сбивать с толку.
- После того, как формулы были восстановлены, их не так просто расширить, если это необходимо. Это может быть обременительно, например, если выходные данные могут отличаться от периода к периоду. Подход OFFSET решает эти проблемы. Я обсуждал OFFSET ранее (подробности см. в разделе Начало OFFSET).
Синтаксис для OFFSET следующий:
Аргументы в квадратных скобках (высота и ширина) могут быть опущены в формуле. В своей самой простой форме OFFSET(Reference,x,y) выберет ссылку на x строк вниз (-x будет x строк вверх) и y столбцов вправо (-y будет y столбцов слева) от ссылки. . Например, рассмотрим следующую сетку:
СМЕЩ(A1,2,3) переместит нас на две строки вниз и на три столбца поперек к ячейке D3. Следовательно, OFFSET(A1,2,3) = 16, а именно.
СМЕЩ(D4,-1,-2) перенесет нас на одну строку вверх и на два столбца влево к ячейке B3. Следовательно, OFFSET(D4,-1,-2) = 14, а именно.
Вернемся к нашему примеру:
Новый пример бизнес-данных
Обратите внимание, что данные бизнес-подразделения разделены 15 строками (например, первый блок начинается в строке 8 и заканчивается в строке 22 с учетом пустых строк). Таким образом, мы можем создать одну формулу, которую можно скопировать:
Формулы СМЕЩЕНИЯ
В этом примере мы начали формулу в ячейке B2 и скопировали ее в ячейку B6. Формула в ячейке B2:
=OFFSET('Бизнес-данные'!$G$10,СТРОКИ('Бизнес-данные'!$C$8:$C$22)*(СТРОКИ($A$2:$A2)-1),).
Первая ссылка — это доход для бизнес-подразделения 1. Ссылка на строки берет глубину каждого блока (определяемую здесь как ROWS('Бизнес-данные'!$C$8:$C$22)) и умножая на ROWS($A$2). :$A2)-1, например в строке 2 этот коэффициент будет равен нулю, в строке 3 он будет равен 1, в строке 4 он будет равен 2 и т. д. Это гарантирует, что ссылка на следующую статью доходов будет находиться в следующей строке.
Сначала это может показаться сложным, но с практикой эту идею можно адаптировать для пропуска столбцов и вместо них выбирать другие позиции (например, валовую прибыль, налоги).
Слово мудрым
В прикрепленном файле Excel показано, как можно использовать проверку данных (дополнительную информацию см. в разделе Проверка данных) для создания более сложных сводных отчетов с использованием всего одной уникальной формулы:
Пример вывода
Читайте также: