Цикл в сделать в экселе

Обновлено: 07.07.2024

можно ли перебирать (цикл) группу строк в Excel без установленного VBA или макросов? Google не дал ничего полезного.

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

Я неплохо разбираюсь в PHP / SQL, но просто изучаю Excel.

путь получить результаты Вашей формулы был бы начать в новом листе.

в ячейку A1 поместите формулу

скопируйте эту ячейку в строку 40 В ячейку B1 поместите формулу

в ячейку B2 поместите формулу

скопируйте эту ячейку в строку 40.

значение, которое вы хотите сейчас в этом столбце в строке 40.

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

Я просто искал что-то похожее:

Я хочу суммировать каждый нечетный столбец строки.

SUMIF имеет два возможных диапазона, диапазон до сумма и в рассмотреть критерии in.

эта функция рассмотрит, является ли ячейка в диапазоне B "=1", она будет суммировать соответствующую ячейку, только если она есть.

чтобы получить" =1", чтобы вернуться в диапазон B, Я поместил это в B:

затем автоматическое заполнение, чтобы получить модуль для заполнения, вы можете поместить и вычисляемые критерии здесь, чтобы получить условия SUMIF или SUMIFS, которые вам нужно зациклить через каждую ячейку.

проще, чем материал массива и скрывает заднюю часть циклов!

собираюсь отвечать на это сам (поправьте меня, если я ошибаюсь):

невозможно перебирать группы строк (например, массив) в Excel без VBA установлен / макросы включены.

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

Агрегируйте результаты из этой таблицы в ячейку результатов.

200 ячеек so, которые ссылаются на результаты, могут ссылаться на ячейку, содержащую результаты агрегации. В новейших версиях excel вы можете назвать ячейку результата и ссылаться на нее таким образом, для удобства чтения.

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

создание одного цикла в Excel довольно просто. Это на самом деле делает работу за вас. Попробуйте это в новой книге

A3 автоматически будет "=A2+1 " при перетаскивании вниз. Первые шаги не это должно быть ясно. Excel автоматически распознает шаблон и подсчет, если вы просто поместите "2" в A2, но если мы хотим, чтобы B1-B5 был "100", а B5-B10 - "200" (подсчет таким же образом), вы можете понять, почему знание того, как это сделать, явно имеет значение. В этом сценарии вы просто вводите:

" 100 " в B1, перетащите до B5 и
"=B1+100 " в B6

B7 автоматически будет "=B2+100 " etc. как вы перетащите вниз, так что в основном он увеличивается каждые 5 ряды бесконечны. Чтобы сделать цикл чисел 1-5 в столбце A:

введите "=A1 " в ячейку A6. При перетаскивании вниз он автоматически будет "=A2"в ячейке A7 и т. д. потому что Excel делает вещи.

Итак, теперь у нас есть столбец a, повторяющий числа 1-5, а столбец B увеличивается на 100 каждые 5 ячеек.Вы можете заставить столбец B повторить, например, числа 100-900, используя тот же метод, что и для столбца A, например, каждый возможна комбинация с несколькими переменными. Перетащите вниз колонны, и они будут делать это бесконечно. Я явно не рассматриваю данный сценарий, но если вы выполните шаги и поймете их, концепция должна дать вам ответ на проблему, которая включает добавление дополнительных столбцов и конкакцинацию или использование их в качестве переменных.

@Nat дал хороший ответ. Но поскольку нет способа сократить код, почему бы не использовать contatenate для "генерации" кода, который вам нужен. Он работает для меня, когда я ленив (при вводе всего кода в ячейке).

Итак, нам нужно просто определить шаблон > использовать excel для создания шаблона "структура" > добавить " ") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"") &IF('testsheet'!$C <= 99,'testsheet'!$A,"")

Я не печатал его, я просто использую символ " & " для объединения упорядоченной ячейки в excel (другой файл нет файла, над которым мы работаем).

обратите внимание, что :

