Как сделать цикл в excel

Как сделать цикл в excel?

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

таблицей БЕЗ макросов и дополнительных модулей (функций пользователя).

Стандартный набор функций Excel не поддерживает ничего похожего на циклы, однако выход нашелся – использование циклических ссылок и итераций.

Первый шаг, чтобы начать работать с циклическими ссылками – это разрешить итеративные вычисления в Excel. В версии Microsoft Office XP это делается в разделе меню «Сервис – Параметры»:

В открывшемся окне активируем соответствующий флажок:

Так же обратите внимание на поле «Предельное число итераций» — оно определяет, сколько раз будет проходить вычисление по всем циклическим ссылкам, если вы сами не поставите ограничение.

!Информация: В Excel 2007 включение итеративных вычислений производится в пункте меню «Файл – Параметры Excel» в разделе «Формулы».

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

Впишите в ячейку А1 формулу:

Если значение предельного числа итераций равно 100, то результатом вычислений в ячейке А1 будет равно 100. Теперь изменим формулу:

Как видите число итераций мы ограничили числом в ячейке А2, а именно 10. Чтобы проиллюстрировать, как происходят итерации, добавим формулу в ячейку B2:

Как видите, каждый этап итерации (новое значение А1) добавляется к ячейке B1 до тех пор пока происходят итерации.

!Замечание: Возможно, результат в B2 будет не таким – для того, чтобы исправить это сделайте следующее: после ввода формулы в ячейку B1 перейдите в ячейку A1, установите курсор в поле редактирования формулы и нажмите Enter – произойдет пересчет итераций. Для корректной работы итераций необходимо задать ряд дополнительных условий, ограничивающих их количество и задающее условие сброса текущего значения поля в исходное значение. Пересчет всех ячеек с циклическими ссылками происходит каждый раз при изменении ячеек, от которых они зависят.

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

К циклам VBA относятся:

  • Цикл For
  • Цикл Do While
  • Цикл Do Until

Далее мы подробно рассмотрим каждый из этих циклов.

Оператор цикла «For» в Visual Basic

Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each.

Цикл «For … Next»

Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:

В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.

В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение . Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.

Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.

Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:

Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.

Цикл «For Each»

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

Оператор прерывания цикла «Exit For»

Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.

Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:

Цикл «Do While» в Visual Basic

Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:

В приведённом примере условие iFib_Next Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Добрый день!
Неделю пытаюсь разобраться с макросами в Excel — выходит признаюсь честно плохо…
Решение задачи так и не нашла. Надеюсь вы сумеете мне подсказать куда конкретнее направить свою активность.

Задача передо мной стоит такая:

ячейка A1 содержит значение, которое я с помощью формул разбиваю на диапазон ячеек (задействовано 276 ячеек) целыми числами. Но при разбивке образуется дельта округления. Так вот мне необходимо эту дельту целыми числами доразнести в уже заполненные формулами ячейки например по порядку.
Например если дельта = 117, а диапазон 276, то в первые 117 ячеек прибавляем по 1, а остальные не трогаем.

Подскажите, пожалуйста, возможно ли вообще это описать для Excel?

Источник:
http://word-office.ru/kak-sdelat-cikl-v-excel.html

Как выполнить цикл в excel без VBA или макросов?

можно ли перебирать (цикл) группу строк в 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 довольно просто. Это на самом деле делает работу за вас. Попробуйте это в новой книге

  1. введите » 1 » в A1
  2. введите «=A1+1 » в A2

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

  1. » 100 » в B1, перетащите до B5 и
  2. «=B1+100 » в B6

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

  1. введите «=A1 » в ячейку A6. При перетаскивании вниз он автоматически будет «=A2″в ячейке A7 и т. д. потому что Excel делает вещи.
Читайте также  Поиск или замена текста и чисел на листе - Служба поддержки Office

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

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

Итак, нам нужно просто определить шаблон > использовать excel для создания шаблона «структура» > добавить » = » и вставить его в предполагаемую ячейку.

например, вы хотите достичь (я имею в виду, введите в ячейку) :

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

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

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

часть 2> 1 to 40

  • введите часть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,
  • вставьте его где-нибудь, добавьте » = » спереди, удалите дополнительные & и заднюю часть.

