/**
* Функция возвращает сумму ячеек в диапазоне с цветом фона равным color
* @param {Any[][]} range - Диапазон ячеек
* @param {String} color - цвет фона ячеек
* @return {Number} сумма ячеек в диапазоне с цветом фона равным color
*/
function sumByBackgroundColor(range, color) {
const values = range.getValues();
const backgrounds = range.getBackgrounds();
let sum = backgrounds.reduce((acc, row, ri) => {
return row.reduce((acc, c, ci) => {
if (c === color) {
return acc + values[ri][ci];
}
return acc;
}, acc);
}, 0);
return sum;
}
Public Function SumCellsByColor(rng As Range, clr As Long) As Double
Dim cell As Range
Dim colSum As Double
colSum = 0
For Each cell In rng
If cell.Interior.ColorIndex = clr Then
colSum = colSum + cell.Value
End If
Next cell
SumCellsByColor = colSum
End Function
$objPHPExcel = PHPExcel_IOFactory::load('myExcelFile.xlsx');
$objWorksheet = $objPHPExcel->getActiveSheet();
$lastRow = $objWorksheet->getHighestRow();
for ($row = 1; $row <= $lastRow; $row++) {
$currentValue = $objWorksheet->getCell('A' . $row)->getValue();
$nextValue = $objWorksheet->getCell('A' . ($row + 1))->getValue();
if ($currentValue != $nextValue) {
$objWorksheet->setCellValue('A' . $row, $currentValue . '_');
}
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('myExcelFile.xlsx');
Sub AddSharpToBoldText()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Font.Bold = True Then
cell.Value = "# " & cell.Value
End If
Next cell
End Sub
Sub AddMinusSignToRedText()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Font.Color = RGB(255, 0, 0) Then
cell.Value = "- " & cell.Value
End If
Next cell
End Sub
=ArrayFormula(LAMBDA(x;y;c;filter({x\y};НЕ(ЕНД(ПОИСКПОЗ(y;c;0)))))(A2:A;B2:B;E2:E))
=QUERY({Формула_1};"SELECT Col1,Count(Col1) GROUP BY Col1 PIVOT Col2")
=ArrayFormula(5000*LAMBDA(x;x/СУММ(x))(RANDARRAY(10)))
=ln(cos(A1-(ЦЕЛОЕ(A1/(2*ПИ())))*2*ПИ()))
3,14159265358979
\D на ""
(\d+)(\d{10})\n на +7$2\n