Skip to content

Instantly share code, notes, and snippets.

@heetbeet
Last active February 12, 2020 14:57
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save heetbeet/64cdfce26c2e4420ed416028b1d22393 to your computer and use it in GitHub Desktop.
When does excel use a single item from a named range v.s. all the items?
'''
This script serves as and answer to the stack overflow question
https://stackoverflow.com/questions/59652623/when-does-excel-use-a-single-item-from-a-named-range-v-s-all-the-items
'''
import urllib.request
from path import Path
import collections
class ddict(dict):
def __init__(self, **kwds):
self.update(kwds)
self.__dict__ = self
#%% I manually added //True to variables with singlelike behaviour
#From https://github.com/LibreOffice/core/blob/0cb4b2a18b790ff74a6893781beba2c4a4a8ce8f/sc/source/filter/excel/xlformula.cxx
types = '''
#define RO { EXC_PARAM_REGULAR, EXC_PARAMCONV_ORG, false } // True
#define RA { EXC_PARAM_REGULAR, EXC_PARAMCONV_ARR, false } // 0
#define RR { EXC_PARAM_REGULAR, EXC_PARAMCONV_RPT, false } // True
#define RX { EXC_PARAM_REGULAR, EXC_PARAMCONV_RPX, false } // 0
#define VO { EXC_PARAM_REGULAR, EXC_PARAMCONV_ORG, true } // True
#define VV { EXC_PARAM_REGULAR, EXC_PARAMCONV_VAL, true } // True
#define VA { EXC_PARAM_REGULAR, EXC_PARAMCONV_ARR, true } // 0
#define VR { EXC_PARAM_REGULAR, EXC_PARAMCONV_RPT, true } // True
#define VX { EXC_PARAM_REGULAR, EXC_PARAMCONV_RPX, true } // 0
#define RO_E { EXC_PARAM_EXCELONLY, EXC_PARAMCONV_ORG, false } // True
#define VR_E { EXC_PARAM_EXCELONLY, EXC_PARAMCONV_RPT, true } // True
#define C { EXC_PARAM_CALCONLY, EXC_PARAMCONV_ORG, false } // True
'''.strip()
#%% parse into a mapping
varmap = {}
for line in types.split('\n'):
lst = line.split()
varmap[lst[1]] = eval(lst[-1])
#%% Read the source files where the opcodes and functions are described
formulastxt = urllib.request.urlopen('https://raw.githubusercontent.com/LibreOffice/core/1d10db1003f092f48ed09a0d45994f7481b96d84/sc/source/filter/oox/formulabase.cxx').read().decode('utf-8')
#%% These table are in other spreadsheet software and not in Excel
exclude_tables = {'saFuncTableOdf','saFuncTableOOoLO'}
#%% Get all the excel functionname table entries
macrolines = []
funclines = []
for i in formulastxt.split('const FunctionData saFunc')[1:]:
tablename = 'saFunc'+i.split('[')[0]
if tablename in exclude_tables:
continue
tablecontent = i.split('\n{')[1].split('\n};')[0]
tablecontent = '\n'.join([i for i in tablecontent.split('\n') if i.strip().split('//')[0] != ''])
for line in tablecontent.split('\n'):
if not '{' in line:
continue
line = line.replace('{', " ")
line = line.split('//')[0]
if line.strip().startswith('nullptr'):
continue
funclines.append([i.strip() for i in line.split('}')[0].split(',')])
#%% Make a dict with functions and their argumnent information
funcdict = {} #collections.defaultdict(list)
for func in funclines:
_min=int(str(func[4]).replace('MX', '255'))
_max=int(str(func[5]).replace('MX', '255'))
rettype=func[6]
argtypes=func[7:]
for i in range(len(argtypes)):
argtypes[i] = argtypes[i].replace('_E', '')
if argtypes == ['']:
argtypes = []
argsingle = []
else:
argsingle = [varmap[i]==True for i in argtypes]
func[1] = func[1].replace('"',"").strip()
funcdict[func[1]] = ddict(min = _min,
max = _max,
rettype = rettype,
argtypes = argtypes,
argsingle = argsingle)
#Fix this out-of-whack one
funcdict['AGGREGATE'] = ddict(**{'min': 3,
'max': 255,
'rettype': 'V',
'argtypes': ['VR', 'RO', 'RX'],
'argsingle': [True, True, False]})
#%% Find all functions with any vectorlike arguments
for fname in sorted(funcdict.keys()):
args = funcdict[fname]
if args.max == 0: continue
arrayargs = []
for i, t in enumerate(args.argtypes):
if not(varmap[t]):
arrayargs.append(i+1)
#print the function
if arrayargs:
print(fname +
' ( ' +
', '.join([v if i+1 in arrayargs else '□' for i,v in enumerate(args.argtypes)]) +
('' if len(args.argtypes) == args.max else ', …') +
' )'
)
@heetbeet
Copy link
Author

