Группировка строк в таблице Excel по признаку в столбцах

Romnaro · 16 Дек 2014

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

<a href="http://gyazo.com/fb6617eed08a955755bcd46df41c3bf0"><img src="http://i.gyazo.com/fb6617eed08a955755bcd46df41c3bf0.png" /></a>

Количество и названия столбцов всегда одинаковое, а количество строк - разное.

Нужно суммировать значения ячеек в столбце value1 в тех строках ПОДРЯД, в которых флаги равны (значение ячейки в столбце flag равно значению ячейки flag в предыдущей строке).

Другими словами:

<a href="http://gyazo.com/c6cf82591917bc44d16a97771e18b05f"><img src="http://i.gyazo.com/c6cf82591917bc44d16a97771e18b05f.png" /></a>

переменные нарисовал только для примера, т.к. в общем, конечная цель - конвертировать эту таблицу в новую, более компактную. Такого вида:
<a href="http://gyazo.com/a2b65c1b163004382b10cb7342eae386"><img src="http://i.gyazo.com/a2b65c1b163004382b10cb7342eae386.png" /></a>

строки суммируются не только по 4 штуки, а так, как покажет flag. Например, по 3, 15 или по 1 (т.е если в столбце flag построчные значения идут как "2 0 2 0 " и тд, то ничего не суммируется, а просто четыре строки подряд переносятся в новую таблицу).

я начинающий, и ничего кроме неэффективного шаманства с FileReadLine придумать пока не могу
помогите, пожалуйста!

ildar · 17 Дек 2014

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

Romnaro · 17 Дек 2014

Во вложении слева - оригинал, а справа - результат. Как последнем скриншоте

ildar · 17 Дек 2014

я имел ввиду оригинал файла.

Romnaro · 17 Дек 2014

ildar сказал(а):
я имел ввиду оригинал файла.

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

ildar · 18 Дек 2014

Набросал на скорую руку

Код:

$sFilePath = @ScriptDir & '\исходная таблица.xls'
$oExcel = _ExcelBookOpen($sFilePath, 0)
$aArray = _ExcelReadSheetToArray($oExcel)
_ExcelBookClose($oExcel) ; Закрываем рабочую книгу
;_ArrayDisplay($aArray)
For $i = 4 to UBound($aArray)
	If $aArray[$i][2] = '' Then ExitLoop
Next
ReDim $aArray[$i][$aArray[0][1]]
Local $aArrayOUT[$i][$aArray[0][1]]
;_ArrayDisplay($aArray)
$iFlag = $aArray[4][6]
$iValue1 = 0
$iQuantity = 0
$iValue2 = 0
$iValue3 = 0
$k = 0
For $j = 4 to $i - 1
	If $aArray[$j][6] = $iFlag Then
			$iValue1 += $aArray[$j][4]
			$iQuantity += $aArray[$j][5]
			$iValue2 += $aArray[$j][7]
			$iValue3 += $aArray[$j][8]
			$aArrayOUT[$k][0] = $iValue1
			$aArrayOUT[$k][1] = $iQuantity
			$aArrayOUT[$k][2] = $iValue2
			$aArrayOUT[$k][3] = $iValue3
		Else
			$k += 1
			$iValue1 = $aArray[$j][4]
			$iQuantity = $aArray[$j][5]
			$iValue2 = $aArray[$j][7]
			$iValue3 = $aArray[$j][8]
			$iFlag = $aArray[$j][6]
			$aArrayOUT[$k][0] = $aArray[$j][4]
			$aArrayOUT[$k][1] = $aArray[$j][5]
			$aArrayOUT[$k][2] = $aArray[$j][7]
			$aArrayOUT[$k][3] = $aArray[$j][8]
		EndIf
Next
Redim $aArrayOUT[$k + 1][4]
_ArrayDisplay($aArrayOUT)

C2H5OH · 18 Дек 2014

Набросал на скорую руку

И что, работает? :shok:

Вот это

Код:

For $i = 4 to UBound($aArray)
    If $aArray[$i][2] = '' Then ExitLoop
Next

и потом ещё вот это

Код:

For $j = 4 to $i - 1

