Разобрался. Вот результат.
Единственное, подскажите:
1) как переместить кнопку "Тест" правее, что бы не закрывать видимость расчетов
2) как обойти ограничение: когда первая ячейка пустая, то расчет не производится

Здравствуйте.
Помогите модернизировать расчёт на основе варианта с SQL расчётом средней температуры  (изготовленного ikki) для расчёта суммы осадков.
Кроме того, просьба добавить расчёт средней/суммы за день.

Павел \Ʌ/, Спасибо, всё работает.
Да, моя вина, действительно, поспешил и сам запутался в составлении итоговой таблицы. Сейчас расставил как надо и добавил к Вашей обратную формулу пересчёта. В итоге "Исходная таблица - изменяемая" заполнилась правильно без ошибок.
С наступающим Новым годом!

Павел \Ʌ/, чёткой закономерности в расположении товаров в итоговой таблице нет. Однако есть соответствия, по которым можно выяснить, что и откуда брать - это два критерия по столбцам и два критерия по строкам, а также расположения товара в схеме. Получается всего 5 критериев, которые будут ориентиром для итоговой таблицы.
Пример: Значение 1,9 товара "Молоток" в исходной таблице ячейка P19 (по исходной схеме ячейка P8)  соответствует критериям: "Сидоров" + "1" - по строкам и "Дальний" + "P" - по столбцам. Следовательно в итоговой таблице результат будет записан в ячейку L75, так как соответствует тем же самым критериям: "Молоток" + "P"  +  "Сидоров" +  "Дальний" + "1" - это логистика, по которой берётся значение из исходной таблицы и записывается в итоговую таблицу.

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

Павел \Ʌ/, здравствуйте. Спасибо, за предложенный способ.
Чтобы не было зацикливания, создаются дополнительные исходные и итоговые таблицы. Даже можно без итоговой, так как
при внесении необходимой цифры будет удаляться формула в этой ячейке. Однако для наглядности можно и с ней.
Примерно такой формулой как Вы написали, я теоретически думал решить задачу (2-ой способ).

Просьба адаптировать Вашу формулу для примера с расширенной схемой, приведённой ниже.

andypetr, так вы просто перенесли формулу из одной таблицы в другую. Такой же процесс ручного пересчёта я показал в верхней таблице, где создаётся обратная формула расчёта для каждой ячейки с указанием от неё зависимых, но такой метод не подходит для большого диапазона данных.
Задача была автоматизировать процесс пересчёта, без прописи обратной формулы расчёта и указания зависимых для каждой ячейки.
Мне кажется, что здесь, возможно, есть два способа решения задачи:
1) с помощью формулы или VBA будет отслеживаться ссылки на ячейки итоговой таблицы и таким образом связывать итоговую с исходной таблицей, а далее с помощью формулы обратного пересчёта изменяться значения в новой исходной таблице.
2) рядом с исходной и итоговой создаются таблицы со схемой размещения товара (текст), на их основании создаётся новая таблица, где с помощью формулы (на соответствие размещения товара) и формулы обратного пересчёта будет отслеживаться путь и автоматически производиться корректировка исходных данных по итоговым, а сама формула может легко растягиваться на весь диапазон данных.

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

Павел \Ʌ/, Спасибо.
Да, спасибо, поспешил, правильно - двузначное.

Здравствуйте. Просьба помочь с формулой для поиска чисел (целых и десятичных) внутри ячеек диапазона соответствующих условию и  подсчёта их суммы.
Условия:
1) выбираются числа на основании двузначного числа (на какую цифру оно заканчивается: 0, 1 или 2) после  символа "*".
2) в счёт идёт число/числа находящееся перед символом "*",  но до символа "-". В ячейке их может быть несколько.

Запись в ячейке состоит из связок: число+символ "*"(разделяющий число и условие)+двузначное число(условие)+символ "-"(разделяющий следующую связку). Поиск производится внутри каждой связки по диапазону.
Пример с формой и ручным подсчётом прилагается.

Павел \Ʌ/, Спасибо.
Вторая формула (без дополнительного столбца) делает связку месяц-декада.
Думал, что в этой теме уже не ответят (один вопрос - один ответ) и поэтому создал соответствующую тему https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=160871&TITLE_SEO=160871-formula-dlya-smeshcheniya-na-osnovanii-dvukh-kriteriev

Проконсультируйте по поводу Вашей формулы:
=ЕСЛИОШИБКА(СУММЕСЛИ(ИНДЕКС(D$4:D$369;п_1):ИНДЕКС(D$4:D$369;п_2);">10";ИНДЕКС(H$4:H$369;п_1):ИНДЕКС(H$4:H$369;п_2))*10/СУММЕСЛИ(ИНДЕКС(D$4:D$369;п_1):ИНДЕКС(D$4:D$369;п_2);">10";ИНДЕКС(D$4:D$369;п_1):ИНДЕКС(D$4:D$369;п_2));"")