heetbeet commented Jan 10, 2020

Output:

AGGREGATE ( □, □, RX, … )
AND ( RX, … )
AVEDEV ( RX, … )
AVERAGE ( RX, … )
AVERAGEA ( RX, … )
CHISQ.TEST ( VA, … )
CHITEST ( VA, … )
CORREL ( VA, … )
COUNT ( RX, … )
COUNTA ( RX, … )
COVAR ( VA, … )
COVARIANCE.P ( VA, … )
COVARIANCE.S ( VA, … )
DEVSQ ( RX, … )
F.TEST ( VA, … )
FORECAST ( □, VA, … )
FORECAST.ETS ( □, VA, □, … )
FORECAST.ETS.CONFINT ( □, VA, □, … )
FORECAST.ETS.SEASONALITY ( □, VA, □, … )
FORECAST.ETS.STAT ( □, VA, □, … )
FORECAST.LINEAR ( □, VA, … )
FREQUENCY ( RA, … )
FTEST ( VA, … )
FVSCHEDULE ( □, RX )
GCD ( RX, … )
GEOMEAN ( RX, … )
GROWTH ( RA, RA, RA, □ )
HARMEAN ( RX, … )
IMPRODUCT ( RX, … )
IMSUM ( RX, … )
INDEX ( RA, □, … )
INTERCEPT ( VA, … )
IRR ( RA, □ )
ISREF ( RX )
KURT ( RX, … )
LARGE ( RX, □ )
LCM ( RX, … )
LINEST ( RA, RA, □, … )
LOGEST ( RA, RA, □, … )
LOOKUP ( □, RA, … )
MATCH ( □, RX, □ )
MAX ( RX, … )
MAXA ( RX, … )
MDETERM ( VA )
MEDIAN ( RX, … )
MIN ( RX, … )
MINA ( RX, … )
MINVERSE ( VA )
MIRR ( RA, □, … )
MMULT ( VA, … )
MODE ( VA, … )
MODE.MULT ( VA, … )
MODE.SNGL ( VA, … )
MULTINOMIAL ( RX, … )
NETWORKDAYS ( □, □, RX, □, … )
NETWORKDAYS.INTL ( □, □, □, RX )
NPV ( □, RX, … )
OR ( RX, … )
PEARSON ( VA, … )
PERCENTILE ( RX, □ )
PERCENTILE.EXC ( RX, □ )
PERCENTILE.INC ( RX, □ )
PERCENTRANK ( RX, □, □ )
PERCENTRANK.EXC ( RX, □, □ )
PERCENTRANK.INC ( RX, □, □ )
PROB ( VA, VA, □, … )
PRODUCT ( RX, … )
QUARTILE ( RX, □ )
QUARTILE.EXC ( RX, □ )
QUARTILE.INC ( RX, □ )
RSQ ( VA, … )
SERIESSUM ( □, □, □, RX )
SKEW ( RX, … )
SKEW.P ( RX, … )
SLOPE ( VA, … )
SMALL ( RX, □ )
STDEV ( RX, … )
STDEV.P ( RX, … )
STDEV.S ( RX, … )
STDEVA ( RX, … )
STDEVP ( RX, … )
STDEVPA ( RX, … )
STEYX ( VA, … )
SUM ( RX, … )
SUMPRODUCT ( VA, … )
SUMSQ ( RX, … )
SUMX2MY2 ( VA, … )
SUMX2PY2 ( VA, … )
SUMXMY2 ( VA, … )
T.TEST ( VA, VA, □, … )
TREND ( RA, RA, RA, □ )
TRIMMEAN ( RX, □ )
TTEST ( VA, VA, □, … )
TYPE ( VX )
VAR ( RX, … )
VAR.P ( RX, … )
VAR.S ( RX, … )
VARA ( RX, … )
VARP ( RX, … )
VARPA ( RX, … )
WORKDAY ( □, □, RX, □, … )
WORKDAY.INTL ( □, □, □, RX )
XIRR ( RX, RX, □ )
XNPV ( □, RX, RX )
XOR ( RX, … )
Z.TEST ( RX, □, … )
ZTEST ( RX, □, … )

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment