ПРостенький макрос из excle в Libre

[bigor]

включить в проверку

Ну тогда такой вариант

Код Выделить Развернуть

=SUMIF($A$2:$A$10;"бакал*";INDEX($B$2:$F$10;;MATCH("ППС";$B$1:$F$1;0))) по данным вашего файла Данные.ods, выложенного выше
массивная, вводится тремя кнопками ctrl+shift+enter
Хотя и с summproduct не реагирует на текст, она считает только в столбце с нужным названием

[kroush]

Хотя и с summproduct не реагирует на текст, она считает только в столбце с нужным названием

пробовал по разному, все равно как только в массив попадает текст сразу ошибка

Последняя формула заработала корректно, создал её с помощью помощника, массивная ctr+shift+ enter не понадобилась. Работает как часы. Хотя когда забивал вручную, приходилось использовать массивную.

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

[bigor]

Формула из #15 не массивная, первоначально, я другую писал, забыл удалить про массивность.
sumproduct, действительно при тексте вываливается в ошибку. Поправленная

Код Выделить Развернуть

=SUMPRODUCT(($B$1:$E$1="ППС")*(ISNUMBER(SEARCH("бакал";$A$2:$A$10)))*(IFERROR(--$B$2:$E$10;0)))

[kroush]

Спасибо всем большое. Все работает как часы.
Возник ещё вопрос. А можно ли осуществить поиск по строке и вытащить только данные находящиеся после совпадения с поиском.

Код Выделить Развернуть

SBP; 200706;B3345100030056060000110011140302;000000;11.12.2023 12:59:29;114.99;0;KKMCLSALE...
Мне надо вытащить сумму(114.99) после времени 12:59:29. Время меняется в зависимости от времени оплаты.

[bigor]

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

[kroush]

Добрый день, наконец добрался до своей идеи. А можно пример. Просидел весь вечер ничего путно не получилось. А в регулярных выражениях я нечего не понимаю. У меня была идея, вначале ищем по дате, после даты до суммы количество символов не меняется, добирамеся до суммы, но ещё надо отсечь все лишнее после неё и поменять .  на ,   так как calc не суммирует с точкой. Но и как убирать лишнее тоже не понятно

[bigor]

Одной строки мало для понимания. Поэтому потом сами допиливать будете.
Для даты

Код Выделить Развернуть

=--TRIM(MID(SUBSTITUTE(A1;";";REPT(" ";100));400;100))Для суммы

Код Выделить Развернуть

=--SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1;";";REPT(" ";100));500;100));".";",")

[kroush]

Спасибо большое, работает. Поясните пожалуйста что значат символы - - вначале формулы.

[bigor]

- -

результатом работы  trim и substitute будет текст, похожий на числа. Нам же нужны дата (число) и сумма (число) вот это один из вариантов преобразования текста в число, можно заменить на value, но -- писать проще

[sokol92]

Нам же нужны дата (число) и сумма (число) вот это один из вариантов преобразования текста в число, можно заменить на value, но -- писать проще

Для тех, кто недавно перешел в Calc из Excel, необходимо дать пояснение.

В Excel указанное преобразование из текста в число (при помощи "--") будет работать при всех настройках.

В Calc, если речь идет о новом документе, работоспособность этого решения записит от настройки параметров: Меню / Сервис / Параметры / LibreOffice Calc / Формула / Подробные настройки вычислений.

При выборе стандартных настроек формула
=(--"0")
будет выдавать "#ЗНАЧ!". 

Если выбрать "Особые (преобразование текста в числа и прочее):" / "Подробности", то откроется диалог
"Подробные настройки вычислений". В нем для поля "Преобразование из текста в число есть следующие опции:

• Генерировать ошибку #ЗНАЧ!
• Считать нулём
• Преобразовывать только однозначные
• Преобразовывать по локальным параметрам

Только последняя опция совместима с Excel.