Result = код, как вы намеревались.

Я использую excel / OpenOfficeCalc, чтобы помочь мне создать код для моих проектов. Работает на меня, надеюсь, это поможет другим. (:

Источник:
http://askdev.ru/q/kak-vypolnit-cikl-v-excel-bez-vba-ili-makrosov-86775/

Циклы в VBA

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

К циклам VBA относятся:

Далее мы подробно рассмотрим каждый из этих циклов.

Оператор цикла «For» в Visual Basic

Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each.

Цикл «For … Next»

Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:

В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.

В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение 1. Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.

Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.

Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:

Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.

Цикл «For Each»

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

Оператор прерывания цикла «Exit For»

Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.

Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:

Цикл «Do While» в Visual Basic

Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:

В приведённом примере условие iFib_Next

Источник:
http://office-guru.ru/excel/cikly-v-vba-463.html

Excelling at Excel вып.2: Циклы в Excel без VBA

Немного теории. Циклом называется конструкция, которая некоторое (определяемое) количество раз выполняет заданные действия. Например, Вам нужно перебрать некий массив данных и выделить в нем пустые поля. В программировании это реализуется при помощи циклов. В VBA наиболее частым вариантом является конструкция For i = 0 to n … Next i.

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

Также отдельно имелись сметы по каждому такому проекту с детализацией статей затрат и с указанием исполнителя по каждой из статей с указанием доли участия. По каждой из статей могло быть до 4 исполнителей.

Задача: свести это в одну таблицу для последующей обработки через ту же сводную таблицу. То есть требовалось получить вот такое представление:

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

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

В нашем примере получалось три цикла (в порядке от младшего к старшему): тип исполнителя (цикл 1), статья затрат (цикл 2), проект (цикл 3). Алгоритм выглядит примерно так:

Цикл 1 (тип исполнителя)

Так бы примерно выглядела бы и структура кода VBA для реализации этих трех циклов, но в самом Excel так сделать нельзя. Что же делать?

Давайте еще раз обратимся к сути цикла: это повторение какого либо действия определенное количество раз. Теперь рассмотрим это на примере одного цикла – цикла 1 (тип исполнителя).

Допустим, у нас 4 возможных типа исполнителя. Они у нас на отдельном листе «Тип исполнителя». Соответственно, нам надо перебрать все эти четыре значения по одному. Как? Во-первых, мы должны определить, что их именно 4. Для этого воспользуемся функцией COUNTA (СЧЁТА).

ВАЖНО! Не забудем вычесть заголовок.

Во-вторых, нам надо оформить перебор значений от 1 до 4. Вернее, до значения полученного из COUNTA (СЧЁТА). Это именно столько «шагов» должен сделать наш цикл.

Увы, без вспомогательных столбцов здесь не обойтись. Добавляем их слева от результирующей таблицы и в первой строке в ячейке А2 смело ставим 1. В ячейке А3 и ниже мы пропишем следующую формулу:

Получаем бесконечное повторение от 1 до 4. Теперь нам остается получить значение на каждому «шагу» цикла. Это можно сделать при помощи функции OFFSET (СМЕЩ), в которой значения столбца А мы будем использовать в качестве второго параметра (смещение по строкам).

Теперь добавим второй цикл – цикл 2 (статья). Подход такой же за исключением одного «НО»: переключать значение мы будем не сразу после предыдущего как в цикле 1, а по достижении максимального значения в цикле 1 (тип исполнителя). Для этого нам нужно формула, описывающая такую логику:

«Если значение типа исполнителя равно количество типов, то

если предыдущее значение статьи равно количеству статей, то 1,

если не равно, то предыдущее значение + 1,

если не равно, то предыдущее значение».

Вот так это выглядит в экселе:

Цикл 3 (проект) оформляется схожим образом с циклом 2 (статья). Но «триггером» для переключения на новое значение будет уже два условия одновременно: максимальное значение количества статей и максимальное количество типов исполнителей. В формуле выполнение этих двух условий мы оформим через функцию AND (И) равную TRUE (ИСТИНА).

Осталось только добавить формулы СМЕЩ в ячейки с данными.

Необходимо не забыть «остановить» цикл. В противном случае вы получите то, что ниже:

Чтобы этого избежать в формуле в столбце А мы специально вставили в одном из возможных исходов значение «» (пусто), чтобы этим самым «остановить» бесконечный цикл. Теперь при протягивании формулы будут выводиться пустые ячейки. В формул остальных ячеек (в т.ч. со СМЕЩ) следует добавить:

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

Интересная статья, но без острой потребности вникать лень.

Я когда ваял в экселе производство растворителей, такие советы были для меня бесценными.

Но я их находил, в основном, на пленетеэксель (не сочтите за рекламу).

Читайте также  Как найти повторяющиеся значения в таблице Excel

А на пикабу это как то специфично выглядит, тем более второй пост подряд.

Плюсану, конечно, но жду таки котиков и сисек, ну или байку какую нибудь )))

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