1) п_1... п_2 - это шаблоны с формулами для сокращения общей формулы?
2) что нужно менять, что бы расчет был в среднем за месяц?
Или тогда уже в другой теме попробовать через соответствие месяц-декада?
Если нет, то первая формула =ЕСЛИОШИБКА(10*СУММЕСЛИМН(I$4:I$63;E$4:E$63;">10";C$4:C$63;СТРОКА(AG1))/СУММЕСЛИМН(E$4:E$63;E$4:E$63;">10";C$4:C$63;СТРОКА(AG1));"") отлично справляется и её легко и понятно изменять под другие цели.

Павел \Ʌ/, Ещё раз Спасибо. Вы уже и сразу проверку предусмотрели.

Здравствуйте.
Просьба помочь с формулой, которая бы на основании двух критериев (месяц и декада) производила расчёт по диапазону.
В прикрепленном примере имеется формула она должна растягиваться, а на основании месяц-декада делать расчёт по конкретным дням в декаде месяца.

Sergey Stoyanov, Спасибо, всё работает. Только сначала выдавала ошибку, потом понял, что формула для  Excel 2007+, а работа делается в Excel 2003.
А я наконец-то сам разобрался и доделал свою формулу:
=СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3);">10";ИНДЕКС(H:H;(СТРОКА(H1)-1)*10+4):ИНДЕКС(H:H;СТРОКА(H1)*10+3))*10/СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3);">10";ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3))

С этим разобрался. Теперь можно и по связке месяц-декада разбираться, чтобы счёт был по декадам и не было недопонимания.

МатросНаЗебре,
Я же написал, мне нужна формула с шагом 10

МатросНаЗебре,
Вот переделал формулу:
=СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3);">10";ИНДЕКС(H:H;(СТРОКА(H1)-1)*10+4):ИНДЕКС(H:H;СТРОКА(H1)*10+3))*10/СУММ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3))
но выдаёт ошибку

При сокращении формулы до:
=СУММ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3))
Всё работает как надо.

До этой части тоже всё правильно работает:
=СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3);">10";ИНДЕКС(H:H;(СТРОКА(H1)-1)*10+4):ИНДЕКС(H:H;СТРОКА(H1)*10+3))

Все предположения я учёл, поэтому и написал в первом сообщении:Мне формула понадобится для дальнейших задач. Сейчас нужно понять где ошибка в этой формуле:
=СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3);">10";ИНДЕКС(H:H;(СТРОКА(H1)-1)*10+4):ИНДЕКС(H:H;СТРОКА(H1)*10+3))*10/СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*10+4):ИНДЕКС(D:D;СТРОКА(D1)*10+3))
Пишет: "Для функции введено слишком мало аргументов"

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

Sergey Stoyanov,
Сделал. но не получается.
=СУММЕСЛИ(ИНДЕКС(D:D;10*ЦЕЛОЕ(СТРОКА(D1)/10)+1):ИНДЕКС(D:D;10*ЦЕЛОЕ(СТРОКА(D1)/10)+10);">10";ИНДЕКС(H:H;10*ЦЕЛОЕ(СТРОКА(H1)/10)+1):ИНДЕКС(H:H;10*ЦЕЛОЕ(СТРОКА(H1)/10)+10)*10/СУММЕСЛИ(ИНДЕКС(D:D;10*ЦЕЛОЕ(СТРОКА(D1)/10)+1):ИНДЕКС(D:D;10*ЦЕЛОЕ(СТРОКА(D1)/10)+10))

Для проверки сократил формулу до:
=СУММ(ИНДЕКС(D:D;10*ЦЕЛОЕ(СТРОКА(D1)/10)+1):ИНДЕКС(D:D;10*ЦЕЛОЕ(СТРОКА(D1)/10)+10))
, но не корректно считает и при растягивании смещения не происходит - все значения одинаковые.

Нужно без дополнительного столбца.
Должно как-то вот так получиться:

=СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*9+4):ИНДЕКС(D:D;СТРОКА(D1)*9+4);">10";ИНДЕКС(H:H;(СТРОКА(H1)-1)*9+4):ИНДЕКС(H:H;СТРОКА(H1)*9+4))*10/СУММЕСЛИ(ИНДЕКС(D:D;(СТРОКА(D1)-1)*9+4):ИНДЕКС(D:D;СТРОКА(D1)*9+4))

но где-то ошибка

Здравствуйте. Помогите с формулой для растягивания по строкам с заданным шагом.
Пример (с шагом 10 по строкам):
1-ая формула
=СУММЕСЛИ(D4:D13;">10";H4:H13)*10/СУММЕСЛИ(D4:D13;">10";D4:D13)
2-ая формула
=СУММЕСЛИ(D14:D23;">10";H14:H23)*10/СУММЕСЛИ(D14:D23;">10";D14:D23)
...
Знаю, что нужно использовать ИНДЕКС, но как правильно в длинных формулах?