Хотя второе не криминал. Но первое это бомба, конечно.

Добавлено:
Сообщение автоматически объединено: 18 Дек 2014

Хотя...
Да, работать будет всегда правильно.
Но как-то очень уж "на грани".
ildar, ты или самоучка, или очень крут...

ildar · 18 Дек 2014

C2H5OH
Я же написал "на скорую руку". Проверено работает. "Бомба" для того чтобы убрать пустые строки массива, созданного путем считывания из xls-файла

Код:

$aArray = _ExcelReadSheetToArray($oExcel)

откуда они берутся, понятия не имею. Раньше при считывании с xls в массив такого не наблюдал.

OffTopic:
Крутой самоучка :laugh:

Romnaro · 21 Дек 2014

Привет
ildar, огромное спасибо!
но я, почему-то не могу запустить.

включил
#include <Array.au3>
#include <Excel.au3>

скрипт и экселевский файл в одной папке

пишет: : ==> Unknown function name.:
$aArray = _ExcelReadSheetToArray($oExcel)

про _Excel_BookOpen тоже самое было, но я глянул свою справку и исправил, как там - на _Excel_BookOpen
А функции _ExcelReadSheetToArray в моей справке вообще нет.
Подозреваю, проблема в версиях? или нужно включить #include в скрипт доп. файлы?

firex · 21 Дек 2014

C2H5OH

OffTopic:
Частенько использую подобные трюки с For, банальный пример на c++:

Код:

for (idx = 0; argptr[idx]; size++, idx++);

Бывают ситуации...

ildar · 21 Дек 2014

Тут есть.

Romnaro · 9 Янв 2015

Привет, извините, что долго не отвечал

ildar сказал(а):
Тут есть.

Большое спасибо. Правда, все равно почему-то не работает. В консоли пишет:

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (12) : ==> Can not redeclare a constant.:
Global Const $xlCalculationManual = -4135
Global Const ^ ERROR
>Exit code: 1 Time: 0.2963

что это может быть?

ildar · 13 Янв 2015

Romnaro
Весь код выложи, посмотреть.
В начале прописал?

Код:

#include <Excel.au3>
#include <Array.au3>

Romnaro · 14 Янв 2015

ildar,

ildar сказал(а):
Весь код выложи, посмотреть.
В начале прописал?

Код:

#include <Excel.au3> #include <Array.au3>

да, конечно.

В общем, пару раз сработало, но появилась новая ошибка. То работает, то не работает. Появляется такое сообщение:

Код:

==> "ReDim" used without an array variable.:
ReDim $aArray[$i][$aArray[0][1]]
ReDim ^ ERROR
>Exit code: 1    Time: 2.21

Код:

#include <Excel.au3>
#include <Array.au3>

$sFilePath = @ScriptDir & '\исходная таблица.xls'
$oExcel = _ExcelBookOpen($sFilePath, 0)
$aArray = _ExcelReadSheetToArray($oExcel)
_ExcelBookClose($oExcel) ; Закрываем рабочую книгу
;_ArrayDisplay($aArray)
For $i = 4 to UBound($aArray)
    If $aArray[$i][2] = '' Then ExitLoop
Next
ReDim $aArray[$i][$aArray[0][1]]
Local $aArrayOUT[$i][$aArray[0][1]]
;_ArrayDisplay($aArray)
$iFlag = $aArray[4][6]
$iValue1 = 0
$iQuantity = 0
$iValue2 = 0
$iValue3 = 0
$k = 0
For $j = 4 to $i - 1
    If $aArray[$j][6] = $iFlag Then
            $iValue1 += $aArray[$j][4]
            $iQuantity += $aArray[$j][5]
            $iValue2 += $aArray[$j][7]
            $iValue3 += $aArray[$j][8]
            $aArrayOUT[$k][0] = $iValue1
            $aArrayOUT[$k][1] = $iQuantity
            $aArrayOUT[$k][2] = $iValue2
            $aArrayOUT[$k][3] = $iValue3
        Else
            $k += 1
            $iValue1 = $aArray[$j][4]
            $iQuantity = $aArray[$j][5]
            $iValue2 = $aArray[$j][7]
            $iValue3 = $aArray[$j][8]
            $iFlag = $aArray[$j][6]
            $aArrayOUT[$k][0] = $aArray[$j][4]
            $aArrayOUT[$k][1] = $aArray[$j][5]
            $aArrayOUT[$k][2] = $aArray[$j][7]
            $aArrayOUT[$k][3] = $aArray[$j][8]
        EndIf