И увы, никаких котиков и сисек тут. Только эксель, только хардкор:)

Думаю, тебе поможет фильтр

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

А суммирование рабочих дней и часов делается через СЧЁТЕСЛИ и СУММЕСЛИ, соответственно:

COUNTIF(N25:AC25;»Я») — на массив где Я и В

SUMIF(N25:AC25;»Я»;N26:AC26) — массив где Я и В и массив с часами

Суммесли не подходит?

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

Раб.дни в помощь. Там есть параметр Праздники. Делаете ссылку на массив с праздниками, приходящимися на будни, и будет Вам счастие.

Самый простой вариант — воспользоваться расширенным фильтром, который выберет из вашего массива все выходные и праздники с непустыми ячейками, а уж потом подсчитать получившийся результат. Праздники забиваются вручную, выхи — формулами. Делается все очень быстро.

Что-то более конкретное сказать можно только если файл посмотреть. Но по опыту, если надо считать по особому форматированные ячейки, то это только VBA

Внезапно для решения рабочих вопросов потребовалось резко изучить VBA. Можешь подсказать книги/курсы/видео для поверхностного изучения синтаксиса и общей логики языка?

изучал сам, без учебников. просто решал конкретную задачу. плюс был опыт программирования на php и javascript. поэтому логика там схожая.

В интернете полно ресурсов, где можно почитать.

а так синтаксис довольно-таки простой. Через точку пишете адрес объекта, к которому обращаетесь или к свойству этого объекта. Например, ячейки: Лист.Адрес.Свойство:

Каждая манипуляция записывается на отдельной строке

Вообще очень помогает макро-рекордер. Он правда часто пишет несколько более пространный код, но это удобно, если не знаешь как делать.

Из конструкций обязательно надо изучить IF . Then и циклы.

Пишем прототип бизнес-игры (экономическая стратегия для 24 пользователей по сети)

. на VBA?? сетевая? браузерная? тогда уже сразу учите PHP. По-хорошему, туда же еще mySQL для работы с БД и javascript для красивой реализации на стороне пользователя. Ну и конечно html с CSS для верстки страниц. сам так запилил своему прошлому работодателю мини-ERP

Вышел я сегодня значит первый день, взглянул на продукт и был поражен нестандартным подходом к разработке. Да, это реально сетевая игра для запуска по локалке, да, она реально работает на 24 пользователя, и да, она написана на VBA целиком и полностью. Выглядит как набор кнопок и всплывающих окон в экселе, но это реально работает. Обмен данными с сервером происходит через расшаренную на сервере папку с таблицами с данными для каждого конкретного пользователя. Так как это прототип, и у нас уже есть человек, который это все написал и поддерживает (я на нее сегодня чуть ли не молиться начал, когда увидел объем проделанной работы), буду договариваться с начальством о переводе всей этой фигни на нормальный стек технологий и буду учить, соответственно то, что ты написал)

офигеть. несколько раз прочитал пост. это же надо было так заморочиться и сделать игру на самом для этого не подходящем движке. создателю респект. главное в программирование, на мой взгляд, — это правильное построение алгоритмов. Если они выстроены правильно, то работать будет на чем угодно. Делал в свое время таск-менеджер и на удаленном сервере на php и mysql. Потом легко перенес это на VBA, т.к. логика и взаимосвязь таблиц данных одна и та же.

В любом случае успехов! Но мой совет — не занимайтесь больше такими извращениями!

И растягиваем вниз

