sc/inc/queryiter.hxx | 18
sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods | 406 ++++++++++++++--
sc/source/core/data/queryiter.cxx | 57 +-
sc/source/core/tool/interpr1.cxx | 22
4 files changed, 423 insertions(+), 80 deletions(-)
New commits:
commit 68738bd0ac262819b13ea7e11af67ee493b9b3e1
Author: Balazs Varga <balazs.varga.ext...@allotropia.de>
AuthorDate: Thu Feb 1 14:36:24 2024 +0100
Commit: Balazs Varga <balazs.varga.ext...@allotropia.de>
CommitDate: Thu Feb 8 08:25:15 2024 +0100
Related: tdf#127293 Fix function XLOOKUP binary search corner cases
Fix some binary search (vertical) corner cases in case of XLOOKUP
where we looking for the first matches.
Change-Id: I6cdc778350989e0802ffc54284fdab9b8a2bece4
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/162877
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.ext...@allotropia.de>
diff --git a/sc/inc/queryiter.hxx b/sc/inc/queryiter.hxx
index e34a7be20e13..662fe8a9cf35 100644
--- a/sc/inc/queryiter.hxx
+++ b/sc/inc/queryiter.hxx
@@ -171,8 +171,16 @@ protected:
nTestEqualConditionFulfilled = nTestEqualConditionEnabled |
nTestEqualConditionMatched
};
+ enum SortedBinarySearchBits
+ {
+ nBinarySearchDisabled = 0x00,
+ nSearchbAscd = 0x01,
+ nSearchbDesc = 0x02,
+ };
+
sal_uInt8 nStopOnMismatch;
sal_uInt8 nTestEqualCondition;
+ sal_uInt8 nSortedBinarySearch;
bool bAdvanceQuery;
bool bIgnoreMismatchOnLeadingStrings;
bool bSortedBinarySearch;
@@ -263,8 +271,11 @@ public:
{ bAdvanceQuery = bVal; }
void AdvanceQueryParamEntryField();
- void SetSortedBinarySearchMode( bool bVal )
- { bSortedBinarySearch = bVal; }
+ void SetSortedBinarySearchMode( sal_Int8 nSearchMode )
+ {
+ nSortedBinarySearch =
sal::static_int_cast<sal_uInt8>(nSearchMode == 2 ?
+ nSearchbAscd : (nSearchMode == -2 ?
nSearchbDesc : nBinarySearchDisabled));
+ }
void SetXlookupMode( bool bVal )
{ bXLookUp = bVal; }
@@ -309,11 +320,12 @@ class ScQueryCellIterator
using Base::nStopOnMismatchEnabled;
using Base::nTestEqualCondition;
using Base::nTestEqualConditionEnabled;
+ using Base::nSortedBinarySearch;
+ using Base::nBinarySearchDisabled;
using Base::PerformQuery;
using Base::getThisResult;
using Base::nBestFitCol;
using Base::nBestFitRow;
- using Base::bSortedBinarySearch;
using Base::bXLookUp;
bool GetThis();
diff --git a/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods
b/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods
index 16abcbcacb4b..49ecf573b50c 100644
--- a/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods
+++ b/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods
@@ -1,7 +1,7 @@
<?xml version="1.0" encoding="UTF-8"?>
<office:document
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
xmlns:ooo="http://openoffice.org/2004/office";
xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"
xmlns:xlink="http://www.w3.org/1999/xlink";
xmlns:config="urn:oasis:names:tc:opendocument:xmlns:config:1.0"
xmlns:dc="http://purl.org/dc/elements/1.1/";
xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0"
xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0"
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
xmlns:rpt="http://openoffice.org/2005/report";
xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"
xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"
xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0"
xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0"
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"
xmlns:ooow="http://openoffice.org/200
4/writer" xmlns:oooc="http://openoffice.org/2004/calc";
xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
xmlns:xforms="http://www.w3.org/2002/xforms";
xmlns:tableooo="http://openoffice.org/2009/table";
xmlns:calcext="urn:org:documentfoundation:names:experimental:calc:xmlns:calcext:1.0"
xmlns:drawooo="http://openoffice.org/2010/draw";
xmlns:loext="urn:org:documentfoundation:names:experimental:office:xmlns:loext:1.0"
xmlns:field="urn:openoffice:names:experimental:ooo-ms-interop:xmlns:field:1.0"
xmlns:math="http://www.w3.org/1998/Math/MathML";
xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0"
xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0"
xmlns:formx="urn:openoffice:names:experimental:ooxml-odf-interop:xmlns:form:1.0"
xmlns:dom="http://www.w3.org/2001/xml-events";
xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:xhtml="http://www.w3.org/1999/xhtml";
xmlns:grddl="http://www.w3.org/2003/g/data-view#"; xmlns
:css3t="http://www.w3.org/TR/css3-text/";
xmlns:presentation="urn:oasis:names:tc:opendocument:xmlns:presentation:1.0"
office:version="1.3"
office:mimetype="application/vnd.oasis.opendocument.spreadsheet">
-
<office:meta><meta:creation-date>2024-01-16T18:30:06.278000000</meta:creation-date><meta:editing-duration>PT52M13S</meta:editing-duration><meta:editing-cycles>14</meta:editing-cycles><meta:generator>LibreOfficeDev/24.8.0.0.alpha0$Windows_X86_64
LibreOffice_project/8f959fffda28199c3d97aea8d5468776a0e43d90</meta:generator><dc:date>2024-01-26T18:16:42.850000000</dc:date><meta:document-statistic
meta:table-count="2" meta:cell-count="754"
meta:object-count="0"/></office:meta>
+
<office:meta><meta:creation-date>2024-01-16T18:30:06.278000000</meta:creation-date><meta:editing-duration>PT1H12M</meta:editing-duration><meta:editing-cycles>22</meta:editing-cycles><meta:generator>LibreOfficeDev/24.8.0.0.alpha0$Windows_X86_64
LibreOffice_project/2b1f5fa987f02ec8014dbf6837c6b090faf11f2d</meta:generator><dc:date>2024-02-01T11:50:13.789000000</dc:date><meta:document-statistic
meta:table-count="2" meta:cell-count="858"
meta:object-count="0"/></office:meta>
<office:settings>
<config:config-item-set config:name="ooo:view-settings">
<config:config-item config:name="VisibleAreaTop"
config:type="int">0</config:config-item>
@@ -30,13 +30,13 @@
<config:config-item config:name="IgnoreBreakAfterMultilineField"
config:type="boolean">false</config:config-item>
</config:config-item-map-entry>
<config:config-item-map-entry config:name="Sheet2">
- <config:config-item config:name="CursorPositionX"
config:type="int">0</config:config-item>
- <config:config-item config:name="CursorPositionY"
config:type="int">104</config:config-item>
+ <config:config-item config:name="CursorPositionX"
config:type="int">1</config:config-item>
+ <config:config-item config:name="CursorPositionY"
config:type="int">115</config:config-item>
<config:config-item config:name="ActiveSplitRange"
config:type="short">2</config:config-item>
<config:config-item config:name="PositionLeft"
config:type="int">0</config:config-item>
<config:config-item config:name="PositionRight"
config:type="int">0</config:config-item>
<config:config-item config:name="PositionTop"
config:type="int">0</config:config-item>
- <config:config-item config:name="PositionBottom"
config:type="int">81</config:config-item>
+ <config:config-item config:name="PositionBottom"
config:type="int">0</config:config-item>
<config:config-item config:name="ZoomType"
config:type="short">0</config:config-item>
<config:config-item config:name="ZoomValue"
config:type="int">65</config:config-item>
<config:config-item config:name="PageViewZoomValue"
config:type="int">60</config:config-item>
@@ -48,7 +48,7 @@
</config:config-item-map-entry>
</config:config-item-map-named>
<config:config-item config:name="ActiveTable"
config:type="string">Sheet1</config:config-item>
- <config:config-item config:name="HorizontalScrollbarWidth"
config:type="int">1835</config:config-item>
+ <config:config-item config:name="HorizontalScrollbarWidth"
config:type="int">1837</config:config-item>
<config:config-item config:name="ZoomType"
config:type="short">0</config:config-item>
<config:config-item config:name="ZoomValue"
config:type="int">65</config:config-item>
<config:config-item config:name="PageViewZoomValue"
config:type="int">60</config:config-item>
@@ -126,7 +126,7 @@
<config:config-item config:name="UpdateFromTemplate"
config:type="boolean">true</config:config-item>
<config:config-item-map-named config:name="ScriptConfiguration">
<config:config-item-map-entry config:name="Sheet1">
- <config:config-item config:name="CodeName"
config:type="string">Sheet1</config:config-item>
+ <config:config-item config:name="CodeName"
config:type="string">Munkalap1</config:config-item>
</config:config-item-map-entry>
<config:config-item-map-entry config:name="Sheet2">
<config:config-item config:name="CodeName"
config:type="string">Sheet2</config:config-item>
@@ -1597,6 +1597,9 @@
<style:style style:name="co8" style:family="table-column">
<style:table-column-properties fo:break-before="auto"
style:column-width="5.657cm"/>
</style:style>
+ <style:style style:name="co9" style:family="table-column">
+ <style:table-column-properties fo:break-before="auto"
style:column-width="1.875cm"/>
+ </style:style>
<style:style style:name="ro1" style:family="table-row">
<style:table-row-properties style:row-height="1.614cm"
fo:break-before="auto" style:use-optimal-row-height="true"/>
</style:style>
@@ -1610,7 +1613,7 @@
<style:table-row-properties style:row-height="0.529cm"
fo:break-before="auto" style:use-optimal-row-height="true"/>
</style:style>
<style:style style:name="ro5" style:family="table-row">
- <style:table-row-properties style:row-height="1.656cm"
fo:break-before="auto" style:use-optimal-row-height="true"/>
+ <style:table-row-properties style:row-height="1.632cm"
fo:break-before="auto" style:use-optimal-row-height="true"/>
</style:style>
<style:style style:name="ta1" style:family="table"
style:master-page-name="Default">
<style:table-properties table:display="true" style:writing-mode="lr-tb"/>
@@ -1673,6 +1676,7 @@
<style:style style:name="ce12" style:family="table-cell"
style:parent-style-name="Default">
<style:table-cell-properties fo:wrap-option="wrap"/>
</style:style>
+ <style:style style:name="ce20" style:family="table-cell"
style:parent-style-name="Default" style:data-style-name="N0"/>
<style:page-layout style:name="pm1">
<style:page-layout-properties style:writing-mode="lr-tb"/>
<style:header-style>
@@ -1715,7 +1719,7 @@
<text:p><text:sheet-name>???</text:sheet-name><text:s/>(<text:title>???</text:title>)</text:p>
</style:region-left>
<style:region-right>
- <text:p><text:date style:data-style-name="N2"
text:date-value="2024-01-26">0000.00.00</text:date>, <text:time
style:data-style-name="N2"
text:time-value="18:11:05.851000000">00:00:00</text:time></text:p>
+ <text:p><text:date style:data-style-name="N2"
text:date-value="2024-02-01">0000.00.00</text:date>, <text:time
style:data-style-name="N2"
text:time-value="14:12:01.197000000">00:00:00</text:time></text:p>
</style:region-right>
</style:header>
<style:header-left style:display="false"/>
@@ -1773,7 +1777,7 @@
<table:table-cell office:value-type="float" office:value="2"
calcext:value-type="float">
<text:p>2</text:p>
</table:table-cell>
- <table:table-cell table:style-name="ce14"
table:formula="of:=AND([Sheet2.C2:.C201])" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <table:table-cell table:style-name="ce14"
table:formula="of:=AND([Sheet2.C2:.C202])" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
<text:p>IGAZ</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce22" office:value-type="string"
calcext:value-type="string">
@@ -1804,7 +1808,8 @@
<table:table-column table:style-name="co6"
table:default-cell-style-name="ce10"/>
<table:table-column table:style-name="co7"
table:default-cell-style-name="Default"/>
<table:table-column table:style-name="co8"
table:default-cell-style-name="Default"/>
- <table:table-column table:style-name="co4"
table:number-columns-repeated="15" table:default-cell-style-name="Default"/>
+ <table:table-column table:style-name="co4"
table:number-columns-repeated="14" table:default-cell-style-name="Default"/>
+ <table:table-column table:style-name="co9"
table:default-cell-style-name="Default"/>
<table:table-row table:style-name="ro2">
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>Function</text:p>
@@ -1846,7 +1851,24 @@
<table:table-cell office:value-type="float" office:value="4"
calcext:value-type="float">
<text:p>4</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="6"/>
+ <table:table-cell office:value-type="float" office:value="2"
calcext:value-type="float">
+ <text:p>2</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>Country Asc1</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>Country Asc2</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>Country Desc1</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>Country Desc2</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>Prefix</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.G2];[.H2:.H11];[.J2:.J11])"
office:value-type="string" office:string-value="+55"
calcext:value-type="string">
@@ -1880,7 +1902,16 @@
<table:table-cell office:value-type="float" office:value="10"
calcext:value-type="float">
<text:p>10</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="0" calcext:value-type="float">
+ <text:p>0</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="8" calcext:value-type="float">
+ <text:p>8</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>a</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("India";[.H2:.H11];[.J2:.J11])"
office:value-type="string" office:string-value="+91"
calcext:value-type="string">
@@ -1911,7 +1942,16 @@
<table:table-cell office:value-type="float" office:value="9"
calcext:value-type="float">
<text:p>9</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="1" calcext:value-type="float">
+ <text:p>1</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="7" calcext:value-type="float">
+ <text:p>7</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>b</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("INDIA";[.H2:.H11];[.J2:.J11])"
office:value-type="string" office:string-value="+91"
calcext:value-type="string">
@@ -1942,14 +1982,26 @@
<table:table-cell office:value-type="float" office:value="8"
calcext:value-type="float">
<text:p>8</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell office:value-type="float" office:value="1"
calcext:value-type="float">
+ <text:p>1</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float" office:value="2"
calcext:value-type="float">
+ <text:p>2</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="7" calcext:value-type="float">
+ <text:p>7</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce20" office:value-type="string"
calcext:value-type="string">
+ <text:p>c</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=#N/A" office:value-type="string"
office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce18" table:formula="of:=ISNA([.A5])"
office:value-type="boolean" office:boolean-value="true"
calcext:value-type="boolean">
<text:p>IGAZ</text:p>
@@ -1973,7 +2025,19 @@
<table:table-cell office:value-type="float" office:value="7"
calcext:value-type="float">
<text:p>7</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell office:value-type="float" office:value="3"
calcext:value-type="float">
+ <text:p>3</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float" office:value="2"
calcext:value-type="float">
+ <text:p>2</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="6" calcext:value-type="float">
+ <text:p>6</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>d</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11];"")">
@@ -2004,7 +2068,16 @@
<table:table-cell office:value-type="float" office:value="6"
calcext:value-type="float">
<text:p>6</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="3" calcext:value-type="float">
+ <text:p>3</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="4" calcext:value-type="float">
+ <text:p>4</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>e</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11];-99)"
office:value-type="float" office:value="-99" calcext:value-type="float">
@@ -2032,7 +2105,16 @@
<table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="6" calcext:value-type="float">
<text:p>6</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="4" calcext:value-type="float">
+ <text:p>4</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="3" calcext:value-type="float">
+ <text:p>3</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>f</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11];"Not
here")" office:value-type="string" office:string-value="Not here"
calcext:value-type="string">
@@ -2063,14 +2145,26 @@
<table:table-cell office:value-type="float" office:value="4"
calcext:value-type="float">
<text:p>4</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="6" calcext:value-type="float">
+ <text:p>6</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float" office:value="3"
calcext:value-type="float">
+ <text:p>3</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float" office:value="2"
calcext:value-type="float">
+ <text:p>2</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>g</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("";[.H2:.H11];[.J2:.J11])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=#N/A" office:value-type="string"
office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce18" table:formula="of:=ISNA([.A9])"
office:value-type="boolean" office:boolean-value="true"
calcext:value-type="boolean">
<text:p>IGAZ</text:p>
@@ -2094,7 +2188,19 @@
<table:table-cell office:value-type="float" office:value="3"
calcext:value-type="float">
<text:p>3</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="7" calcext:value-type="float">
+ <text:p>7</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float" office:value="1"
calcext:value-type="float">
+ <text:p>1</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float" office:value="2"
calcext:value-type="float">
+ <text:p>2</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>h</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("Nigeria";[.H2:.H11];[.J2:.J11];;;1)"
office:value-type="string" office:string-value="00234"
calcext:value-type="string">
@@ -2125,7 +2231,16 @@
<table:table-cell office:value-type="float" office:value="2"
calcext:value-type="float">
<text:p>2</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="7" calcext:value-type="float">
+ <text:p>7</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="1" calcext:value-type="float">
+ <text:p>1</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>I</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("Nigeria";[.H2:.H11];[.J2:.J11];;;-1)"
office:value-type="string" office:string-value="+234"
calcext:value-type="string">
@@ -2156,7 +2271,16 @@
<table:table-cell office:value-type="float" office:value="1"
calcext:value-type="float">
<text:p>1</text:p>
</table:table-cell>
- <table:table-cell table:number-columns-repeated="8"/>
+ <table:table-cell table:number-columns-repeated="3"/>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="8" calcext:value-type="float">
+ <text:p>8</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="2"
office:value-type="float" office:value="0" calcext:value-type="float">
+ <text:p>0</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>j</text:p>
+ </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell table:number-columns-repeated="2"/>
@@ -2385,10 +2509,10 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell table:number-matrix-columns-spanned="1"
table:number-matrix-rows-spanned="2"
table:formula="of:=COM.MICROSOFT.XLOOKUP("";[.I14:.R14];[.I15:.R16])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=#N/A" office:value-type="string"
office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce18"
table:formula="of:=ISNA([.A20])" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
<text:p>IGAZ</text:p>
@@ -2400,10 +2524,10 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell office:value-type="string" office:string-value=""
calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=#N/A" office:value-type="string"
office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce18"
table:formula="of:=ISNA([.A21])" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
<text:p>IGAZ</text:p>
@@ -2546,8 +2670,8 @@
<table:table-cell office:value-type="float" office:value="0.24"
calcext:value-type="float">
<text:p>0,24</text:p>
</table:table-cell>
- <table:table-cell table:style-name="ce18" office:value-type="string"
calcext:value-type="string">
- <text:p/>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A30]=[.B30]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=FORMULA([.A30])"
office:value-type="string"
office:string-value="=XLOOKUP(G29;I30:I35;H30:H35;;1;2)"
calcext:value-type="string">
<text:p>=XLOOKUP(G29;I30:I35;H30:H35;;1;2)</text:p>
@@ -2588,7 +2712,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.G29];[.I30:.I35];[.H30:.H35];;2;2)"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>#N/A</text:p>
@@ -2937,14 +3061,14 @@
</table:table-row>
<table:table-row table:style-name="ro5">
<table:table-cell
table:formula="of:=SUM(COM.MICROSOFT.XLOOKUP([.G44];[.H45:.H49];[.K45:.K49]):COM.MICROSOFT.XLOOKUP([.G45];[.H45:.H49];[.K45:.K49]))"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>Err:502</text:p>
+ <text:p>Hiba:502</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="11070"
calcext:value-type="float">
<text:p>11070</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce18"/>
- <table:table-cell table:formula="of:=FORMULA([.A44])"
office:value-type="string"
office:string-value="=SUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))"
calcext:value-type="string">
-
<text:p>=SUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))</text:p>
+ <table:table-cell table:formula="of:=FORMULA([.A44])"
office:value-type="string"
office:string-value="=SZUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))"
calcext:value-type="string">
+
<text:p>=SZUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce12" office:value-type="string"
calcext:value-type="string">
<text:p>XLOOKUP should (in this case) return a range (TODO: Missing
feature from Calc: Evaluate Formula)</text:p>
@@ -3067,7 +3191,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("B";[.H51:.H53];[.K51:.K52])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>Err:504</text:p>
+ <text:p>Hiba:504</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>err:504</text:p>
@@ -3100,7 +3224,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("C";[.H51:.H53];[.K51:.K52])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>Err:504</text:p>
+ <text:p>Hiba:504</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>err:504</text:p>
@@ -3133,7 +3257,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("E";[.H51:.I53];[.K51:.L53])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>Err:504</text:p>
+ <text:p>Hiba:504</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>err:504</text:p>
@@ -3166,7 +3290,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("E";[.H51:.I53];[.K51:.K53])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>Err:504</text:p>
+ <text:p>Hiba:504</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>err:504</text:p>
@@ -3185,7 +3309,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("C";[.H51:.I53];[.K51:.K53])"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>Err:504</text:p>
+ <text:p>Hiba:504</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>err:504</text:p>
@@ -4197,7 +4321,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("C?";[.H$95:.L$95];[.H$96:.L$96];;2;1)"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>#N/A</text:p>
@@ -4346,7 +4470,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP("f?";[.I$79:.I$83];[.L$79:.L$83];;2;1)"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>#N/A</text:p>
@@ -4515,7 +4639,7 @@
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.M1];[.K$1:.K$12];[.I$1:.I$12];;0;2)"
office:value-type="string" office:string-value="" calcext:value-type="error">
- <text:p>#N/A</text:p>
+ <text:p>#HIÁNYZIK</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>#N/A</text:p>
@@ -4528,19 +4652,209 @@
</table:table-cell>
<table:table-cell table:number-columns-repeated="16"/>
</table:table-row>
- <table:table-row table:style-name="ro2" table:number-rows-repeated="90">
+ <table:table-row table:style-name="ro2">
+ <table:table-cell table:number-columns-repeated="2"/>
+ <table:table-cell table:style-name="ce18"/>
+ <table:table-cell table:number-columns-repeated="17"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.P$1:.P$11];[.T$1:.T$11];;0;2)"
office:value-type="string" office:string-value="" calcext:value-type="error">
+ <text:p>#HIÁNYZIK</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>#N/A</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=ISERROR([.A113])" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A113])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;0;2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;0;2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.P$1:.P$11];[.T$1:.T$11];;-1;2)"
office:value-type="string" office:string-value="b" calcext:value-type="string">
+ <text:p>b</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>b</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A114]=[.B114]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A114])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;-1;2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;-1;2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.P$1:.P$11];[.T$1:.T$11];;1;2)"
office:value-type="string" office:string-value="d" calcext:value-type="string">
+ <text:p>d</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>d</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A115]=[.B115]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A115])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;1;2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;1;2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.Q$1:.Q$11];[.T$1:.T$11];;0;2)"
office:value-type="string" office:string-value="c" calcext:value-type="string">
+ <text:p>c</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>c</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A116]=[.B116]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A116])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;0;2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;0;2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.Q$1:.Q$11];[.T$1:.T$11];;-1;2)"
office:value-type="string" office:string-value="c" calcext:value-type="string">
+ <text:p>c</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>c</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A117]=[.B117]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A117])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;-1;2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;-1;2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.Q$1:.Q$11];[.T$1:.T$11];;1;2)"
office:value-type="string" office:string-value="c" calcext:value-type="string">
+ <text:p>c</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>c</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A118]=[.B118]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A118])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;1;2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;1;2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell table:number-columns-repeated="2"/>
+ <table:table-cell table:style-name="ce18"/>
+ <table:table-cell table:number-columns-repeated="17"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.R$1:.R$11];[.T$1:.T$11];;0;-2)"
office:value-type="string" office:string-value="" calcext:value-type="error">
+ <text:p>#HIÁNYZIK</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>#N/A</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=ISERROR([.A120])" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A120])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;0;-2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;0;-2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.R$1:.R$11];[.T$1:.T$11];;-1;-2)"
office:value-type="string" office:string-value="I" calcext:value-type="string">
+ <text:p>I</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>I</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A121]=[.B121]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A121])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;-1;-2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;-1;-2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.R$1:.R$11];[.T$1:.T$11];;1;-2)"
office:value-type="string" office:string-value="g" calcext:value-type="string">
+ <text:p>g</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>g</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A122]=[.B122]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A122])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;1;-2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;1;-2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.S$1:.S$11];[.T$1:.T$11];;0;-2)"
office:value-type="string" office:string-value="h" calcext:value-type="string">
+ <text:p>h</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>h</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A123]=[.B123]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A123])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;0;-2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;0;-2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.S$1:.S$11];[.T$1:.T$11];;-1;-2)"
office:value-type="string" office:string-value="h" calcext:value-type="string">
+ <text:p>h</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>h</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A124]=[.B124]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A124])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;-1;-2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;-1;-2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2">
+ <table:table-cell
table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.S$1:.S$11];[.T$1:.T$11];;1;-2)"
office:value-type="string" office:string-value="h" calcext:value-type="string">
+ <text:p>h</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>h</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce18"
table:formula="of:=[.A125]=[.B125]" office:value-type="boolean"
office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>IGAZ</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A125])"
office:value-type="string"
office:string-value="=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;1;-2)"
calcext:value-type="string">
+ <text:p>=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;1;-2)</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="16"/>
+ </table:table-row>
+ <table:table-row table:style-name="ro2" table:number-rows-repeated="77">
<table:table-cell table:number-columns-repeated="2"/>
<table:table-cell table:style-name="ce18"/>
<table:table-cell table:number-columns-repeated="17"/>
</table:table-row>
- <table:table-row table:style-name="ro2"
table:number-rows-repeated="1048374">
+ <table:table-row table:style-name="ro2"
table:number-rows-repeated="1048373">
<table:table-cell table:number-columns-repeated="20"/>
</table:table-row>
<table:table-row table:style-name="ro2">
<table:table-cell table:number-columns-repeated="20"/>
</table:table-row>
<calcext:conditional-formats>
- <calcext:conditional-format
calcext:target-range-address="Sheet2.C2:Sheet2.C201">
+ <calcext:conditional-format
calcext:target-range-address="Sheet2.C2:Sheet2.C202">
<calcext:condition calcext:apply-style-name="Default"
calcext:value="=""" calcext:base-cell-address="Sheet2.C2"/>
<calcext:condition calcext:apply-style-name="true" calcext:value="=1"
calcext:base-cell-address="Sheet2.C2"/>
<calcext:condition calcext:apply-style-name="false" calcext:value="=0"
calcext:base-cell-address="Sheet2.C2"/>
diff --git a/sc/source/core/data/queryiter.cxx
b/sc/source/core/data/queryiter.cxx
index 39b86fe173c0..43996b1693eb 100644
--- a/sc/source/core/data/queryiter.cxx
+++ b/sc/source/core/data/queryiter.cxx
@@ -64,9 +64,9 @@ ScQueryCellIteratorBase< accessType, queryType
>::ScQueryCellIteratorBase(ScDocu
: AccessBase( rDocument, rContext, rParam, bReverse )
, nStopOnMismatch( nStopOnMismatchDisabled )
, nTestEqualCondition( nTestEqualConditionDisabled )
+ , nSortedBinarySearch( nBinarySearchDisabled )
, bAdvanceQuery( false )
, bIgnoreMismatchOnLeadingStrings( false )
- , bSortedBinarySearch( false )
, bXLookUp( false )
, nBestFitCol(SCCOL_MAX)
, nBestFitRow(SCROW_MAX)
@@ -206,8 +206,8 @@ void ScQueryCellIteratorBase< accessType, queryType
>::PerformQuery()
if ( aCell.isEmpty())
return;
- // XLookUp: Forward/backward search for best fit value, except
if we have an exact match
- if (bXLookUp && !bSortedBinarySearch && (rEntry.eOp ==
SC_LESS_EQUAL || rEntry.eOp == SC_GREATER_EQUAL) &&
+ // XLookUp: Forward/asc/backward/desc search for best fit
value, except if we have an exact match
+ if (bXLookUp && (rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp ==
SC_GREATER_EQUAL) &&
(nBestFitCol != nCol || nBestFitRow != nRow))
{
bool bNumSearch = rItem.meType == ScQueryEntry::ByValue &&
aCell.hasNumeric();
@@ -314,14 +314,18 @@ void ScQueryCellIteratorBase< accessType, queryType
>::InitPos()
// non-matching position using SC_LESS and the start position
// is the one after it.
lastRow = nRow;
- ScQueryOp saveOp = op;
- op = SC_LESS;
- if( BinarySearch( nCol, true ))
- beforeRow = nRow;
- // If BinarySearch() returns false, there was no match, which
means
- // there's no value smaller. In that case BinarySearch() has
set
- // the position to the first row in the range.
- op = saveOp; // back to SC_EQUAL
+ // BinarySearch() looks for the first match for XLOOKUP
+ if (!bXLookUp)
+ {
+ ScQueryOp saveOp = op;
+ op = SC_LESS;
+ if( BinarySearch( nCol, true ))
+ beforeRow = nRow;
+ // If BinarySearch() returns false, there was no match,
which means
+ // there's no value smaller. In that case BinarySearch()
has set
+ // the position to the first row in the range.
+ op = saveOp; // back to SC_EQUAL
+ }
}
else if( maParam.GetEntry(0).GetQueryItem().mbMatchEmpty
&& rDoc.IsEmptyData(nCol, maParam.nRow1, nCol, maParam.nRow2,
nTab))
@@ -337,8 +341,7 @@ void ScQueryCellIteratorBase< accessType, queryType
>::InitPos()
if( BinarySearch( nCol ))
lastRow = nRow;
}
- bool bFirstMatch = (bXLookUp && op != SC_EQUAL);
- AccessBase::InitPosFinish(beforeRow, lastRow, bFirstMatch);
+ AccessBase::InitPosFinish(beforeRow, lastRow, bXLookUp);
}
}
@@ -542,8 +545,13 @@ bool ScQueryCellIteratorBase< accessType, queryType
>::BinarySearch( SCCOL col,
{
if (fLastInRangeValue <= nCellVal)
{
- fLastInRangeValue = nCellVal;
- nLastInRange = i;
+ if (bXLookUp && nSortedBinarySearch != nSearchbDesc &&
fLastInRangeValue == nCellVal && aIndexer.getLowIndex() != i)
+ bDone = true;
+ else
+ {
+ fLastInRangeValue = nCellVal;
+ nLastInRange = i;
+ }
}
else if (fLastInRangeValue >= nCellVal)
{
@@ -585,8 +593,13 @@ bool ScQueryCellIteratorBase< accessType, queryType
>::BinarySearch( SCCOL col,
aCellStr);
if (nTmp <= 0)
{
- aLastInRangeString = aCellStr;
- nLastInRange = i;
+ if (bXLookUp && nSortedBinarySearch != nSearchbDesc &&
nTmp == 0 && aIndexer.getLowIndex() != i)
+ bDone = true;
+ else
+ {
+ aLastInRangeString = aCellStr;
+ nLastInRange = i;
+ }
}
else if (nTmp > 0)
{
@@ -654,8 +667,10 @@ bool ScQueryCellIteratorBase< accessType, queryType
>::BinarySearch( SCCOL col,
{
found = i;
nLastInRange = i;
- // But keep searching to find the last matching one.
- nLo = nMid + 1;
+ if (bXLookUp && (nSortedBinarySearch == nSearchbAscd &&
(rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp == SC_EQUAL)))
+ bDone = true;
+ else // But keep searching to find the last matching one.
+ nLo = nMid + 1;
}
else if (bAscending)
{
@@ -732,7 +747,7 @@ bool ScQueryCellIterator< accessType
>::FindEqualOrSortedLastInRange( SCCOL& nFo
nFoundCol = rDoc.MaxCol()+1;
nFoundRow = rDoc.MaxRow()+1;
- if (bXLookUp && !bSortedBinarySearch)
+ if (bXLookUp && nSortedBinarySearch == nBinarySearchDisabled)
SetStopOnMismatch( false ); // assume not sorted keys for XLookup
else
SetStopOnMismatch( true ); // assume sorted keys
@@ -747,7 +762,7 @@ bool ScQueryCellIterator< accessType
>::FindEqualOrSortedLastInRange( SCCOL& nFo
(maParam.GetEntry(0).eOp == SC_LESS_EQUAL || maParam.GetEntry(0).eOp
== SC_GREATER_EQUAL);
// assume not sorted properly if we are using XLookup with forward or
backward search
- if (bBinary && bXLookUp && !bSortedBinarySearch)
+ if (bBinary && bXLookUp && nSortedBinarySearch == nBinarySearchDisabled)
bBinary = false;
bool bFound = false;
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 6b82844f95ce..22fc30c23976 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -10642,7 +10642,7 @@ bool ScInterpreter::SearchRangeForValue(
VectorSearchArguments& vsa, ScQueryPara
aCellIter.SetAdvanceQueryParamEntryField(true);
aCellIter.SetXlookupMode(vsa.isXLookup);
// TODO: no binary search for column (horizontal) search
(use linear)
- aCellIter.SetSortedBinarySearchMode(false);
+ aCellIter.SetSortedBinarySearchMode(vsa.eSearchMode);
if (rEntry.eOp == SC_EQUAL)
{
if (aCellIter.GetFirst())
@@ -10807,13 +10807,14 @@ static bool lcl_LookupQuery( ScAddress &
o_rResultPos, ScDocument& rDoc, ScInter
SCROW nRow;
bool bLessOrEqual = rEntry.eOp == SC_LESS || rEntry.eOp ==
SC_LESS_EQUAL;
// we can use binary search if the SearchMode is searchbasc or
searchbdesc
- if (ScQueryCellIteratorSortedCache::CanBeUsed(rDoc, rParam,
rParam.nTab, cell, refData, rContext) ||
- (static_cast<SearchMode>(nSearchMode) == searchbasc &&
!bLessOrEqual) ||
- (static_cast<SearchMode>(nSearchMode) == searchbdesc &&
bLessOrEqual))
+ if ((static_cast<SearchMode>(nSearchMode) == searchbasc &&
!bLessOrEqual) ||
+ (static_cast<SearchMode>(nSearchMode) == searchbdesc &&
bLessOrEqual) ||
+ ScQueryCellIteratorSortedCache::CanBeUsed(rDoc, rParam,
rParam.nTab, cell, refData, rContext))
{
// search for the first LessOrEqual value if SearchMode is desc or
// search for the first GreaterOrEqual value if SearchMode is asc
ScQueryCellIteratorSortedCache aCellIter(rDoc, rContext,
rParam.nTab, rParam, false, false);
+ aCellIter.SetSortedBinarySearchMode(nSearchMode);
aCellIter.SetXlookupMode(bXlookupMode);
if (aCellIter.GetFirst())
{
@@ -10828,8 +10829,7 @@ static bool lcl_LookupQuery( ScAddress & o_rResultPos,
ScDocument& rDoc, ScInter
bool bReverse = (static_cast<SearchMode>(nSearchMode) ==
searchrev);
ScQueryCellIteratorDirect aCellIter(rDoc, rContext, rParam.nTab,
rParam, false, bReverse);
-
aCellIter.SetSortedBinarySearchMode(static_cast<SearchMode>(nSearchMode) ==
searchbasc ||
- static_cast<SearchMode>(nSearchMode) == searchbdesc);
+ aCellIter.SetSortedBinarySearchMode(nSearchMode);
aCellIter.SetXlookupMode(bXlookupMode);
if (aCellIter.FindEqualOrSortedLastInRange(nCol, nRow))
{
@@ -10847,10 +10847,11 @@ static bool lcl_LookupQuery( ScAddress &
o_rResultPos, ScDocument& rDoc, ScInter
bool bBinary = rParam.bByRow &&
(bLiteral || rEntry.GetQueryItem().meType ==
ScQueryEntry::ByValue);
- if( bBinary && (ScQueryCellIteratorSortedCache::CanBeUsed( rDoc,
rParam, rParam.nTab, cell, refData, rContext ) ||
- static_cast<SearchMode>(nSearchMode) == searchbasc ||
static_cast<SearchMode>(nSearchMode) == searchbdesc ))
+ if( bBinary && (static_cast<SearchMode>(nSearchMode) == searchbasc ||
static_cast<SearchMode>(nSearchMode) == searchbdesc ||
+ ScQueryCellIteratorSortedCache::CanBeUsed(rDoc, rParam,
rParam.nTab, cell, refData, rContext)))
{
ScQueryCellIteratorSortedCache aCellIter( rDoc, rContext,
rParam.nTab, rParam, false, false );
+ aCellIter.SetSortedBinarySearchMode(nSearchMode);
aCellIter.SetXlookupMode(bXlookupMode);
if (aCellIter.GetFirst())
{
@@ -10861,8 +10862,9 @@ static bool lcl_LookupQuery( ScAddress & o_rResultPos,
ScDocument& rDoc, ScInter
}
else
{
- bool bReverse = (static_cast<SearchMode>(nSearchMode) ==
searchrev);
- ScQueryCellIteratorDirect aCellIter( rDoc, rContext, rParam.nTab,
rParam, false, bReverse );
+ ScQueryCellIteratorDirect aCellIter( rDoc, rContext, rParam.nTab,
rParam, false,
+ static_cast<SearchMode>(nSearchMode) == searchrev);
+ aCellIter.SetSortedBinarySearchMode(nSearchMode);
aCellIter.SetXlookupMode(bXlookupMode);
if (aCellIter.GetFirst())
{