Next
Redim $aArrayOUT[$k + 1][4]
_ArrayDisplay($aArrayOUT)

Сейчас попробовал разные варианты и оказалось так: скрипт срабатывает 1-й раз. При повтором запуске выдает эту ошибку (см выше). Чтобы он запускался снова, нужно в диспетчере задач убить все процессы Excel, затем ОБЯЗАТЕЛЬНО открыть любой файл Excel, который выдаст предложение сохранить аварийно закрытые файлы - отказаться, закрыть Excel. После этого скрипт опять срабатывает, но снова только 1 раз

ildar · 14 Янв 2015

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

Код:

$sFilePath = @ScriptDir & '\исходная таблица.xls'
$oExcel = _ExcelBookOpen($sFilePath, 0)
$aArray = _ExcelReadSheetToArray($oExcel)
Sleep(5000)  ;<---------------------------------------------------------------------------тут
_ExcelBookClose($oExcel) ; Закрываем рабочую книгу

edyapd · 14 Янв 2015

Если ещё актуально.

Код:

#include <Excel.au3>
#include <Array.au3>

$sFilePath = @ScriptDir & '\исходная таблица.xls'
$oExcel = _ExcelBookOpen($sFilePath)

Dim $aValue[5][3] = [["flag", 0, 0],["value1", 0, 0],["quantity", 0, 0],["value2", 0, 0],["value3", 0, 0]]
$sFlag = "flag"
For $n = 0 To UBound($aValue) - 1
	$aRes = _ExcelSearch($oExcel, $aValue[$n][0])
	If Not IsArray($aRes) Then
		MsgBox(0, "", 'Не найден ' & $aValue[$n][0])
		Exit
	EndIf
	$aValue[$n][1] = $aRes[0]
	$aValue[$n][2] = $aRes[1]
Next
$n = $aValue[0][1] + 1
While _ExcelReadCell($oExcel, $n+1, $aValue[0][2]) <> -1
	If _ExcelReadCell($oExcel, $n, $aValue[0][2]) = _ExcelReadCell($oExcel, $n+1, $aValue[0][2]) Then
		For $i = 1 To UBound($aValue) - 1
			$iValue = _ExcelReadCell($oExcel, $n, $aValue[$i][2]) + _ExcelReadCell($oExcel, $n+1, $aValue[$i][2])
			_ExcelWriteCell($oExcel, $iValue, $n, $aValue[$i][2])
		Next
		_ExcelRowDelete($oExcel, $n+1)
		ContinueLoop
	EndIf
	$n += 1
WEnd

Func _ExcelSearch($oExcel, $sFlag)
	For $i = 1 To 20
		For $j = 1 To 20
			If _ExcelReadCell($oExcel, $i, $j) = $sFlag Then
				Dim $aRes[2]
				$aRes[0] = $i
				$aRes[1] = $j
				Return $aRes
			EndIf
		Next
	Next
	Return 0
EndFunc

Скипт, в отличии от ранее предложеного работает медлено. Убирает по 5 строк в секунду. Но если файлы не очень большие, то терпимо.

shyra1976 · 29 Янв 2015

edyapd сказал(а):

Если ещё актуально.

Код:

#include <Excel.au3>
#include <Array.au3>

$sFilePath = @ScriptDir & '\исходная таблица.xls'
$oExcel = _ExcelBookOpen($sFilePath)

Dim $aValue[5][3] = [["flag", 0, 0],["value1", 0, 0],["quantity", 0, 0],["value2", 0, 0],["value3", 0, 0]]
$sFlag = "flag"
For $n = 0 To UBound($aValue) - 1
	$aRes = _ExcelSearch($oExcel, $aValue[$n][0])
	If Not IsArray($aRes) Then
		MsgBox(0, "", 'Не найден ' & $aValue[$n][0])
		Exit
	EndIf
	$aValue[$n][1] = $aRes[0]
	$aValue[$n][2] = $aRes[1]