Во-первых, @ArtemTabolin, спасибо — делаешь хорошее дело!

Во-вторых — сначала я зашел в этот пост, а потом уже заглянул в первый, т.к. первый пропустил, т.к. подумал, что это очередной рекламный пост — меняй название))

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

А где пример файла что бы вы живую поглядеть формулы?

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

Если честно, то меня эксел бесит 🙂

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

Я прям благоговею перед их создателями, считаю их просто монстрами. И одновременно мне их жалко, жалко их потраченный впустую труд. Что люди не делают, лишь бы не изучать VBA и SQL 🙂

И я показываю, им как все эти ужасные формулы на несколько строк переписываются маленьким скриптиком в VBA редакторе.

А если еще на компьютере есть MS Access то, тут вообще возможна истинная магия.

Учить этот ваш VBA труд ещё больший. ну всем дано программистами быть. Для меня это все — пытка просто.

я знаю, я сам через это прошел.

на одном из проектов заказчик прямо просил не использовать VBA, чтобы простой пользователь мог так или иначе понять логика и проследить ее в ходе трансформации данных. VBA хорош для невидимой черновой работы. Когда же есть большой объем интерактивности с юзером VBA не очень подходит.

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

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

Проведите эксперимент: объясните человеку формулу СУММЕСЛИ и код VBA с суммированием по условию через цикл. Для меня лично результат очевиден

Я могу использовать СУММЕСЛИ и в VBA, если знаю о её существовании.

В данном случае в VBA можно использовать функцию SumIf и объяснять человеку придется ровно тоже самое.

Я могу использовать любую фукнцию экселя в VBA только нужно знать её имя, что легко гуглится, ну или ищется в папке с установленной программой файл, в котором есть все нормальные имена фунций и их русскоязычные алиасы, по крайней мере в старых экселях этот файл был, обычная эксел таблица.

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

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

Уже тройное вложенное ЕСЛИ вызывает стойкое желание разбить монитор высчитывая эти скобочки и точку с запятой..

А если нужно 10 вложений. ничего кроме желания грязно выругаться это не вызывает. В VBA коде, хоть 15 вложений сделайте простым ctrl+C , ctrl+V

и всё это будет легко читаемо и прозрачно.

Собственно, я ни на чём не настаиваю, это чисто моё мнение. Но когда меня просят, сделать что-нибудь этакое, я по возможности стараюсь это запихнуть в VBA. Подход в стиле : «Нажми на кнопку, получишь результат»

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

Источник:
http://pikabu.ru/story/excelling_at_excel_vyip2_tsiklyi_v_excel_bez_vba_5726650

Как удалить или разрешить циклическую ссылку

Вы ввели формулу, но она не работает. Вместо этого вы получаете это сообщение об ошибке «Циклическая ссылка». Миллионы людей имеют такую же проблему, и это происходит из-за того, что формула пытается подсчитаться самой себе, и у вас есть функция, которая называется итеративным вычислением. Вот как это выглядит:

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

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

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

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

Читайте также  Вставка объекта в электронную таблицу Excel

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

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

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

Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение «Циклические ссылки».

В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.

При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение «Циклические ссылки» без адресов ячеек.

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

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

Когда Excel впервые находит циклическую ссылку, отображается предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.

Когда вы закроете сообщение, в ячейке будет отображено либо нулевое, либо последнее вычисленное значение. И теперь, наверное, говорят: «повесить последнее вычисленное значение?» Да. В некоторых случаях можно успешно выполнить формулу, прежде чем выполнять вычисление. Например, формула, использующая функцию если , может работать, пока пользователь не введет аргумент (часть данных, которая должна выполняться правильно), которая приводит к тому, что формула будет вычислять саму себя. В этом случае Excel сохраняет значение из последнего успешного вычисления.

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

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

Внимание! Во многих случаях при создании дополнительных формул с циклическими ссылками предупреждающее сообщение в приложении Excel больше не отображается. Ниже перечислены некоторые, но не все, ситуации, в которых предупреждение появится.

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

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

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

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

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

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