часть 1> IF('testsheet'!$C$

часть 2> 1 to 40

часть 3> <= 99,'testsheet'!$A$

введите часть1 А1, часть3 С1, участие в Е1.
введите "= A1 "в A2," = C1 "в C2," = E1 " в E2.
введите "= B1+1 "в B2," = D1+1 " в Д2.
введите "=A2&B2&C2&D2&E2 " в G2
введите "=I1&G2 " в I2

Теперь выберите A2:I2 и перетащите его вниз. Обратите внимание, что число increament каждой строки добавил, и сгенерированный текст совмещен, клетку за клеткой и построчно.

копировать содержимое I41,
вставьте его где-нибудь, добавьте " postinfo clearfix">

Циклы в VBA Excel предназначены для повторения одного и того же блока операторов. Количество итераций может быть задано явно, зависеть от условия или количества элементов, содержащихся в обрабатываемом объекте.

Цикл For… Next

Цикл For… Next в VBA Excel предназначен для выполнения группы операторов необходимое количество раз, заданное счетчиком — специальной переменной цикла. После выполнения каждой итерации значение счетчика увеличивается или уменьшается на единицу или на число, указанное с помощью оператора Step. Предусмотрен принудительный выход из цикла с помощью оператора Exit For. Перейти к подробному описанию …

Цикл Do While… Loop

Цикл Do While… Loop в VBA Excel предназначен для повторения блока операторов до тех пор, пока выполняется заданное условие (возвращается значение True). Этот цикл позволяет проверять условие как до, так и после выполнения операторов. Предусмотрен принудительный выход из цикла с помощью оператора Exit Do. Перейти к подробному описанию …

Цикл While… Wend

Цикл While… Wend в VBA Excel предназначен для выполнения блока операторов до тех пор, пока выполняется заданное условие (возвращается значение True). Этот цикл позволяет проверять условие только до выполнения операторов. Принудительный выход из цикла с помощью оператора Exit Do не предусмотрен. Перейти к подробному описанию …

Цикл Do Until… Loop

Цикл Do Until… Loop в VBA Excel предназначен для повторения блока операторов пока не выполняется заданное условие (возвращается значение False). Этот цикл позволяет проверять условие как до, так и после выполнения операторов. Предусмотрен принудительный выход из цикла с помощью оператора Exit Do. Перейти к подробному описанию …

Цикл For Each… Next

Цикл For Each… Next в VBA Excel предназначен для выполнения блока операторов по отношению к каждому элементу из группы элементов (диапазон, массив, коллекция). Предусмотрен принудительный выход из цикла с помощью оператора Exit For. Перейти к подробному описанию …

При выполнении VBA программы (VBA, Visual Basic for Applications) может потребоваться повторить какой-то фрагмент кода несколько раз. Для выполнения этой задачи существуют циклы.

Синтаксис языка Visual Basic предусматривает следующие их типы:

Давайте проанализируем отличия между ними во всех подробностях.

Цикл типа For

For … Next

Цикл For … Next опирается на переменную, которая увеличивается по окончании каждой итерации в определенном диапазоне. И каждый раз происходит выполнение определенного кода. Проще всего изобразить принцип работы цикла этого типа на легком фрагменте кода.

For i = 1 To 10

Total = Total + iArray(i)

В приведенном варианте кода в рамках цикла определяется переменная, увеличивающая содержащееся в ней число на единицу после каждого выполнения фрагмента кода (это и называется итерацией).

Здесь не указан размер шага, поэтому циклом используется стандартный размер 1. Выполняется такое количество итераций, которое позволяет сделать так, чтобы значение прошло от 1 до 10. Но если потребуется использовать разные размеры шагов, необходимо использовать слово Step. Приведем пример для наглядности.

For d = 0 To 10 Step 0.1

dTotal = dTotal + d

В описанном варианте цикла определенный размер шага – 0,1, поэтому значение переменной d изменяется по числовому ряду 0.0, 0.1, 0,2 и так далее, пока значение переменной не достигнет 10.

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

For i = 10 To 1 Step -1

Цикл For Each

Цикл For Each аналогичен For Next , за исключением того, что последний проходит по числовому ряду значений, а For Each осуществляет требуемую последовательность действий для всех объектов, принадлежащих к заданной группе. Например, следующий код показывает, как пользователь мог бы применить цикл For Each.

Dim wSheet As Worksheet

For Each wSheet in Worksheets

Next wSheet

Этот код будет выполняться для каждого листа, входящего в состав документа, над которым работает человек.

Конструкция Exit For

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

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

Рассмотрим еще один пример, который хорошо отображает работу оператора Exit For . Здесь цикл анализирует 100 объектов, интерпретатор сравнивает каждый из них со значением переменной dValue1s. Если оно равно dVal1, то цикл завершается.

For i = 1 To 100

If dValues1(i) = dVal1 Then

indexVal = i

Цикл Do While

Характерная особенность цикла Do While – выполнение содержащегося в нем кода осуществляется до того момента, пока истинно определенное условие. Это хорошо демонстрирует следующий пример функции Sub, где этот цикл используется, чтобы напечатать числа, входящие в последовательность Фибоначчи, пока число в переменной больше 1000.

Sub Fibonacci()

iFib_Next = 0

Do While iFib_Next < 1000

If i = 1 Then

iStep = iFib

iFib = iFib_Next

Cells(i, 1).Value = iFib

iFib_Next = iFib + iStep

В этом фрагменте видно, что на старте прописано условие iFib_Next < 1000 . Если iFib_Next становится больше 1000, то цикл автоматически завершается.

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

Loop While iFib_Next < 1000

Цикл Do Until

Этот тип напоминает Do While , но здесь код выполняется до тех пор, когда условие не становится правдивым. То есть, в прошлом варианте изначально условие истинное, пока в ходе выполнения действий оно не станет ложным. Здесь – наоборот. Это хорошо демонстрирует такой пример:

Do Until IsEmpty(Cells(iRow, 1))

dCellValues(iRow) = Cells(iRow, 1).Value

iRow = iRow + 1

Эта подпрограмма извлекает значения ячеек, входящих в одну колонку, пока не обнаружится пустая.

Точно так же, как в предыдущем примере, если условие IsEmpty(Cells(iRow, 1)) ставится в начале цикла, он будет запущен, лишь если есть заполненная ячейка.

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

Loop Until IsEmpty(Cells(iRow, 1))

Циклы – это эффективный инструмент, позволяющий автоматизировать повторяющиеся действия. Он может использоваться для программирования самых разнообразных задач. Это значительно облегчает работу с большими объемами данных.

Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить эту проблему, можно переместить формулу в другую ячейку. Нажмите CTRL+X, чтобы вырезать формулу, выберите другую ячейку и нажмите CTRL+V, чтобы в нее в нее врезаться.

Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:

Вы также можете попробовать один из описанных ниже способов.

Если вы только что ввели формулу, начните с этой ячейки и проверьте, есть ли в ней ссылка. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также приводят к ошибкам цикловой ссылки.

Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.

Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.

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

Вы можете перемещаться между ячейками в цикле, дважды щелкнув стрелку зависимости. Стрелка указывает на ячейку, которая влияет на значение выбранной ячейки. Чтобы отследить стрелку трассировки, щелкните Формулыи выберите Зависимые зависимые от нее зависимые зависимые от нее зависимые.

Предупреждение о циклической ссылке

Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:

Щелкните формулу в строке формулы и нажмите клавишу ВВОД.

Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.

Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.

Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.

Пользователь открывает книгу, содержащую циклическую ссылку.

При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.

Итеративные вычисления

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

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

Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.

Если вы используете Excel 2007, нажмите кнопку Microsoft Office нажмите кнопку Параметры Excel ивыберите категорию Формулы.

В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.

В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.

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

Итеративное вычисление может иметь три исход:

Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.

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

Решение переключается между двумя значениями. Например, после первой итерации результат будет 1, после следующей — 10, после следующей — 1 и так далее.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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