Next
$n = $aValue[0][1] + 1
While _ExcelReadCell($oExcel, $n+1, $aValue[0][2]) <> -1
	If _ExcelReadCell($oExcel, $n, $aValue[0][2]) = _ExcelReadCell($oExcel, $n+1, $aValue[0][2]) Then
		For $i = 1 To UBound($aValue) - 1
			$iValue = _ExcelReadCell($oExcel, $n, $aValue[$i][2]) + _ExcelReadCell($oExcel, $n+1, $aValue[$i][2])
			_ExcelWriteCell($oExcel, $iValue, $n, $aValue[$i][2])
		Next
		_ExcelRowDelete($oExcel, $n+1)
		ContinueLoop
	EndIf
	$n += 1
WEnd

Func _ExcelSearch($oExcel, $sFlag)
	For $i = 1 To 20
		For $j = 1 To 20
			If _ExcelReadCell($oExcel, $i, $j) = $sFlag Then
				Dim $aRes[2]
				$aRes[0] = $i
				$aRes[1] = $j
				Return $aRes
			EndIf
		Next
	Next
	Return 0
EndFunc

Скипт, в отличии от ранее предложеного работает медлено. Убирает по 5 строк в секунду. Но если файлы не очень большие, то терпимо.

что то у меня не работает без

Код:

Local $oAppl = _Excel_Open()
$sFilePath1 = "C:\Users\apougatc\Desktop\temp\Daily .xlsm"
 _Excel_BookOpen($oAppl,$sFilePath1,0)

vovsla · 7 Фев 2015

Есть функция которая считывает данные быстрее стандартной

Код:

;~ -----------------------------------------------------------------------------_ExcelReadSheetToArrayQuick----------------------------------------------------------------------------
Func _ExcelReadSheetToArrayQuick($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
    Local $avRET[1][2] = [[0, 0]] ; 2D return array
    ; Test inputs
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iStartRow < 1 Then Return SetError(2, 0, 0)
    If $iStartColumn < 1 Then Return SetError(2, 1, 0)
    If $iRowCnt < 0 Then Return SetError(3, 0, 0)
    If $iColCnt < 0 Then Return SetError(3, 1, 0)

    ; Get size of current sheet as R1C1 string
    ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    ;Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    Local $sLastCell = $oExcel.Activesheet.Cells.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1) ; изменено

    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]

    ; Return 0's if the sheet is blank
    If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

    ; Check input range is in bounds
    If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
    If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
    If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
    If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)

    ; Check for defaulted counts
    If $iRowCnt = 0 Then $iRowCnt = $iLastRow - $iStartRow + 1
    If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1

    ; Size the return array
    ReDim $avRET[$iRowCnt][$iColCnt]
    ;$avRET[0][0] = $iRowCnt
    ;$avRET[0][1] = $iColCnt
    $avRET = $oExcel.Application.WorksheetFunction.Transpose($oExcel.Activesheet.Cells($iStartRow, $iStartColumn).Resize($iRowCnt, $iColCnt).Value)
    ;Return data
    Return $avRET
EndFunc   ;==>_ExcelReadSheetFromArrayQuick

Добавлено:
Сообщение автоматически объединено: 7 Фев 2015

Если столбцы не меняются то можно сделать по-простому.
Хотя в результате появляется столбец с флагом, но его можно просто не использовать
И почему-то у меня неправильно считывается время...

Код:

#include <Array.au3>
#include <Excel.au3>

$oExcel=_ExcelBookOpen(@ScriptDir&'\исходная таблица.xls')
$Arr1=_ExcelReadSheetToArrayQuick($oExcel)
_ExcelBookClose($oExcel)

Dim $Arr2[2][8]
$Arr2[0][0]=1

