#007 Контролно число на ЕГН с една формула

Този пример спокойно може да го сложите в групата "не правете така в къщи":) Тази формула е толкова сложна, че не я препоръчвам дори на себе си. Една малка грешка и се получава грешен резултат. Но в крайна сметка се амбицирах преди време и реших да я направя.

Проблем: Да се изчисли контролната цифра на ЕГН в клетката A1.
Отговор: =IF(MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})),11)=10, 0, MOD(SUMPRODUCT(VALUE(MID(A1, ROW( INDIRECT("1:9")) ,1))*({2;4;8;5;10;9;7;3;6})),11))

Nice А?:):) Ето анализът на формулата:

Контролно число: Последната цифра на ЕГН-то се нарича контролна и се изчислява по следния алгоритъм:
1. Всяка от първите девет цифри се умножава по съответно тегло. Теглата са: 2,4,8,5,10,9,7,3,6
2. Намира се сумата на тези произведения
3. Намира се остатъкът от делението на 11 на намерената в точка две сума
4. Ако остатъкът е по-малък от 10 това е контролното число, ако е равен на 10 контролното число е 0!

1. Извличане на първите девет цифрите от ЕГН-то  една по една: VALUE(MID(A1,ROW(INDIRECT("1:9")),1))

 Тук се симулира цикъл във формула. Няколко думи за неговото осъществяване:

• Ако стойностите не са много може просто да изброят заградени в {} и разделени със "," (хоризонтален масив)! В нашия случай трябваше да се напише {1,2,3,4,5,6,7,8,9}, което като се замисля не изглежда прекалено дълго и зле:)
• Когато стойностите са повече се използва конструкцията Row(начало:край). Например Row(1:9) или Row(1:999).... Тази конструкция има един недостатък. При местене на формулата тя се "настройва" за новото място. Не ни спасява и "трикът" с използването на абсолютни адреси например Row($1:$9). В този случай формулата не се променя  при преместване, но става каша ако изтриваме или вмъкваме редове в района на първи и девети ред. За това използвайте този метод предпазливо.
• Най-стабилния е метода използван в примера ROW(Indirect("начало:край"). В този случай формулата не зависи от "околната среда" и нейното местоположение:) Ще използвам този начин за правене на вътрешен цикъл и  в други примери.

Функцията Value преобразува извлечение символ в цифра. В интерес на истината съм подходил доста "консервативно" към проблемът, но и без това формулата си е сложна да правя други трикове в нея:) Като резултат се получава масив от първите девет цифри.

2. Сума на произведенията на цифрите и теглата:

SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6}))

Тук трябва да се обърне внимание на масивът с теглата. Той е "вертикален"! Ха сега:) Пак ви намерих занимание за четене:) Ето тук (на англйски): http://office.microsoft.com/en-us/excel-help/more-arrays-introducing-array-constants-in-excel-HA001087291.aspx Тази "подробност" ми коства около час когато правих формулата де;) Т.е. е важно, че разделителят на елементите е ";" а не ","! (тук възниква въпросът как се отбелязват вертикалните и хоризонталните масиви при друг вид езикови настройки (друг десетичен знак и друг знак за разделител на списъци) за което ще пиша в коментар под темата когато го тествам!)

Няма да се спирам защо се използва SumProduct а не Sum (четете по-старите теми)!

3. Намиране остатък от делението на 11.Нищо сложно при използването на функцията Mod.

MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})) ,11)

4. Поставяне в If конструкция =IF(остатък=10,0,остатък).

Ми това е:)