From bfb4c58ae708c75949559290bdfdd9afcef6aa91 Mon Sep 17 00:00:00 2001 From: Balazs Varga Date: Wed, 20 Mar 2024 01:49:58 +0100 Subject: tdf#126573 Add Excel2021 array function SORTBY to Calc TODO/WIP: oasis proposal More information about how this new function works: https://support.microsoft.com/en-au/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f https://exceljet.net/functions/sortby-function Change-Id: I4538a32f7f75056d3055369fc5f4483d24fd1089 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/165069 Tested-by: Jenkins Tested-by: Gabor Kelemen Reviewed-by: Balazs Varga --- formula/inc/core_resource.hrc | 6 + formula/source/core/api/FormulaCompiler.cxx | 1 + include/formula/compiler.hxx | 3 +- include/formula/opcode.hxx | 2 + sc/README.md | 1 + sc/inc/helpids.h | 1 + sc/inc/scfuncs.hrc | 12 + sc/qa/extras/scfunctionlistobj.cxx | 2 +- .../data/functions/spreadsheet/fods/sortby.fods | 5743 ++++++++++++++++++++ sc/qa/unit/ucalc.cxx | 1 + sc/source/core/data/funcdesc.cxx | 1 + sc/source/core/inc/interpre.hxx | 3 + sc/source/core/tool/interpr1.cxx | 235 +- sc/source/core/tool/interpr3.cxx | 69 + sc/source/core/tool/interpr4.cxx | 1 + sc/source/core/tool/parclass.cxx | 1 + sc/source/core/tool/token.cxx | 1 + sc/source/filter/excel/xlformula.cxx | 3 +- sc/source/filter/oox/formulabase.cxx | 3 +- 19 files changed, 6045 insertions(+), 44 deletions(-) create mode 100644 sc/qa/unit/data/functions/spreadsheet/fods/sortby.fods diff --git a/formula/inc/core_resource.hrc b/formula/inc/core_resource.hrc index 3d4e822353ef..82d3a52c98e8 100644 --- a/formula/inc/core_resource.hrc +++ b/formula/inc/core_resource.hrc @@ -280,6 +280,7 @@ const std::pair RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF[] = { "HLOOKUP" , SC_OPCODE_H_LOOKUP }, { "COM.MICROSOFT.FILTER" , SC_OPCODE_FILTER }, { "COM.MICROSOFT.SORT" , SC_OPCODE_SORT }, + { "COM.MICROSOFT.SORTBY" , SC_OPCODE_SORTBY }, { "ORG.OPENOFFICE.MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy for range list (union) { "OFFSET" , SC_OPCODE_OFFSET }, { "INDEX" , SC_OPCODE_INDEX }, @@ -731,6 +732,7 @@ const std::pair RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML[] = { "HLOOKUP" , SC_OPCODE_H_LOOKUP }, { "_xlfn._xlws.FILTER" , SC_OPCODE_FILTER }, { "_xlfn._xlws.SORT" , SC_OPCODE_SORT }, + { "_xlfn.SORTBY" , SC_OPCODE_SORTBY }, { "_xlfn.ORG.OPENOFFICE.MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy for range list (union) { "OFFSET" , SC_OPCODE_OFFSET }, { "INDEX" , SC_OPCODE_INDEX }, @@ -1185,6 +1187,7 @@ const std::pair RID_STRLIST_FUNCTION_NAMES_ENGLISH_PODF[] = { "HLOOKUP" , SC_OPCODE_H_LOOKUP }, { "FILTER" , SC_OPCODE_FILTER }, { "SORT" , SC_OPCODE_SORT }, + { "SORTBY" , SC_OPCODE_SORTBY }, { "MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy for range list (union) { "OFFSET" , SC_OPCODE_OFFSET }, { "INDEX" , SC_OPCODE_INDEX }, @@ -1640,6 +1643,7 @@ const std::pair RID_STRLIST_FUNCTION_NAMES_ENGLISH_API[] = { "HLOOKUP" , SC_OPCODE_H_LOOKUP }, { "FILTER" , SC_OPCODE_FILTER }, { "SORT" , SC_OPCODE_SORT }, + { "SORTBY" , SC_OPCODE_SORTBY }, { "MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy for range list (union) { "OFFSET" , SC_OPCODE_OFFSET }, { "INDEX" , SC_OPCODE_INDEX }, // ?? first character = I ?? @@ -2093,6 +2097,7 @@ const std::pair RID_STRLIST_FUNCTION_NAMES_ENGLISH[] = { "HLOOKUP" , SC_OPCODE_H_LOOKUP }, { "FILTER" , SC_OPCODE_FILTER }, { "SORT" , SC_OPCODE_SORT }, + { "SORTBY" , SC_OPCODE_SORTBY }, { "MULTIRANGE" , SC_OPCODE_MULTI_AREA }, { "OFFSET" , SC_OPCODE_OFFSET }, { "INDEX" , SC_OPCODE_INDEX }, @@ -2527,6 +2532,7 @@ const std::pair RID_STRLIST_FUNCTION_NAMES[] = { NC_("RID_STRLIST_FUNCTION_NAMES", "HLOOKUP") , SC_OPCODE_H_LOOKUP }, { NC_("RID_STRLIST_FUNCTION_NAMES", "FILTER") , SC_OPCODE_FILTER }, { NC_("RID_STRLIST_FUNCTION_NAMES", "SORT") , SC_OPCODE_SORT }, + { NC_("RID_STRLIST_FUNCTION_NAMES", "SORTBY") , SC_OPCODE_SORTBY }, { NC_("RID_STRLIST_FUNCTION_NAMES", "MULTIRANGE") , SC_OPCODE_MULTI_AREA }, // legacy for range list (union) { NC_("RID_STRLIST_FUNCTION_NAMES", "OFFSET") , SC_OPCODE_OFFSET }, { NC_("RID_STRLIST_FUNCTION_NAMES", "INDEX") , SC_OPCODE_INDEX }, // ?? first character = I ?? diff --git a/formula/source/core/api/FormulaCompiler.cxx b/formula/source/core/api/FormulaCompiler.cxx index d5d926f6f4be..278628cbd648 100644 --- a/formula/source/core/api/FormulaCompiler.cxx +++ b/formula/source/core/api/FormulaCompiler.cxx @@ -1232,6 +1232,7 @@ bool FormulaCompiler::IsMatrixFunction( OpCode eOpCode ) case ocFourier : case ocFilter : case ocSort : + case ocSortBy : return true; default: { diff --git a/include/formula/compiler.hxx b/include/formula/compiler.hxx index c004a12cb39b..c7501da8c421 100644 --- a/include/formula/compiler.hxx +++ b/include/formula/compiler.hxx @@ -512,7 +512,8 @@ #define SC_OPCODE_RANDBETWEEN_NV 499 #define SC_OPCODE_FILTER 500 #define SC_OPCODE_SORT 501 -#define SC_OPCODE_STOP_2_PAR 502 /* last function with two or more parameters' OpCode + 1 */ +#define SC_OPCODE_SORTBY 502 +#define SC_OPCODE_STOP_2_PAR 503 /* last function with two or more parameters' OpCode + 1 */ #define SC_OPCODE_STOP_FUNCTION SC_OPCODE_STOP_2_PAR /* last function's OpCode + 1 */ #define SC_OPCODE_LAST_OPCODE_ID (SC_OPCODE_STOP_FUNCTION - 1) /* last OpCode */ diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx index 59ee8c5bd6b9..eda50add2361 100644 --- a/include/formula/opcode.hxx +++ b/include/formula/opcode.hxx @@ -508,6 +508,7 @@ enum OpCode : sal_uInt16 ocRandbetweenNV = SC_OPCODE_RANDBETWEEN_NV, ocFilter = SC_OPCODE_FILTER, ocSort = SC_OPCODE_SORT, + ocSortBy = SC_OPCODE_SORTBY, // internal stuff ocInternalBegin = SC_OPCODE_INTERNAL_BEGIN, ocTTT = SC_OPCODE_TTT, @@ -988,6 +989,7 @@ inline std::string OpCodeEnumToString(OpCode eCode) case ocRandbetweenNV: return "RandbetweenNV"; case ocFilter: return "Filter"; case ocSort: return "Sort"; + case ocSortBy: return "SortBy"; case ocTTT: return "TTT"; case ocDebugVar: return "DebugVar"; case ocDataToken1: return "DataToken1"; diff --git a/sc/README.md b/sc/README.md index e751b9368149..6e7d9c0ac35c 100644 --- a/sc/README.md +++ b/sc/README.md @@ -79,6 +79,7 @@ https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocume * XLOOKUP * FILTER * SORT + * SORTBY * Mathematical Functions * SUMIF * SUMIFS diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h index 0d6d3303ba73..0e8ec8dc851d 100644 --- a/sc/inc/helpids.h +++ b/sc/inc/helpids.h @@ -597,5 +597,6 @@ inline constexpr OUString HID_FUNC_XLOOKUP_MS = u"SC_HID_FUNC_XLOOKUP_MS"_ustr; inline constexpr OUString HID_FUNC_XMATCH_MS = u"SC_HID_FUNC_XMATCH_MS"_ustr; inline constexpr OUString HID_FUNC_FILTER_MS = u"SC_HID_FUNC_FILTER_MS"_ustr; inline constexpr OUString HID_FUNC_SORT_MS = u"SC_HID_FUNC_SORT_MS"_ustr; +inline constexpr OUString HID_FUNC_SORTBY_MS = u"SC_HID_FUNC_SORTBY_MS"_ustr; /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/inc/scfuncs.hrc b/sc/inc/scfuncs.hrc index 5f612b957ce9..9162a2fbafac 100644 --- a/sc/inc/scfuncs.hrc +++ b/sc/inc/scfuncs.hrc @@ -4209,4 +4209,16 @@ const TranslateId SC_OPCODE_SORT_ARY[] = NC_("SC_OPCODE_SORT", "A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column.") }; +// -=*# Resource for function SORTBY #*=- +const TranslateId SC_OPCODE_SORTBY_ARY[] = +{ + NC_("SC_OPCODE_SORTBY", "Sorts the contents of a range or array based on the values in a corresponding range or array."), + NC_("SC_OPCODE_SORTBY", "Sort range"), + NC_("SC_OPCODE_SORTBY", "The array or range to sort."), + NC_("SC_OPCODE_SORTBY", "Sort by range "), + NC_("SC_OPCODE_SORTBY", "Range 1, range 2,... are the arrays or ranges to sort on."), + NC_("SC_OPCODE_SORTBY", "Sort order "), + NC_("SC_OPCODE_SORTBY", "Order 1, order 2,... are the orders to use for sorting. 1 for ascending, -1 for descending. Default is ascending.") +}; + /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/qa/extras/scfunctionlistobj.cxx b/sc/qa/extras/scfunctionlistobj.cxx index bdb01ab24e56..e0fa95b679b7 100644 --- a/sc/qa/extras/scfunctionlistobj.cxx +++ b/sc/qa/extras/scfunctionlistobj.cxx @@ -77,7 +77,7 @@ public: ScFunctionListObj::ScFunctionListObj() : UnoApiTest("/sc/qa/extras/testdocuments") , XElementAccess(cppu::UnoType>::get()) - , XIndexAccess(399) + , XIndexAccess(400) , XNameAccess("IF") , XServiceInfo("stardiv.StarCalc.ScFunctionListObj", "com.sun.star.sheet.FunctionDescriptions") { diff --git a/sc/qa/unit/data/functions/spreadsheet/fods/sortby.fods b/sc/qa/unit/data/functions/spreadsheet/fods/sortby.fods new file mode 100644 index 000000000000..1b9d8a013881 --- /dev/null +++ b/sc/qa/unit/data/functions/spreadsheet/fods/sortby.fods @@ -0,0 +1,5743 @@ + + + + 2024-01-16T18:30:06.278000000PT5H32M16S109LibreOfficeDev/24.8.0.0.alpha0$Windows_X86_64 LibreOffice_project/6a201e13b12259100fe92d4925d2ea9cc9abf6e72024-03-21T12:50:29.932000000 + + + 0 + 0 + 59461 + 40947 + + + view1 + + + 2 + 0 + 2 + 0 + 0 + 0 + 0 + 0 + 85 + 60 + true + false + false + false + false + + + 16 + 29 + 2 + 0 + 0 + 0 + 0 + 0 + 85 + 60 + true + false + false + false + false + + + Sheet1 + 2070 + 0 + 85 + 60 + false + true + true + false + true + 12632256 + true + 1 + true + true + false + false + false + 1270 + 1270 + 1 + 1 + true + false + false + false + false + + + + + true + true + true + 0 + true + true + false + true + false + + + en + US + + + + + + 12632256 + true + true + 0 + false + false + true + true + false + 3 + false + Microsoft Print to PDF + false + ZBb+/01pY3Jvc29mdCBQcmludCB0byBQREYAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAATWljcm9zb2Z0IFByaW50IFRvIFBERgAAAAAAAAAAAAAWAAEANhUAAAAAAAAEAAhSAAAEdAAAM1ROVwAAAAAKAE0AaQBjAHIAbwBzAG8AZgB0ACAAUAByAGkAbgB0ACAAdABvACAAUABEAEYAAAAAAAAAAAAAAAAAAAAAAAAAAAABBAMG3ABQFAMvAQABAAkAmgs0CGQAAQAPAFgCAgABAFgCAwABAEEANAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAAAAAAAAQAAAAIAAAABAAAA/////0dJUzQAAAAAAAAAAAAAAABESU5VIgDIACQDLBE/XXt+AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAUAAAAAAAUAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAyAAAAFNNVEoAAAAAEAC4AHsAMAA4ADQARgAwADEARgBBAC0ARQA2ADMANAAtADQARAA3ADcALQA4ADMARQBFAC0AMAA3ADQAOAAxADcAQwAwADMANQA4ADEAfQAAAFJFU0RMTABVbmlyZXNETEwAUGFwZXJTaXplAEE0AE9yaWVudGF0aW9uAFBPUlRSQUlUAFJlc29sdXRpb24AUmVzT3B0aW9uMQBDb2xvck1vZGUAQ29sb3IAAAAAAAAAAAAAAAAAAAAAAAAsEQAAVjRETQEAAAAAAAAAnApwIhwAAADsAAAAAwAAAPoBTwg05ndNg+4HSBfANYHQAAAATAAAAAMAAAAACAAAAAAAAAAAAAADAAAAAAgAACoAAAAACAAAAwAAAEAAAABWAAAAABAAAEQAbwBjAHUAbQBlAG4AdABVAHMAZQByAFAAYQBzAHMAdwBvAHIAZAAAAEQAbwBjAHUAbQBlAG4AdABPAHcAbgBlAHIAUABhAHMAcwB3AG8AcgBkAAAARABvAGMAdQBtAGUAbgB0AEMAcgB5AHAAdABTAGUAYwB1AHIAaQB0AHkAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEgBDT01QQVRfRFVQTEVYX01PREUTAER1cGxleE1vZGU6OlVua25vd24MAFBSSU5URVJfTkFNRRYATWljcm9zb2Z0IFByaW50IHRvIFBERgsARFJJVkVSX05BTUUWAE1pY3Jvc29mdCBQcmludCBUbyBQREY= + false + 1270 + 1270 + 1 + 1 + true + false + false + true + true + true + true + 7 + true + + + Sheet1 + + + Sheet2 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Kč + + + + - + + + Kč + + + + \ + + + + + \- + + + + + + % + + + \ + + + + + + \ + + - + + + + + \ + + - + + + + + + + + + + £ + + + + + - + £ + + + + + + + + $ + + + + + + $ + + ( + + ) + + + $ + + - + + + + + + + + + + + + + . + + . + + + + + + + + + + + + ( + + ) + + + + € + + + + + € + - + + + + + $ + + + + + ($ + + ) + + + + + + + + + + + + ( + + ) + + + - + + + + + + + + + + ⌀ + + + + + € + + + - + + € + + + - € + + + + + + + + + + + + + + + + € + + + + + + € + + + - + + + + + - + + - + + + + £ + + + + - + £ + + + + + + - + + + + + + + + + + + + + + + + - + + + + + + - + + + + + + + + + + + + € + + + + + + + ( + € + + + ) + + + + + + + + + + $ + + + + + + + + ( + + ) + + + + + + Kč + + + + - + + + Kč + + + + $ + + + + + - + $ + + + + + + + + + + + - + + + + + + - + + + + + + + + + + + + Kč + + + + - + + + Kč + + + + Yes + + + Yes + + + No + + + + + + + + + + + + + EUR + + + + - + + + EUR + + + + + € + + + - + + € + + + + \ + + + + \- + + + + + \ + + + + + + \ + + - + + + + + \ + + - + + + + + + + + + + + + + + + + + + + + + + + + + + + ( + + ) + + + + - + + + + + + + + + + £ + + + + - + £ + + + + + + + + + + + + + EUR + + + + - + + + EUR + + + + + € + + + + - + + € + + + + + - + + - + + + + $ + + + + + + $ + + ( + + ) + + + $ + + - + + + + + + + + + + $ + + + + + $( + + ) + + + $- + + + + + + + + + + + + + + + + £ + + + + + - + £ + + + + + + On + + + On + + + Off + + + + + $ + + + + + + ($ + + ) + + + + + € + + + - + + € + + + + + + + $ + + + + + ($ + + ) + + + + $ + + + + + + ($ + + ) + + + + + + + + + + + + + ( + + ) + + + + - + + + + + + + + + + + + + + Kč + + + - + + + Kč + + + + + + + True + + + True + + + False + + + + + + € + + + - + + € + + + - + + € + + + + + + + + + + + + + + + + + - + + + + + - + + + + + + + + + + + / + + + + £ + + + + + - + £ + + + + + £ + + + + + - + £ + + + + + $ + + + + + $( + + ) + + + $- + + + + + + + + + + + + + + + + - + + + + + - + + + + + + + + + + + + WAHR + + + WAHR + + + FALSCH + + + + + + . + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + + + + + + ( + + ) + + + - + + + + + + + + + + + + + : + + + + + : + + + + £ + + + + + - + £ + + + + + + \ + + + + + \- + + + + + € + + + + + € + - + + + + + + + € + + + + - + + + € + + + + + + + + + + + € + + + + - + + € + + + + + + Kč + + + - + + + Kč + + + + + : + + : + + + + \ + + + + \- + + + + + + + + + - + + + € + + + + + + + / + + / + + + + + + + / + + / + + + + € + + + + + + - + € + + + + + + + Kč + + + - + + Kč + + + + + Kč + + + + - + + Kč + + + + + Kč + + + - + + Kč + + + + + Kč + + + + - + + Kč + + + + + + + - + + + + + + + + + - + + + + + + + + - + + + + + + + + + - + + + + + + + + + + - + + + + + + + - + + + + + + + + + + + + Kč + + + - + + + Kč + + + + - Kč + + + + + + + + + + + + + + + - + + + + + + + - + + + + + + + + + + + + + + Kč + + + - + + + Kč + + + + - + + Kč + + + + + + + + + + ¥€ + + + + + + + + + ( + € + + + + + ) + + + + + + + + - + + + + + + + + + + + - + + + + + + + + + + - + + + + + + + + + + + - + + + + + + + % + + + + % + + + + % + + + + % + + + + % + + + + % + + + + % + + + + % + + + + % + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + + € + + + + - + + + € + + + + + % + + + + % + + + + % + + + + % + + + + % + + + + % + + + + + + $ + + + + + ( + $ + + ) + + + + + % + + + + % + + + + - + + % + + + + + + + + - + + + + + + - + + - + + + + £ + + + + + - + £ + + + + + £ + + + + + - + £ + + + + + + % + + + + - + + % + + + + + DM + + + - + + DM + + + + + DM + + + + - + + DM + + + + + DM + + + - + + DM + + + + + DM + + + + - + + DM + + + + + + + + + + - + + + + + + + + - + + + + + + + + + + + + + + DM + + + - + + + DM + + + + + - DM + + + + + + + + + + + + + + + + + - + + + + + + + + - + + + + + + + + + + + + + + + + DM + + + - + + + DM + + + + + - + + DM + + + + + + + + + + + Ouch! - + + - Error detected! + + + + - Result=0 - No Errordetection + + + + / + + / + + + + + / + + / + + + + + + € + + + + - + + + € + + + + + . + + . + + + + + + + + + + - + + + + + + + + - + + + + + + + + + + + + + + € + + + - + + + € + + + + + - € + + + + + + + + + + + + + + + + + - + + + + + + + + - + + + + + + + + + + + + + + + + € + + + - + + + € + + + + + - + + € + + + + + + + + + + + $ + + + + $ + + + + + - + $ + + + + + + . + + . + + + + + . + + + + + + + . + + + + + + + + + + : + + + + + + + : + + : + + + + + + + . + + . + + + + : + + + + + + + + - + + + + + + + + + + + - + + + + + + + + + + - + + + + + + + + + + + - + + + + + + $ + + + + $ + + + + -$ + + + + + $ + + + + + -$ + + + + + $ + + + + -$ + + + + + $ + + + + + -$ + + + + + -$ + + + + $ + + + + -$ + + + + $- + + + + + - + + + + + + + - + + + + - + + + + + -$ + + + + $ + + + + -$ + + + + $- + + + + + + - + + + + + + + - + + + + - + + + + + + + Ft + + + - + + Ft + + + + + Ft + + + + - + + Ft + + + + + Ft + + + - + + Ft + + + + + Ft + + + + - + + Ft + + + + + . + + . + + + + + . + + . + + + + + . + + + + + . + + + + + . + + . + + + + : + + + + + + + + - + + + + + + + + + + + - + + + + + + + + + + - + + + + + + + + + + + - + + + + + + + + + + + + - + + + + + + + + - + + + + + + + + + + + + + + Ft + + + - + + + Ft + + + + + - Ft + + + + + + + + + + + + + + + + + - + + + + + + + + - + + + + + + + + + + + + + + + + Ft + + + - + + + Ft + + + + + - + + Ft + + + + + + + + + + + + / + + / + + + + + - + + - + + + + + - + + + + + - + + + + + : + + + + + + + : + + : + + + + + + + : + + + + + : + + : + + + + + / + + / + + + + : + + + + + + + + + ( + + ) + + + + + + + + + ( + + ) + + + + + + + + + ( + + + ) + + + + + + + + + + ( + + + ) + + + + + + + + + ( + + + ) + + + + + + + + + + ( + + + ) + + + + + + + + ( + + ) + + + + + + + + ( + + ) + + + + + + + + + + ( + + ) + + + + - + + + + + + + + + + + + + + + + + + ( + + ) + + + + + - + + + + + + + + + + + + + + + + ( + + ) + + + + - + + + + + + + + + + + + + + + + + + + + ( + + ) + + + + + - + + + + + + + + + + + + + + + + + + + - + + + + + : + + : + + + + + : + + + + + + + + % + + + + + + + . + + + + + + + . + + + + + + + + + + : + + + + + + + : + + : + + + + + + + + + + - + + + + + + + + + + + - + + + + + + + + + + - + + + + + + + + + + + - + + + + + + + € + + + - + + € + + + + + € + + + + - + + € + + + + + € + + + - + + € + + + + + € + + + + - + + € + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + ??? + + + + + Page 1 + + + + + + + + ???(???) + + + 0000.00.00, 00:00:00 + + + + + + Page 1/ 99 + + + + + + + + + + + + + + + SORTBY Function + + + + + + + + + + + + Result + + + IGAZ + + + + + + + + + Sheet + + + Result + + + Description + + + + + 2 + + + IGAZ + + + Simple SORTBY formulas with local references and values + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Function + + + + Expected + + + + Correct + + + FunctionString + + + Comment + + + XL-test + + + + + + + + + + + + + Range Sort by Rows + + + + + + Sort + + + + + + Region + + + Name + + + Age + + + + Region + + + Name + + + Age + + + + + + Region + + + Name + + + Age + + + + + b + + + + + + East + + + Tom + + + 52 + + + + East + + + Tom + + + 52 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + East + + + Tom + + + 52 + + + + d + + + + + + + East + + + Fritz + + + 19 + + + + East + + + Fritz + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + West + + + Fred + + + 65 + + + + u + + + + + + North + + + Amy + + + 22 + + + + North + + + Amy + + + 22 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + North + + + Amy + + + 22 + + + + o + + + + + + North + + + Xi + + + 19 + + + + North + + + Xi + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + South + + + Sal + + + 73 + + + + + k + + + + + + South + + + Sal + + + 73 + + + + South + + + Sal + + + 73 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + East + + + Fritz + + + 19 + + + + + 2 + + + + + + South + + + Hector + + + 66 + + + + South + + + Hector + + + 66 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + West + + + Srivan + + + 39 + + + + + 7 + + + + + + West + + + Fred + + + 65 + + + + West + + + Fred + + + 65 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + North + + + Xi + + + 19 + + + + + -1 + + + + + + West + + + Srivan + + + 39 + + + + West + + + Srivan + + + 39 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;N$4:N$11;1;P$4:P$11;-1)} + + + + South + + + Hector + + + 66 + + + + + + + + + + + + Z + + + + + + Region + + + Name + + + Age + + + + Region + + + Name + + + Age + + + + + + Region + + + East + + + West + + + North + + + South + + + + Q + + + + + + East + + + Tom + + + 52 + + + + East + + + Tom + + + 52 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + Name + + + Tom + + + Fred + + + Amy + + + Sal + + + + + + East + + + Fritz + + + 19 + + + + East + + + Fritz + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + Age + + + 52 + + + 65 + + + 22 + + + 73 + + + + + + North + + + Amy + + + 22 + + + + North + + + Amy + + + 22 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + + + + + North + + + Xi + + + 19 + + + + North + + + Xi + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + + + South + + + Sal + + + 73 + + + + South + + + Sal + + + 73 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + + + + Region + + + + + + South + + + Hector + + + 66 + + + + South + + + Hector + + + 66 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + + + East + + + + + + West + + + Fred + + + 65 + + + + West + + + Fred + + + 65 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + West + + + + + + West + + + Srivan + + + 39 + + + + West + + + Srivan + + + 39 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$19:S$26;1;T$31:T$38;-1)} + + + + North + + + + + + + + + + + + South + + + + + + Region + + + Name + + + Age + + + + Region + + + Name + + + Age + + + + + + + + East + + + + + + South + + + Hector + + + 66 + + + + South + + + Hector + + + 66 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + West + + + + + + West + + + Srivan + + + 39 + + + + West + + + Srivan + + + 39 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + North + + + + + + North + + + Xi + + + 19 + + + + North + + + Xi + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + South + + + + + + East + + + Tom + + + 52 + + + + East + + + Tom + + + 52 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + + + West + + + Fred + + + 65 + + + + West + + + Fred + + + 65 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + + + East + + + Fritz + + + 19 + + + + East + + + Fritz + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + + + South + + + Sal + + + 73 + + + + South + + + Sal + + + 73 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + Age + + + + + + North + + + Amy + + + 22 + + + + North + + + Amy + + + 22 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$3:S$10)} + + + + + + 52 + + + + + + + + + + + + 65 + + + + + + Region + + + Name + + + Age + + + + Region + + + Name + + + Age + + + + + + + + 22 + + + + + + South + + + Hector + + + 66 + + + + South + + + Hector + + + 66 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + 73 + + + + + + North + + + Xi + + + 19 + + + + North + + + Xi + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + 19 + + + + + + West + + + Srivan + + + 39 + + + + West + + + Srivan + + + 39 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + + 39 + + + + + + East + + + Fritz + + + 19 + + + + East + + + Fritz + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + + 19 + + + + + + South + + + Sal + + + 73 + + + + South + + + Sal + + + 73 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + + 66 + + + + + + North + + + Amy + + + 22 + + + + North + + + Amy + + + 22 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + + West + + + Fred + + + 65 + + + + West + + + Fred + + + 65 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + East + + + Tom + + + 52 + + + + East + + + Tom + + + 52 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{8;7;6;5;4;3;2;1};1)} + + + + + + + + + + + Region + + + Name + + + Age + + + + Region + + + Name + + + Age + + + + + + Name + + + Tom + + + Fred + + + Amy + + + Sal + + + + + + South + + + Hector + + + 66 + + + + South + + + Hector + + + 66 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + North + + + Xi + + + 19 + + + + North + + + Xi + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + West + + + Srivan + + + 39 + + + + West + + + Srivan + + + 39 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + East + + + Fritz + + + 19 + + + + East + + + Fritz + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + South + + + Sal + + + 73 + + + + South + + + Sal + + + 73 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + North + + + Amy + + + 22 + + + + North + + + Amy + + + 22 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + West + + + Fred + + + 65 + + + + West + + + Fred + + + 65 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + + East + + + Tom + + + 52 + + + + East + + + Tom + + + 52 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{1;2;3;4;5;6;7;8};-1)} + + + + + + + + + + + Region + + + Name + + + Age + + + + Region + + + Name + + + Age + + + + + + + + West + + + Fred + + + 65 + + + + West + + + Fred + + + 65 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + South + + + Hector + + + 66 + + + + South + + + Hector + + + 66 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + East + + + Fritz + + + 19 + + + + East + + + Fritz + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + West + + + Srivan + + + 39 + + + + West + + + Srivan + + + 39 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + North + + + Xi + + + 19 + + + + North + + + Xi + + + 19 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + North + + + Amy + + + 22 + + + + North + + + Amy + + + 22 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + East + + + Tom + + + 52 + + + + East + + + Tom + + + 52 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + South + + + Sal + + + 73 + + + + South + + + Sal + + + 73 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;{0;"t";5;-1;"p";9;7;"q"};-1)} + + + + + + + + + + + Region + + + Name + + + Age + + + + + + + + Err:504 + + + + IGAZ + + + {=SORTBY($N$4:$P$11;S$12:S$13)} + + + + + + + + + + + Region + + + Name + + + Age + + + + + + + + Err:504 + + + + IGAZ + + + {=SORTBY($N$4:$P$7;$N15:$Q15)} + + + + + + + + Range Sort by Columns + + + + + + + + + + + North + + + East + + + West + + + South + + + North + + + East + + + West + + + South + + + IGAZ + + + {=SORTBY($N$13:$Q$15;$N15:$Q15)} + + + Region + + + + + + Amy + + + Tom + + + Fred + + + Sal + + + Amy + + + Tom + + + Fred + + + Sal + + + IGAZ + + + {=SORTBY($N$13:$Q$15;$N15:$Q15)} + + + Name + + + + + + 22 + + + 52 + + + 65 + + + 73 + + + 22 + + + 52 + + + 65 + + + 73 + + + IGAZ + + + {=SORTBY($N$13:$Q$15;$N15:$Q15)} + + + Age + + + + + + + + + + + East + + + South + + + West + + + North + + + East + + + South + + + West + + + North + + + IGAZ + + + {=SORTBY($N$13:$Q$15;$P43:$S43;-1)} + + + Region + + + + + + Tom + + + Sal + + + Fred + + + Amy + + + Tom + + + Sal + + + Fred + + + Amy + + + IGAZ + + + {=SORTBY($N$13:$Q$15;$P43:$S43;-1)} + + + Name + + + + + + 52 + + + 73 + + + 65 + + + 22 + + + 52 + + + 73 + + + 65 + + + 22 + + + IGAZ + + + {=SORTBY($N$13:$Q$15;$P43:$S43;-1)} + + + Age + + + + + + + + + + + South + + + North + + + East + + + West + + + South + + + North + + + East + + + West + + + IGAZ + + + {=SORTBY($N$13:$Q$15;{3.4.2.1})} + + + Region + + + + + + Sal + + + Amy + + + Tom + + + Fred + + + Sal + + + Amy + + + Tom + + + Fred + + + IGAZ + + + {=SORTBY($N$13:$Q$15;{3.4.2.1})} + + + Name + + + + + + 73 + + + 22 + + + 52 + + + 65 + + + 73 + + + 22 + + + 52 + + + 65 + + + IGAZ + + + {=SORTBY($N$13:$Q$15;{3.4.2.1})} + + + Age + + + + + + + + + + + Err:504 + + + + IGAZ + + + {=SORTBY($N$13:$Q$15;$O43:$S43)} + + + + + + + + + + + Err:504 + + + + IGAZ + + + {=SORTBY($N$13:$Q$15;S$3:S$6;1)} + + + + + + + + + + + West + + + East + + + South + + + North + + + West + + + East + + + South + + + North + + + IGAZ + + + {=SORTBY({"South"."North"."East"."West";"Sal"."Amy"."Tom"."Fred";73.22.52.65};{3.4.2.1})} + + + Region + + + + + + Fred + + + Tom + + + Sal + + + Amy + + + Fred + + + Tom + + + Sal + + + Amy + + + IGAZ + + + {=SORTBY({"South"."North"."East"."West";"Sal"."Amy"."Tom"."Fred";73.22.52.65};{3.4.2.1})} + + + Name + + + + + + 65 + + + 52 + + + 73 + + + 22 + + + 65 + + + 52 + + + 73 + + + 22 + + + IGAZ + + + {=SORTBY({"South"."North"."East"."West";"Sal"."Amy"."Tom"."Fred";73.22.52.65};{3.4.2.1})} + + + Age + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx index df62ab5ef344..62ef8c2218da 100644 --- a/sc/qa/unit/ucalc.cxx +++ b/sc/qa/unit/ucalc.cxx @@ -2922,6 +2922,7 @@ CPPUNIT_TEST_FIXTURE(Test, testFunctionLists) "SHEET", "SHEETS", "SORT", + "SORTBY", "STYLE", "VLOOKUP", "XLOOKUP", diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx index 677f76453ff5..5d89eb333350 100644 --- a/sc/source/core/data/funcdesc.cxx +++ b/sc/source/core/data/funcdesc.cxx @@ -789,6 +789,7 @@ ScFunctionList::ScFunctionList( bool bEnglishFunctionNames ) { SC_OPCODE_RANDBETWEEN_NV, ENTRY(SC_OPCODE_RANDBETWEEN_NV_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_RANDBETWEEN_NV, 2, { 0, 0 }, 0 }, { SC_OPCODE_FILTER, ENTRY(SC_OPCODE_FILTER_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_FILTER_MS, 3, { 0, 0, 1 }, 0 }, { SC_OPCODE_SORT, ENTRY(SC_OPCODE_SORT_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_SORT_MS, 4, { 0, 1, 1, 1 }, 0 }, + { SC_OPCODE_SORTBY, ENTRY(SC_OPCODE_SORTBY_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_SORTBY_MS, PAIRED_VAR_ARGS + 1, { 0, 0, 1 }, 0 }, }; ScFuncDesc* pDesc = nullptr; diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx index b984f8a4e37c..31ff2ecb7a9c 100644 --- a/sc/source/core/inc/interpre.hxx +++ b/sc/source/core/inc/interpre.hxx @@ -567,6 +567,8 @@ private: std::unique_ptr CreateFastSortInfoArray( const ScSortParam& rSortParam, bool bMatrix, SCCOLROW nInd1, SCCOLROW nInd2); std::vector GetSortOrder(const ScSortParam& rSortParam, const ScMatrixRef& pMatSrc); + ScMatrixRef CreateSortedMatrix(const ScSortParam& rSortParam, const ScMatrixRef& pMatSrc, + const ScRange& rSourceRange, const std::vector& rSortArray, SCSIZE nsC, SCSIZE nsR); void QuickSort(ScSortInfoArray* pArray, const ScMatrixRef& pMatSrc, SCCOLROW nLo, SCCOLROW nHi); @@ -712,6 +714,7 @@ private: void ScXLookup(); void ScFilter(); void ScSort(); + void ScSortBy(); void ScSubTotal(); // If upon call rMissingField==true then the database field parameter may be diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx index c1a4af803ef1..8ea81b336641 100644 --- a/sc/source/core/tool/interpr1.cxx +++ b/sc/source/core/tool/interpr1.cxx @@ -8433,75 +8433,230 @@ void ScInterpreter::ScSort() // sorting... std::vector aOrderIndices = GetSortOrder(aSortData, pMatSrc); + // create sorted matrix + ScMatrixRef pResMat = CreateSortedMatrix(aSortData, pMatSrc, + ScRange(aSortData.nCol1, aSortData.nRow1, aSortData.nSourceTab, + aSortData.nCol2, aSortData.nRow2, aSortData.nSourceTab), + aOrderIndices, nsC, nsR); - SCCOLROW nStartPos = (!aSortData.bByRow ? aSortData.nCol1 : aSortData.nRow1); - size_t nCount = aOrderIndices.size(); - std::vector aPosTable(nCount); + if (pResMat) + PushMatrix(pResMat); + else + PushIllegalParameter(); +} - for (size_t i = 0; i < nCount; ++i) - aPosTable[aOrderIndices[i] - nStartPos] = i; +void ScInterpreter::ScSortBy() +{ + sal_uInt8 nParamCount = GetByte(); - ScMatrixRef pResMat = nullptr; - if (!aOrderIndices.empty()) + if (nParamCount < 2/*|| (nParamCount % 2 != 1)*/) { - pResMat = GetNewMat(nsC, nsR, /*bEmpty*/true); - if (!pMatSrc) + PushError(FormulaError::ParameterExpected); + return; + } + + sal_uInt8 nSortCount = nParamCount / 2; + + ScSortParam aSortData; + aSortData.maKeyState.resize(nSortCount); + + // 127th, ..., 3rd and 2nd argument: sort by range/array and sort orders pair + sal_uInt8 nSortBy = nSortCount; + ScMatrixRef pFullMatSortBy = nullptr; + while (nSortBy-- > 0 && nGlobalError == FormulaError::NONE) + { + // 3rd argument sort_order optional: default ascending + if (nParamCount >= 3 && (nParamCount % 2 == 1)) { - ScCellIterator aCellIter(mrDoc, ScRange(aSortData.nCol1, aSortData.nRow1, aSortData.nSourceTab, - aSortData.nCol2, aSortData.nRow2, aSortData.nSourceTab)); - for (bool bHas = aCellIter.first(); bHas; bHas = aCellIter.next()) + sal_Int8 nSortOrder = static_cast(GetInt32WithDefault(1)); + if (nSortOrder != 1 && nSortOrder != -1) { - SCSIZE nThisCol = static_cast(aCellIter.GetPos().Col() - aSortData.nCol1); - SCSIZE nThisRow = static_cast(aCellIter.GetPos().Row() - aSortData.nRow1); + PushIllegalParameter(); + return; + } + aSortData.maKeyState[nSortBy].bAscending = (nSortOrder == 1); + nParamCount--; + } - ScRefCellValue aCell = aCellIter.getRefCellValue(); - if (aCell.hasNumeric()) + // 2nd argument: take sort by ranges + ScMatrixRef pMatSortBy = nullptr; + SCSIZE nbyC = 0, nbyR = 0; + switch (GetStackType()) + { + case svSingleRef: + case svDoubleRef: + case svMatrix: + case svExternalSingleRef: + case svExternalDoubleRef: + { + if (nSortCount == 1) { - if (aSortData.bByRow) - pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), nThisCol, aPosTable[nThisRow]); - else - pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), aPosTable[nThisCol], nThisRow); + pFullMatSortBy = GetMatrix(); + if (!pFullMatSortBy) + { + PushIllegalParameter(); + return; + } + pFullMatSortBy->GetDimensions(nbyC, nbyR); } else { - svl::SharedString aStr; - GetCellString(aStr, aCell); - if (aSortData.bByRow) - pResMat->PutString(aStr, nThisCol, aPosTable[nThisRow]); + pMatSortBy = GetMatrix(); + if (!pMatSortBy) + { + PushIllegalParameter(); + return; + } + pMatSortBy->GetDimensions(nbyC, nbyR); + } + + // last->first (backward) sortby array + if (nSortBy == nSortCount - 1) + { + if (nbyC == 1 && nbyR > 1) + aSortData.bByRow = true; + else if (nbyR == 1 && nbyC > 1) + aSortData.bByRow = false; else - pResMat->PutString(aStr, aPosTable[nThisCol], nThisRow); + { + PushIllegalParameter(); + return; + } + + if (nSortCount > 1) + { + pFullMatSortBy = GetNewMat(aSortData.bByRow ? (nbyC * nSortCount) : nbyC, + aSortData.bByRow ? nbyR : (nbyR * nSortCount), /*bEmpty*/true); + } } } + break; + + default: + PushIllegalParameter(); + return; } - else + + // ..., penultimate sortby arrays + if (nSortCount > 1 && nSortBy <= nSortCount - 1) { - for (SCCOL ci = aSortData.nCol1; ci <= aSortData.nCol2; ci++) + SCSIZE nCheckCol = 0, nCheckRow = 0; + pFullMatSortBy->GetDimensions(nCheckCol, nCheckRow); + if ((aSortData.bByRow && nbyR == nCheckRow && nbyC == 1) || + (!aSortData.bByRow && nbyC == nCheckCol && nbyR == 1)) { - for (SCROW rj = aSortData.nRow1; rj <= aSortData.nRow2; rj++) + for (SCSIZE ci = 0; ci < nbyC; ci++)//col { - if (pMatSrc->IsStringOrEmpty(ci, rj)) + for (SCSIZE rj = 0; rj < nbyR; rj++)//row { - if (aSortData.bByRow) - pResMat->PutString(pMatSrc->GetString(ci, rj), ci, aPosTable[rj]); - else - pResMat->PutString(pMatSrc->GetString(ci, rj), aPosTable[ci], rj); - } - else - { - if (aSortData.bByRow) - pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), ci, aPosTable[rj]); + if (pMatSortBy->IsStringOrEmpty(ci, rj)) + { + if (aSortData.bByRow) + pFullMatSortBy->PutString(pMatSortBy->GetString(ci, rj), ci + nSortBy, rj); + else + pFullMatSortBy->PutString(pMatSortBy->GetString(ci, rj), ci, rj + nSortBy); + } else - pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), aPosTable[ci], rj); + { + if (aSortData.bByRow) + pFullMatSortBy->PutDouble(pMatSortBy->GetDouble(ci, rj), ci + nSortBy, rj); + else + pFullMatSortBy->PutDouble(pMatSortBy->GetDouble(ci, rj), ci, rj + nSortBy); + } } } } + else + { + PushIllegalParameter(); + return; + } } + + aSortData.maKeyState[nSortBy].bDoSort = true; + aSortData.maKeyState[nSortBy].nField = nSortBy; + + nParamCount--; } + // 1st argument is the range/array to be sorted + SCSIZE nsC = 0, nsR = 0; + SCCOL nSortCol1 = 0, nSortCol2 = 0; + SCROW nSortRow1 = 0, nSortRow2 = 0; + SCTAB nSortTab1 = 0, nSortTab2 = 0; + ScMatrixRef pMatSrc = nullptr; + switch ( GetStackType() ) + { + case svSingleRef: + PopSingleRef(nSortCol1, nSortRow1, nSortTab1); + nSortCol2 = nSortCol1; + nSortRow2 = nSortRow1; + nsC = nSortCol2 - nSortCol1 + 1; + nsR = nSortRow2 - nSortRow1 + 1; + break; + case svDoubleRef: + { + PopDoubleRef(nSortCol1, nSortRow1, nSortTab1, nSortCol2, nSortRow2, nSortTab2); + if (nSortTab1 != nSortTab2) + { + PushIllegalParameter(); + return; + } + nsC = nSortCol2 - nSortCol1 + 1; + nsR = nSortRow2 - nSortRow1 + 1; + } + break; + case svMatrix: + case svExternalSingleRef: + case svExternalDoubleRef: + { + pMatSrc = GetMatrix(); + if (!pMatSrc) + { + PushIllegalParameter(); + return; + } + pMatSrc->GetDimensions(nsC, nsR); + nSortCol2 = nsC - 1; // nSortCol1 = 0 + nSortRow2 = nsR - 1; // nSortRow1 = 0 + } + break; + + default: + PushIllegalParameter(); + return; + } + + SCSIZE nCheckMatrixCol = 0, nCheckMatrixRow = 0; + pFullMatSortBy->GetDimensions(nCheckMatrixCol, nCheckMatrixRow); + if (nGlobalError != FormulaError::NONE) + { + PushError(nGlobalError); + return; + } + else if ((aSortData.bByRow && nsR != nCheckMatrixRow) || + (!aSortData.bByRow && nsC != nCheckMatrixCol)) + { + PushIllegalParameter(); + return; + } + else + { + aSortData.nCol2 = nCheckMatrixCol - 1; + aSortData.nRow2 = nCheckMatrixRow - 1; + } + + // sorting... + std::vector aOrderIndices = GetSortOrder(aSortData, pFullMatSortBy); + // create sorted matrix + ScMatrixRef pResMat = CreateSortedMatrix(aSortData, pMatSrc, + ScRange(nSortCol1, nSortRow1, nSortTab1, nSortCol2, nSortRow2, nSortTab2), + aOrderIndices, nsC, nsR); + if (pResMat) PushMatrix(pResMat); else - PushError(FormulaError::NestedArray); + PushIllegalParameter(); } void ScInterpreter::ScSubTotal() diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx index 17e52858637b..860f98988a41 100644 --- a/sc/source/core/tool/interpr3.cxx +++ b/sc/source/core/tool/interpr3.cxx @@ -4253,6 +4253,75 @@ std::vector ScInterpreter::GetSortOrder( const ScSortParam& rSortParam return aOrderIndices; } +ScMatrixRef ScInterpreter::CreateSortedMatrix( const ScSortParam& rSortParam, const ScMatrixRef& pMatSrc, + const ScRange& rSourceRange, const std::vector& rSortArray, SCSIZE nsC, SCSIZE nsR ) +{ + SCCOLROW nStartPos = (!rSortParam.bByRow ? rSortParam.nCol1 : rSortParam.nRow1); + size_t nCount = rSortArray.size(); + std::vector aPosTable(nCount); + + for (size_t i = 0; i < nCount; ++i) + aPosTable[rSortArray[i] - nStartPos] = i; + + ScMatrixRef pResMat = nullptr; + if (!rSortArray.empty()) + { + pResMat = GetNewMat(nsC, nsR, /*bEmpty*/true); + if (!pMatSrc) + { + ScCellIterator aCellIter(mrDoc, rSourceRange); + for (bool bHas = aCellIter.first(); bHas; bHas = aCellIter.next()) + { + SCSIZE nThisCol = static_cast(aCellIter.GetPos().Col() - rSourceRange.aStart.Col()); + SCSIZE nThisRow = static_cast(aCellIter.GetPos().Row() - rSourceRange.aStart.Row()); + + ScRefCellValue aCell = aCellIter.getRefCellValue(); + if (aCell.hasNumeric()) + { + if (rSortParam.bByRow) + pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), nThisCol, aPosTable[nThisRow]); + else + pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), aPosTable[nThisCol], nThisRow); + } + else + { + svl::SharedString aStr; + GetCellString(aStr, aCell); + if (rSortParam.bByRow) + pResMat->PutString(aStr, nThisCol, aPosTable[nThisRow]); + else + pResMat->PutString(aStr, aPosTable[nThisCol], nThisRow); + } + } + } + else + { + for (SCCOL ci = rSourceRange.aStart.Col(); ci <= rSourceRange.aEnd.Col(); ci++) + { + for (SCROW rj = rSourceRange.aStart.Row(); rj <= rSourceRange.aEnd.Row(); rj++) + { + if (pMatSrc->IsStringOrEmpty(ci, rj)) + { + if (rSortParam.bByRow) + pResMat->PutString(pMatSrc->GetString(ci, rj), ci, aPosTable[rj]); + else + pResMat->PutString(pMatSrc->GetString(ci, rj), aPosTable[ci], rj); + } + else + { + if (rSortParam.bByRow) + pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), ci, aPosTable[rj]); + else + pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), aPosTable[ci], rj); + } + } + } + } + } + + return pResMat; +} + void ScInterpreter::QuickSort( ScSortInfoArray* pArray, const ScMatrixRef& pMatSrc, SCCOLROW nLo, SCCOLROW nHi ) { if ((nHi - nLo) == 1) diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx index c9e795176297..5e5d513289d0 100644 --- a/sc/source/core/tool/interpr4.cxx +++ b/sc/source/core/tool/interpr4.cxx @@ -4129,6 +4129,7 @@ StackVar ScInterpreter::Interpret() case ocRandbetweenNV : ScRandbetween(); break; case ocFilter : ScFilter(); break; case ocSort : ScSort(); break; + case ocSortBy : ScSortBy(); break; case ocTrue : ScTrue(); break; case ocFalse : ScFalse(); break; case ocGetActDate : ScGetActDate(); break; diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx index 0bc87183ad89..e07645ec908b 100644 --- a/sc/source/core/tool/parclass.cxx +++ b/sc/source/core/tool/parclass.cxx @@ -235,6 +235,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] = { ocSlope, {{ ForceArray, ForceArray }, 0, Value }}, { ocSmall, {{ Reference, Value }, 0, Value }}, { ocSort, {{ ReferenceOrRefArray, ForceArray, ForceArray, Value }, 0, ForceArrayReturn }}, + { ocSortBy, {{ ReferenceOrRefArray, ReferenceOrRefArray, Value, }, 2, ForceArrayReturn }}, { ocStDev, {{ Reference }, 1, Value }}, { ocStDevA, {{ Reference }, 1, Value }}, { ocStDevP, {{ Reference }, 1, Value }}, diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx index b81cb8bb3d26..f2d21ca74963 100644 --- a/sc/source/core/tool/token.cxx +++ b/sc/source/core/tool/token.cxx @@ -1391,6 +1391,7 @@ void ScTokenArray::CheckToken( const FormulaToken& r ) case ocXMatch: case ocFilter: case ocSort: + case ocSortBy: case ocSLN: case ocIRR: case ocMIRR: diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx index 00b5843ad421..787d4c53f5d1 100644 --- a/sc/source/filter/excel/xlformula.cxx +++ b/sc/source/filter/excel/xlformula.cxx @@ -604,7 +604,8 @@ const XclFunctionInfo saFuncTable_2021[] = EXC_FUNCENTRY_V_VR( ocXLookup, 3, 6, 0, "XLOOKUP" ), EXC_FUNCENTRY_V_VR( ocXMatch, 2, 4, 0, "XMATCH" ), EXC_FUNCENTRY_V_VR( ocFilter, 2, 3, 0, "FILTER" ), - EXC_FUNCENTRY_V_VR( ocSort, 1, 4, 0, "SORT" ) + EXC_FUNCENTRY_V_VR( ocSort, 1, 4, 0, "SORT" ), + EXC_FUNCENTRY_V_VR( ocSortBy, 2, 3, 0, "SORTBY" ) }; diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx index a07edc25efbc..003c1378df32 100644 --- a/sc/source/filter/oox/formulabase.cxx +++ b/sc/source/filter/oox/formulabase.cxx @@ -878,7 +878,8 @@ const FunctionData saFuncTable2021[] = { "COM.MICROSOFT.XLOOKUP", "XLOOKUP", NOID, NOID, 3, 6, R, { VR, VA, VR }, FuncFlags::MACROCALL_NEW }, { "COM.MICROSOFT.XMATCH", "XMATCH", NOID, NOID, 2, 4, V, { VR, VA }, FuncFlags::MACROCALL_NEW }, { "COM.MICROSOFT.FILTER", "FILTER", NOID, NOID, 2, 3, A, { VR, VA }, FuncFlags::MACROCALL_NEW }, - { "COM.MICROSOFT.SORT", "SORT", NOID, NOID, 1, 4, A, { VO }, FuncFlags::MACROCALL_NEW } + { "COM.MICROSOFT.SORT", "SORT", NOID, NOID, 1, 4, A, { VO }, FuncFlags::MACROCALL_NEW }, + { "COM.MICROSOFT.SORTBY", "SORTBY", NOID, NOID, 2, MX, V, { RO, RO, VR }, FuncFlags::MACROCALL_NEW | FuncFlags::PARAMPAIRS } }; -- cgit