Jooksev loendur – Exceli valemi kummaline käitumine

Komistasin probleemi otsa Excelis ja kuna leidsin lahenduse, siis ehk on kellelgi sama probleemi olnud

Sisu järgmine. Mul vaja loendada, mitmendat korda mingi väärtus (nimetus) esineb tabelis

Konkreetne näide on Kevadsulgede regimistabelist. Siin on vaja, et iga liigatunnuse järele tekiks järjekorranumber, mitmes paar liigas

Saadud tulemuse sain nagu ikka valemiga:

=COUNTIF($C$2:C75;C75)
=COUNTIF($C$2:C76;C76)

See on valem, mis loendab just nii nagu mul vaja ja lohistades ülevalt alla muutub just nii nagu mul vaja. Kõik oli kena kuni Excel 2010ni (ma arvan, et Excel 2007 ajal asi toimis, sest mullustel võistlustel mul mingit probleemi ei olnud selle valemiga). Tabelisse ridu lisades valem kopeeritakse ilusti ülevalt alla nagu peab. Toodud valem aga muutub veidraks, kusjuures Excel muudab valemi vigaseks alates viimasest korrektsest reast. Lisandunud reaga on valem selline:

=COUNTIF($C$2:C75;C75)
=COUNTIF($C$2:C77;C76)
=COUNTIF($C$2:C77;C77)

Peale teise rea lisamist selline:

=COUNTIF($C$2:C75;C75)
=COUNTIF($C$2:C78;C76)
=COUNTIF($C$2:C78;C77)
=COUNTIF($C$2:C78;C78)

Põhjus on selles, et tabeli valemianalüüsija interpreteerib seda valemit valesti. Üldse on parem tabelites A1-tüüpi valemeid vältida, aga no vahel ei saa

Leidsin ka võimaluse, kuidas sedalaadi probleemi vältida ja kirjeldada valem Tabelivalemina. Pisut keeruline tundub, aga vähemalt toimib

    =COUNTIF((TableName[[#Headers];[ColumnName]]):([@ColumnName]);[ColumnName])

Või eestikeelses variandis

    =COUNTIF((TableName[[#Päised];[ColumnName]]):([@ColumnName]);[ColumnName])

Ehk on abiks analoogilise mure tekkimisel sullegi

Henn

PS! Peale selle loo avaldamist leidsin ka teise lahenduse. Seekord ei kasuta tabelivalemit (kuigi ka sellega saaks) vaid vana head OFFSET funktsiooni:

    =COUNTIF(OFFSET(C76;2-ROW();0;ROW()-1;1);C76)

Töötab ilusti ka see J

H

This entry was posted in Excel, Funktsioonid, Office. Bookmark the permalink.

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s