Исходя из сказанного, можно рекомендовать всё-таки применять функцию ЗНАЧ (русское наименование в Calc, она же VALUE). Это решение совместимо с Excel и будет работать как задумано при любых настройках.

[kroush]

Спасибо большое за разъяснения.

По ходу работы возник ещё вопрос. В Calc есть удобная функция данные - текст по столбцам. А существует ли похожая формула? К примеру, когда в документ производим выгрузку посредством макроса из нескольких других файлов и с помощью формул приводим к необходимым нам данным и получаем готовые данные. Но есть отчёты которые выгружается сплошным текстом в ячейку и у них разделители теста ;. Так вот чтобы не производить дополнительных действий можно ли с помощью формул или возможно подключения модуля с помощью макроса делать " текст по столбца" в автоматическом режиме.

[sokol92]

Статья от Microsoft здесь, применимо и к Calc.

В приложенном файле альтернативный подход с помощью UDF - функции, возвращающей (горизонтальный) массив. Напомню, что функции массива вводятся с помощью Ctrl+Shift+Enter.

Код Выделить Развернуть

Function TextByCol(s, delim)
  Dim arr, i As Long, arr2(1 to 1, 1 to 255)
  TextByCol=Array()
 
  arr=Split(s, delim)
  If UBound(arr)>=0 Then
    For i=0 To UBound(arr)
      arr2(1, i+1)=arr(i)
    Next i
    TextByCol=arr2
  End If   
 End Function


[bigor]

Только последняя опция совместима с Excel.

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

[bigor]

сплошным текстом в ячейку и у них разделители теста ;

в принципе это делают формулы из #21, они вытаскивают 5 и 6 позицию, меняя второй параметр mid() можно разобрать всю строку

[kroush]

Статья от Microsoft здесь, применимо и к Calc.

В приложенном файле альтернативный подход с помощью UDF - функции, возвращающей (горизонтальный) массив. Напомню, что функции массива вводятся с помощью Ctrl+Shift+Enter.

Код Выделить Развернуть

Function TextByCol(s, delim)
  Dim arr, i As Long, arr2(1 to 1, 1 to 255)
  TextByCol=Array()
 
  arr=Split(s, delim)
  If UBound(arr)>=0 Then
    For i=0 To UBound(arr)
      arr2(1, i+1)=arr(i)
    Next i
    TextByCol=arr2
  End If   
 End Function


Спасибо, все работает, Попробовал сам сделать через макрос.
 

Код Выделить Развернуть

sub Text ()
 oCdoc=thiscomponent
 oCsheet=oCdoc.sheets(0)
 Dim arr, i As Long, arr2(1 to 1, 1 to 10)
  stolbec=0
  oAsrange=oCsheet.getCellByposition (stolbec,0).string
  delim=";"
  TextByCol=Array()
  arr=Split(oAsrange, delim)
  If UBound(arr)>=0 Then
     For i=0 To UBound(arr)
       arr2(1, i+1)=arr(i)
    Next i
  OcrA=oCsheet.getCellRangeByposition (1,0, 10,0)
      ocrA.SetDataArray(Arr2)
  End If   
 end sub
 Появился еще вопрос, а как определить последнюю не пустую ячейку. Например: у нас есть строка А1:А10, вот как узнать что последняя будет А10. Желательно макросом, в принципе можно и формулой, а потом взять значение этой ячейки уже макросом. Хочу не вручную в этом макросе указать For i to 10000? А то вдруг будет не 10000, а 10001.
P.S. здесь я вручную указал что у нас данные переносятся на 10 столбцов, в принципе ничего критичного, но хотелось бы еще добавить определения размерности массива Arr2/
P.S.2 при написании макросов, можно ли в каждом отдельном взятом макросе использовать те же имена, если я им присвоил другие значения. И обратное: если в другом макросе уже присвоены какие то значения, сбросятся ли их определения как только перейдем работать в другой?
Например в одном из макросов определили последнюю не пустую ячейку, могу ли я из-него вытащить необходимое значение в другом макросе. Речь идет об одно и тоже книге.