P.S. Понимаю, что для данного файла-примера лучше использовать привязку: месяц+декада. Просто хочу разобраться.
Если можно, то подскажите оба варианта:
1) заданное смещение через ИНДЕКС
2) связка месяц+декада через ИНДЕКС+ПОИСКПОЗ

Sanja, Спасибо, но необходимо формулой.

БМВ, Спасибо за "тонкий" намёк-подсказку.
Поменял в двух функциях ПОВТОР число повторений с 6 на 8 и ошибка пропала. Получается для блоков от 1 до 4 в функции ПОВТОР количество повторений использовать - 6, для 5-ти блоков - 8. Далее с каждым увеличением блок на один, необходимо увеличивать число повторений в функции ПОВТОР на два.

Формула для ячеек содержащих от 1 до 5-ти блоков:
=ЕСЛИ(ЕПУСТО(E4);"";СУММ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-0";8);"-";"*");"*";ПОВТОР(" ";99));99*{1:3:5:7:9};99))*ПРОСМОТР(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-10";8);"-";"*");"*";ПОВТОР(" ";99));99*{2:4:6:8:10};99)));$A$2:$A$7;$B$2:$B$7)))

Существует таблица данных с количеством блоков от 1 до 5, разделенных символом "-". При редактировании формулы для 5-ти блоков стала появляться ошибка в ячейках, где имеется только один блок. Помогите пожалуйста отредактировать формулу.
Исходная формула для ячеек содержащих от 1 до 3-ёх блоков:
=ЕСЛИ(ЕПУСТО(E4);"";СУММ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-0";6);"-";"*");"*";ПОВТОР(" ";99));99*{1:3:5};99))*ПРОСМОТР(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-10";6);"-";"*");"*";ПОВТОР(" ";99));99*{2:4:6};99)));$A$2:$A$7;$B$2:$B$7)))
При создании формулы для ячеек содержащих от 1 до 5-ти блоков:
=ЕСЛИ(ЕПУСТО(E4);"";СУММ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-0";6);"-";"*");"*";ПОВТОР(" ";99));99*{1:3:5:7:9};99))*ПРОСМОТР(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-10";6);"-";"*");"*";ПОВТОР(" ";99));99*{2:4:6:8:10};99)));$A$2:$A$7;$B$2:$B$7)))
в ячейках, где имеется только один блок  появляется ошибка #ЗНАЧ!
P.S. В формуле для ячеек содержащих от 1 до 4-ёх блоков: ...{1:3:5:7}...{2:4:6:8}... ошибка не возникает.

БМВ,
Пришлось увеличить количество блоков и соответственно изменить формулы для расчёта. В результате, при создании формулы для 5-ти блоков стала появляться ошибка в ячейках, где имеется только один блок. Помогите пожалуйста отредактировать формулу.
Исходная формула для ячеек содержащих от 1 до 3-ёх блоков:
=ЕСЛИ(ЕПУСТО(E4);"";СУММ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-0";6);"-";"*");"*";ПОВТОР(" ";99));99*{1:3:5};99))*ПРОСМОТР(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-10";6);"-";"*");"*";ПОВТОР(" ";99));99*{2:4:6};99)));$A$2:$A$7;$B$2:$B$7)))
При создании формулы для ячеек содержащих от 1 до 5-ти блоков:
=ЕСЛИ(ЕПУСТО(E4);"";СУММ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-0";6);"-";"*");"*";ПОВТОР(" ";99));99*{1:3:5:7:9};99))*ПРОСМОТР(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&ПОДСТАВИТЬ(0&E4&ПОВТОР("-10";6);"-";"*");"*";ПОВТОР(" ";99));99*{2:4:6:8:10};99)));$A$2:$A$7;$B$2:$B$7)))
в ячейках, где имеется только один блок  появляется ошибка #ЗНАЧ!
P.S. В формуле для ячеек содержащих от 1 до 4-ёх блоков: ...{1:3:5:7}...{2:4:6:8}... ошибка не возникает.

Павел \Ʌ/, Спасибо.
Подскажите, а как прописать в условное форматирование Вашу формулу, как в Вашем примере на листе 2?

Msi2102, Спасибо

Тимофеев, Спасибо

Msi2102, добавил.

Здравствуйте.
Существует формула поиска введенного текста в ячейку E3 на соответствие с текстом в ячейке H3.

=ЕСЛИ(ЕПУСТО($E$3);ИСТИНА;ЕСЛИОШИБКА(ПОИСК($E$3;H3)>=1;ЛОЖЬ))

Но данный поиск идёт по любому вхождению текста ячейки H3, а необходимо, что бы поиск был по первым буквам.

БМВ, СПАСИБО. Оба варианта работают.

А куда прячется/прописывается часть формулы в имени _BlocK ? Чтобы можно было изменить выделенный диапазон, либо использовать её для столбцов.
Разобрался: В Excel 2003 "Присвоение имени" в формулах вызывается сочетанием клавиш Ctrl+F3, а сама находится по пути: Вставка -> Имя -> Присвоить


Попробовал формулу из файла .xlsx использовать для столбцов выдаёт #Н/Д, вспомнил, что Вы говорили: , поэтому сделал вот так:
=СУММ(--(--ТЕКСТ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("-"&ТРАНСП(C4:C7);"-";ПОВТОР(" ";99));99*{1:2:3};99));"*";ПОВТОР(" ";9));18;9));"0;0;0;\0")>9))=СУММ(--(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("-"&ТРАНСП(C4:C7);"-";ПОВТОР(" ";99));99*{1:2:3};99))<>""))
и только потом разглядел, что значит другой разделитель (до этого считал просто магией, так как в прошлый раз не видел различий между формулами) - вместо двоеточия используется точка с запятой {1:2:3} - {1;2;3}
=СУММ(--(--ТЕКСТ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("*"&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("-"&C4:C7;"-";ПОВТОР(" ";99));99*{1;2;3};99));"*";ПОВТОР(" ";9));18;9));"0;0;0;\0")>9))=СУММ(--(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("-"&C4:C7;"-";ПОВТОР(" ";99));99*{1;2;3};99))<>""))

