Здравствуйте! У меня есть формула, собирающая данные по трудозатратам сотрудников по двум критериям, и она работает исправно. Проблема в том, что сотрудников у нас 14 человек, и тупо размножить данную формулу в ячейке выглядит некрасиво, а принеобходимости что-либо изменить вынуждает поиск делать это по 98 тысячам вхождений. И с этим ещё можно было бы жить, но количество сотрудников будет увеличиваться, и что же, добавлять вручную новые повторы формулы? А если будет несколько десятков человек?
Подскажите, с помощью каких функций и можно ли без скриптов описать цикл (N количество раз повторить процедуру, подставляя внутрь неё значение параметра по номеру повтора соответственно), и по каким словам найти в Сети пример? Если тут без скриптов никак, то, пожалуйста, подскажите, где взять, или дайте код. Я ни разу в Таблицах скрипты не писал, хотя самую малость ковырялся в Python какое-то время назад.
Заранее благодарен!
Здравствуйте! У меня есть формула, собирающая данные по трудозатратам сотрудников по двум критериям, и она работает исправно. Проблема в том, что сотрудников у нас 14 человек, и тупо размножить данную формулу в ячейке выглядит некрасиво, а принеобходимости что-либо изменить вынуждает поиск делать это по 98 тысячам вхождений. И с этим ещё можно было бы жить, но количество сотрудников будет увеличиваться, и что же, добавлять вручную новые повторы формулы? А если будет несколько десятков человек?
Подскажите, с помощью каких функций и можно ли без скриптов описать цикл (N количество раз повторить процедуру, подставляя внутрь неё значение параметра по номеру повтора соответственно), и по каким словам найти в Сети пример? Если тут без скриптов никак, то, пожалуйста, подскажите, где взять, или дайте код. Я ни разу в Таблицах скрипты не писал, хотя самую малость ковырялся в Python какое-то время назад.
В Вашем случае, думаю, проще всего будет подтянуть к каждому названию листа данные из этого листа, необходимые для суммирования, а потом уже посто просуммировать их... можно и скриптом конечно, вот, кажется, подходящий пример
В Вашем случае, думаю, проще всего будет подтянуть к каждому названию листа данные из этого листа, необходимые для суммирования, а потом уже посто просуммировать их... можно и скриптом конечно, вот, кажется, подходящий примерaliramora191
с помощью каких функций и можно ли без скриптов описать цикл (N количество раз повторить процедуру, подставляя внутрь неё значение параметра по номеру повтора соответственно)
Насколько я мог понять из картинки, вместо "единички в зеленом квадратике" можно использовать счетчик вида: [vba]
Код
ROW() - колвоСтрокПередПервой
[/vba] Т.е. если первая строка массива (соответствующая "единичке в зеленом квадратике") - это, например, 3-я строка Excel, то выражение можно записать так: [vba]
Код
ROW() - ROW($A$2)
[/vba] , что на третьей строке даст значение 1, на четвертой - 2 и т.д.
с помощью каких функций и можно ли без скриптов описать цикл (N количество раз повторить процедуру, подставляя внутрь неё значение параметра по номеру повтора соответственно)
Насколько я мог понять из картинки, вместо "единички в зеленом квадратике" можно использовать счетчик вида: [vba]
Код
ROW() - колвоСтрокПередПервой
[/vba] Т.е. если первая строка массива (соответствующая "единичке в зеленом квадратике") - это, например, 3-я строка Excel, то выражение можно записать так: [vba]
Код
ROW() - ROW($A$2)
[/vba] , что на третьей строке даст значение 1, на четвертой - 2 и т.д.Gustav
И по поводу замечания (см. на картинке в сообщении №1) по поводу того, что функция AND "почему-то" не работает в формуле массива.
Не работает потому, что эта функция AND, так же, как и функция OR, не является массивной. Так же, кстати, как и функция обычного суммирования SUM - как диапазоны ей не подай, как в ArrayFormula не оберни - она всегда будет возвращать ОДНО значение. Поэтому если мы, в надежде получить построчные суммы в диапазоне C1:C10 пишем в C1 такую формулу: [vba]
Код
=ArrayFormula(SUM(A1:B10))
[/vba] , то ничего у нас не получается - имеем одно значение, но зато получается вот так (имеем 10 значений): [vba]
Код
=ArrayFormula(A1:A10+B1:B10)
[/vba] С логическими функциями AND и OR ситуация аналогичная - нужно просто вспомнить, что AND - это логическое умножение (*), а OR - сложение (+). Тогда вместо "неработающих" (точнее, не дающих построчные результаты - так-то они работают, возвращая только одно общее значение) формул: [vba]
Код
=ArrayFormula(AND(A1:A10>5;B1:B10>5))
=ArrayFormula(OR(A1:A10>5;B1:B10>5))
[/vba] можно записать их через обычные арифметические операции умножения и сложения соответственно. И поскольку результатом этих операций будет число, то вернуться обратно к булевским значениям можно, элементарно сравнив это получившееся число с нулём: [vba]
Код
=ArrayFormula((A1:A10>5)*(B1:B10>5)>0)
=ArrayFormula((A1:A10>5)+(B1:B10>5)>0)
[/vba]
И по поводу замечания (см. на картинке в сообщении №1) по поводу того, что функция AND "почему-то" не работает в формуле массива.
Не работает потому, что эта функция AND, так же, как и функция OR, не является массивной. Так же, кстати, как и функция обычного суммирования SUM - как диапазоны ей не подай, как в ArrayFormula не оберни - она всегда будет возвращать ОДНО значение. Поэтому если мы, в надежде получить построчные суммы в диапазоне C1:C10 пишем в C1 такую формулу: [vba]
Код
=ArrayFormula(SUM(A1:B10))
[/vba] , то ничего у нас не получается - имеем одно значение, но зато получается вот так (имеем 10 значений): [vba]
Код
=ArrayFormula(A1:A10+B1:B10)
[/vba] С логическими функциями AND и OR ситуация аналогичная - нужно просто вспомнить, что AND - это логическое умножение (*), а OR - сложение (+). Тогда вместо "неработающих" (точнее, не дающих построчные результаты - так-то они работают, возвращая только одно общее значение) формул: [vba]
Код
=ArrayFormula(AND(A1:A10>5;B1:B10>5))
=ArrayFormula(OR(A1:A10>5;B1:B10>5))
[/vba] можно записать их через обычные арифметические операции умножения и сложения соответственно. И поскольку результатом этих операций будет число, то вернуться обратно к булевским значениям можно, элементарно сравнив это получившееся число с нулём: [vba]
В Вашем случае, думаю, проще всего будет подтянуть к каждому названию листа данные из этого листа, необходимые для суммирования, а потом уже просто просуммировать их... можно и скриптом конечно, вот, кажется, подходящий пример
Подтягивать данные на этот лист во вспомогательные ячейки - будет очень громоздко и антиэстетично, поскольку данный лист является чем-то вроде дашборда по проекту, в который собираются данные данные о трудозатратах по нескольким критериям сразу, и на нём производится корректирование расчёта цены (вручную) для выставления заказчику. К тому же, у меня будет в полной версии книги уже 15 листов проектов + 15 листов счёта + 14 сотрудников + 3 листа со справочной информацией и 1 лист с сырыми данными = 33 листа. Вводить ещё один тип листа для каждого проекта будет перебор. А количество проектов и сотрудников только растёт. Поэтому я ищу решения для автоматизации.
Предложенный вами код буду изучать. Похоже, придётся мне погрузиться в JS... :))
Возможно. Но это не объясняет, как создать цикл, и как передать номер цикла вместо "единички в зеленом квадратике". Или я не понял, как записать это. Можете дать минимальный пример цикла?
В Вашем случае, думаю, проще всего будет подтянуть к каждому названию листа данные из этого листа, необходимые для суммирования, а потом уже просто просуммировать их... можно и скриптом конечно, вот, кажется, подходящий пример
Подтягивать данные на этот лист во вспомогательные ячейки - будет очень громоздко и антиэстетично, поскольку данный лист является чем-то вроде дашборда по проекту, в который собираются данные данные о трудозатратах по нескольким критериям сразу, и на нём производится корректирование расчёта цены (вручную) для выставления заказчику. К тому же, у меня будет в полной версии книги уже 15 листов проектов + 15 листов счёта + 14 сотрудников + 3 листа со справочной информацией и 1 лист с сырыми данными = 33 листа. Вводить ещё один тип листа для каждого проекта будет перебор. А количество проектов и сотрудников только растёт. Поэтому я ищу решения для автоматизации.
Предложенный вами код буду изучать. Похоже, придётся мне погрузиться в JS... :))
Возможно. Но это не объясняет, как создать цикл, и как передать номер цикла вместо "единички в зеленом квадратике". Или я не понял, как записать это. Можете дать минимальный пример цикла?Mediahead
Возможно. Но это не объясняет, как создать цикл, и как передать номер цикла вместо "единички в зеленом квадратике". Или я не понял, как записать это.
В условиях "слепоты и глухоты" обсуждения (а без файла примера оно по-другому, увы, хоть теоретически и может быть - с натяжечкой! - но на практике обычно обречено на невнятное "тыканье-мыканье", которое у нас сейчас и происходит ), так вот, в этих условиях, насколько я понимаю к этому моменту времени, имеется примерно следующая последовательность формул в соседних строках: [vba]
[/vba] Последовательность эта не очень операбельна (от слова "совсем") в плане протягивания и копирования формулы в нижележащие строки - кроме как варианта размножения первой строки и последующего построчного ручного изменения "единички в зеленом квадратике" на значения от 2 до 9 во всех строках ниже первой.
Я же предлагаю вариант написания первой строки в виде, пригодном для протягивания вниз, с автоматическим самогенерированием чисел "в зеленом квадратике" от 1 до 9 : [vba]
Возможно. Но это не объясняет, как создать цикл, и как передать номер цикла вместо "единички в зеленом квадратике". Или я не понял, как записать это.
В условиях "слепоты и глухоты" обсуждения (а без файла примера оно по-другому, увы, хоть теоретически и может быть - с натяжечкой! - но на практике обычно обречено на невнятное "тыканье-мыканье", которое у нас сейчас и происходит ), так вот, в этих условиях, насколько я понимаю к этому моменту времени, имеется примерно следующая последовательность формул в соседних строках: [vba]
[/vba] Последовательность эта не очень операбельна (от слова "совсем") в плане протягивания и копирования формулы в нижележащие строки - кроме как варианта размножения первой строки и последующего построчного ручного изменения "единички в зеленом квадратике" на значения от 2 до 9 во всех строках ниже первой.
Я же предлагаю вариант написания первой строки в виде, пригодном для протягивания вниз, с автоматическим самогенерированием чисел "в зеленом квадратике" от 1 до 9 : [vba]
[/vba] Формула записана в ячейке W12 (в единственном экземпляре) и действует вниз до ячейки W272. И работает уже не один год (!) в многопользовательской таблице. Может возникнуть вопрос "А почему не просто в таком виде?": [vba]
Код
=ArrayFormula(U3:U263-V12:V272)
[/vba] Отвечу. По началу она такой и была. Но пользователи, любящие перетаскивание ячеек больше, чем их копирование, частенько ломали эту формулу именно своими перетаскиваниями. После того, как адреса упаковали в INDIRECT, эти ломания прекратились.
Так что... не очень понимаю Ваше замечание. И потом мне-то тут всё равно - не этот аспект сейчас обсуждается, а "счётчик цикла". И я подразумеваю написание эталонной первой строки, а потом её протяжку вниз, с созданием формулы в каждой строке - т.е. здесь не написание единственной формулы массива только в первой ячейке, когда ниже она волшебно работает в других ячейках. Нет, здесь не тот случай.
[/vba] Формула записана в ячейке W12 (в единственном экземпляре) и действует вниз до ячейки W272. И работает уже не один год (!) в многопользовательской таблице. Может возникнуть вопрос "А почему не просто в таком виде?": [vba]
Код
=ArrayFormula(U3:U263-V12:V272)
[/vba] Отвечу. По началу она такой и была. Но пользователи, любящие перетаскивание ячеек больше, чем их копирование, частенько ломали эту формулу именно своими перетаскиваниями. После того, как адреса упаковали в INDIRECT, эти ломания прекратились.
Так что... не очень понимаю Ваше замечание. И потом мне-то тут всё равно - не этот аспект сейчас обсуждается, а "счётчик цикла". И я подразумеваю написание эталонной первой строки, а потом её протяжку вниз, с созданием формулы в каждой строке - т.е. здесь не написание единственной формулы массива только в первой ячейке, когда ниже она волшебно работает в других ячейках. Нет, здесь не тот случай.Gustav
К сожалению, не подойдёт такое решение. Всё надо сделать внутри одной ячейки, потому что в каждую ячейку собираются свои данные, а при вашем варианте мне придётся умножить число имеющихся строк на количество сотрудников.
aliramora191 Полтора часа - и все "конфиденциальные данные" вычищены в копии книги, и она даже работает! А это не сразу далось ))) Вуаля, ссылка на мою книгу.
К сожалению, не подойдёт такое решение. Всё надо сделать внутри одной ячейки, потому что в каждую ячейку собираются свои данные, а при вашем варианте мне придётся умножить число имеющихся строк на количество сотрудников.
aliramora191 Полтора часа - и все "конфиденциальные данные" вычищены в копии книги, и она даже работает! А это не сразу далось ))) Вуаля, ссылка на мою книгу.
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
так что в основах есть пробелы.