Иногда вам может потребоваться использовать циклические ссылки, так как они приводят к итерации функций — повторяются до тех пор, пока не будет выполнено определенное числовое условие. Это может замедлить работу компьютера, поэтому итеративные вычисления обычно отключены в 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, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник:
http://support.microsoft.com/ru-ru/office/%D0%BA%D0%B0%D0%BA-%D1%83%D0%B4%D0%B0%D0%BB%D0%B8%D1%82%D1%8C-%D0%B8%D0%BB%D0%B8-%D1%80%D0%B0%D0%B7%D1%80%D0%B5%D1%88%D0%B8%D1%82%D1%8C-%D1%86%D0%B8%D0%BA%D0%BB%D0%B8%D1%87%D0%B5%D1%81%D0%BA%D1%83%D1%8E-%D1%81%D1%81%D1%8B%D0%BB%D0%BA%D1%83-8540bd0f-6e97-4483-bcf7-1b49cd50d123

Как делать цикл по столбцам в Excel, если у них буквенные обозначения

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

Как написать цикл для смены столбцов?
Ведь столбцы обозначаются буквами, существуют ли буквенные циклы

Причем количество столбцов зависит от размера двумерного массива Array
ub1Array варьируется от 7-8 до 25-30

Добавлено через 9 минут
Простите не корректную запись

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

буквенные обозначения TU;JU;VU в обозначении микросхемы AT49BV040B-TU
Господа! Подскажите пожалуйста, что означают буквенные обозначения TU;JU;VU в обозначении.

Буквенные обозначения при вводе ip route на маршрутизаторе
Здравствуйте! Знаю, что при вводе ip route вначале даётся справка о буквенных обозначений, но.

Цикл по столбцам Excel Файла
Добрый день. Подскажите пожалуйста как сделать цикл по столбцам Excel файла. Надо в первой строке.

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

Так вот КАК сделать цикл по буквам(столбцам)?
Натыкался на форуме, что можно изменить где-то буквы на цифры, но не нашел где это в Excele делается.
Можно также запускать макрос в Excele, но тогда как в макросе Excelя из VB менять количество заполняемых столбцов.
Заполняемых строчек больше 30, поэтому для каждой делать макрос вручную. весьма печально.
Функции тоже разные

Добавлено через 4 минуты

Мне нужно НЕ узнать, что написано в ячейке, а туда ЗАПИСАТЬ

Вы правы, Excel работает с числами, поэтому WorkSheet1.cells(5,5) это Е5
А вот как заставить его понять «=AVERAGE(» & 5 & «34:» 5 & 100 & «)» это СРЕДНЕЕ(Е34:Е100)?

его недолго и в .net переписать. Что мы тут делаем:
1. Имея координаты ячейки (в данном случае я просто взял выделяемую ячейку), получаем ее адрес в стиле «буква столбца» + «номер строки»
2. режем на массив по разделителю $
3. Получаем компонент обозначения столбца.

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

Вариант 2:
Завести массив типа
a
b
c
d
.
w
aa
ab

и так до конца.
Имея номер столбца получить из массива (элемент номер-1) букву/буквы столбца.
Ну и дальше составлять формулу, которую запишешь в ячейку.

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Цикл, который выводит все числа от 0 до 500, делящиеся на 7, если в них есть цифра 8
Напишите цикл, который выводит все числа от 0 до 500, делящиеся на 7, если в них есть цифра 8 .

Как сделать цикл по столбцам в Екселе
Тут возник вопрос, нужно выделение в первой строке 4 ячеек, их объединение, и дальше опять 4 ячейки.

Напишите цикл, который выводит все числа от 0 до 500, делящиеся на 7, если в них есть цифра 8
Удалось только вывести все числа в указанном диапазоне, кратные 7. a = for i in range(500): .

Excel: преобразование номера столбцов в буквенные символы и наоборот
Всем привет. Не нашёл в интернете работающего примера преобразования порядкового номера.

Решил сделать игровой автомат сделал три лейбла и в них запихнул цикл из чисел. Как сделать что бы цикл остано
Решил сделать игровой автомат сделал три лейбла и в них запихнул цикл из чисел. Как сделать что бы.

Вывод таблицы в Excel. Как задать стили ячейкам и столбцам?
Делаю вывод таблц в Excel Может кто подскажет, как поменять формат ячейки и размер

Источник:
http://www.cyberforum.ru/vb-net/thread1098452.html