For $Num=3 To UBound($Arr1)-1
	If $Arr1[$Num][2]='' Then ExitLoop

	If $Arr2[$Arr2[0][0]][5]<>$Arr1[$Num][5] Then
		$Arr2[0][0]+=1
		ReDim $Arr2[$Arr2[0][0]+1][8]
		$Arr2[$Arr2[0][0]][1]=$Arr1[$Num][1]
		$Arr2[$Arr2[0][0]][2]=$Arr1[$Num][2]
		$Arr2[$Arr2[0][0]][3]=$Arr1[$Num][3]
		$Arr2[$Arr2[0][0]][4]=$Arr1[$Num][4]
		$Arr2[$Arr2[0][0]][5]=$Arr1[$Num][5]
		$Arr2[$Arr2[0][0]][6]=$Arr1[$Num][6]
		$Arr2[$Arr2[0][0]][7]=$Arr1[$Num][7]
	Else
		$Arr2[$Arr2[0][0]][1]=$Arr1[$Num][1]
		$Arr2[$Arr2[0][0]][2]=$Arr1[$Num][2]
		$Arr2[$Arr2[0][0]][3]=$Arr2[$Arr2[0][0]][3]+$Arr1[$Num][3]
		$Arr2[$Arr2[0][0]][4]=$Arr2[$Arr2[0][0]][4]+$Arr1[$Num][4]
		$Arr2[$Arr2[0][0]][5]=$Arr1[$Num][5]
		$Arr2[$Arr2[0][0]][6]=$Arr2[$Arr2[0][0]][6]+$Arr1[$Num][6]
		$Arr2[$Arr2[0][0]][7]=$Arr2[$Arr2[0][0]][7]+$Arr1[$Num][7]
	EndIf
Next


_ArrayDisplay($Arr2)


;~ -----------------------------------------------------------------------------_ExcelReadSheetToArrayQuick----------------------------------------------------------------------------
Func _ExcelReadSheetToArrayQuick($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
    Local $avRET[1][2] = [[0, 0]] ; 2D return array
    ; Test inputs
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iStartRow < 1 Then Return SetError(2, 0, 0)
    If $iStartColumn < 1 Then Return SetError(2, 1, 0)
    If $iRowCnt < 0 Then Return SetError(3, 0, 0)
    If $iColCnt < 0 Then Return SetError(3, 1, 0)

    ; Get size of current sheet as R1C1 string
    ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    ;Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    Local $sLastCell = $oExcel.Activesheet.Cells.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1) ; изменено

    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]

    ; Return 0's if the sheet is blank
    If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

    ; Check input range is in bounds
    If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
    If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
    If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
    If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)

    ; Check for defaulted counts
    If $iRowCnt = 0 Then $iRowCnt = $iLastRow - $iStartRow + 1
    If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1

    ; Size the return array
    ReDim $avRET[$iRowCnt][$iColCnt]
    ;$avRET[0][0] = $iRowCnt
    ;$avRET[0][1] = $iColCnt
    $avRET = $oExcel.Application.WorksheetFunction.Transpose($oExcel.Activesheet.Cells($iStartRow, $iStartColumn).Resize($iRowCnt, $iColCnt).Value)
    ;Return data
    Return $avRET
EndFunc   ;==>_ExcelReadSheetFromArrayQuick

Автор темы	Похожие темы	Форум	Ответы	Дата
A	Из ресурсов исполняемых файлов получить данные в виде строки-структуры	Общие вопросы по AutoIt	4	12 Апр 2024
A	Из бинарной строки получить число с плавающей точкой	Общие вопросы по AutoIt	0	12 Апр 2024
B	Чтение последнего символа строки	Общие вопросы по AutoIt	3	22 Янв 2024
P	Удаление всех строк между двумя известными	Общие вопросы по AutoIt	2	18 Дек 2023
J	Группировка множественных условий в IF	Общие вопросы по AutoIt	7	4 Окт 2022

Группировка строк в таблице Excel по признаку в столбцах

Новичок

Вложения

Осваивающий

Новичок

Осваивающий

Новичок

Вложения

Осваивающий

AutoIT Гуру

Осваивающий

Новичок

AutoIT Гуру

Осваивающий

Вложения

Новичок

Осваивающий

Новичок

Осваивающий

Осваивающий

Новичок

Осваивающий