Подскажите как дописать к коду из первого сообщения поиск к искомым буквам символ "*", чтобы искал по первым буквам?
Пробовал:  If Target.Address = "$E$3"&*  - не работает
                   If Target.Address = "$E$3&*"  - не работает

Перечитал смежные темы и нашёл ещё интересный VBA-код от Hugo, в теме (ссылка на файл-пример)

Код

Option Explicit Option Compare Text Dim bu As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Column = 1 Then    bu = True    With Me.TextBox1        .Top = Target.Top: .Left = Target.Left: .Text = Target.Value: .Activate    End With    With Me.ListBox1        .Top = Target.Top - 20: .Left = Target.Left + 172: .Clear    End With    bu = False    Me.TextBox1.Visible = True: Me.ListBox1.Visible = True Else    Me.TextBox1.Visible = False: Me.ListBox1.Visible = False End If End Sub Private Sub TextBox1_Change() Dim x, i As Long, txt As String, lt As Long, s As String If Len(TextBox1.Text) = 0 Or bu = True Then Exit Sub txt = TextBox1.Text: lt = Len(TextBox1.Text) x = Sheets("База данных").Columns(1).SpecialCells(2).Value 'For i = 1 To UBound(x, 1)    ' поиск по первым буквам '    If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" 'Next i For i = 1 To UBound(x, 1) 'поиск по любому вхождению If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1) Next i ListBox1.List = Split(Mid(s, 2), "~") End Sub Private Sub ListBox1_Click() If ListBox1.ListIndex = -1 Then Exit Sub bu = True ActiveCell.Value = ListBox1.Value Me.TextBox1.Text = ListBox1.Value bu = False End Sub

Помогите пожалуйста его адаптировать или написать новый к файлу "Книга 1" с имеющимся в нём коде по горизонтальной фильтрации из первого сообщения темы.
Необходимо сделать:
- набор поиска из выделенной ячейки
- поиск по строке с заданным диапазоном
- поиск только по первым буквам
- если не выбирать предложенные варианты из выпадающего списка, то фильтрация (по коду из первого сообщения) по словам начинающимся на эту букву/ы (как если бы набрали "и*", то отфильтровались бы Иван и Игорь)
- при повторном поиске, когда набираешь буквы в ячейке, то предыдущий набор букв стирается (в файле "Книга 1" так и есть, а вот в коде Hugo надо сначала удалить предыдущий текст)
- если удаляешь текст из ячейки, то фильтрация отключается и отображаются все столбцы (в файле "Книга 1" так и есть, а вот в коде Hugo удалить текст поиска без выбора нового невозможно)
- выпадающий список с предложенными вариантами появляется только при выборе ячейки с поиском (в коде Hugo так и есть)  
- когда выбрал ячейку с поиском, но ещё ничего не набрал - выпадающий список пустой (в коде Hugo так и есть)
- высота выпадающего списка регулируется в зависимости от количества соответствующих поиску слов (в коде Hugo такого нет) - не принципиально

asesja,
Спасибо, работает.
Я так понял "пользовательская функция" - это макрос и, просто копированием, функцию  "=PosZ(диапазон)" из файла в файл не перенести, нужно копировать VBA-код.

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

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