Рожденни дни и възраст в Ексел

oldstar трудност:

видян: 13971

Предлгат се доста начини за маркиране, броене и изчисляване на възраст – чрез различни формули и макроси. Тук ще ви покажа няколко варианта за намиране на рожденници в списъци с рожденни дати и ЕГН и други, по едни от по-лесните (без много сложни формули и макроси) начини.

Уточнения:

1. Важно: Всички рожденни дати и ЕГН по-долу са случайно генерирани и нямат нищо общо с конкретни лица.

2. За маркирането (оцветяването) на рожденници се прилагат формули чрез Conditional Formatting. Достъпът до него :

- 2003 - Format-Conditional Formatting – Formula is

- 2007 и нагоре – Home-Conditional Formatting –New Rule - Use formula to determinate which cell to format. Всички други формули се изписват директно

3. Формулите тук са съобразени с адресите на клетките в приложените картинки-примери и с разделител (;) –точка и запетая. При изготвянето на собствен файл, намерете съответствията в адресите.

I. ТЪРСЕНЕ ОТ СПИСЪЦИ С РОЖДЕННА ДАТА

А). Най-простия и лесен начин е директното търсене на рожденници в определен списък.Попълваме списъка с рожденните дати или ЕГН. Колоната, в които ще въвеждат рожденните дата трява да бъде форматирани като текст-Format Cells-Text

В отделна клетка (В1) изписваме датата или ЕГН, които търсим. Mаркирайки от A5 до В12, въвеждаме формулата в Conditional formatting: =$B$1=($B5:$B12) и посочваме цвят. Броенето (в В2) е с формула =COUNTIF(B5:B12;B1)

Б) Рожденници през конкретен месец

Показва рожденници през определен месец, без значение година на раздане.Може да стане по два начина- чрез номер на месец или име на месец.

Б1) При номер на месец става по сления начин:

1. В отделна помощна колона (напр. в колона E) изписваме от 1 до 12 (номерата на месеците)-от E1 до E12

2.Маркираме клетка до списъка (напр.B1) и Data-Validation. В прозорчето Allow маркираме List,

a в Source маркираме номерата на месеците или изписваме $E$1:$E$12 и получаваме падащо меню с номерата на месеците

3.В друга близка (помощна) колона (D) извличаме само номера на месеца от рожденната дата. В клетка D5 прилагаме формулата =MONTH(B5), която копираме до края на списъка.

4.Оцветяването става чрез Conditional Formatting. Маркираме областта А5:В12. Формулата за

оцветяване (за примера) е: =$B$1=MONTH($B5:$B12) със съответен цвят

5. За преброяването прилагаме формулата: =COUNTIF(D5:D12;B1)

Забележка: Ако не се нуждаем от преброяване, а само от маркиране, не се прилагат т.3 и 5.

6. Скриваме помощните колонки.

Б2) Другият начин (чрез име на месец) е малко по-опростен

1. В отделна помощна колона (напр. в колона E) изписваме на месеците-от E1 до E12

2. В отделна клетка (напр. в D1) превръщаме името на месеца в номер чрез формулата =MONTH(1&В1). Получаваме 2.(Ако се появи дата, клетката се форматира –General)

3. Оцветяването става със формула (маркираме A5:B12) =$D$1=MONTH($B5:$B12).

4. Тук за преброяването прилагаме друга формула: =SUMPRODUCT(--(MONTH(B5:B12)=(D1)))

5. Скриваме помoщните колонки

При всяка смяна на месеца от падащото меню, рождениците се маркират и преброяват автоматично.

В.Рожденници на точно определена дата (дата и месец или точна рожденна дата)

В1) По номер на месец и дата

1.Отбелязваме две клетки – за месеца, датата и годината (B1, В2)

2.Валидираме клетката за месеца като номер, напр. В колона Е (чрез изписване на месеците като номер в помощна колонка-виж по-горе за месеци)

За да можем да намерим и преброим рожденниците, трябва да имаме някаква база за сравнение. Това става като конвертираме датите така, че да имат общ признак. Най-лесно става като уеднаквим само годината, т.е. всички дати да са еднаква година. Тук предлагам един от начините. Годината може да бъде произволна (но след 1904). За примера ще използваме 2013.

а) Чрез падащото меню за месеците посочваме произволен месец, а в клетка за датата-също произволна дата.

б) в отделна клетка (D1) изписваме формулата =DATE(2013;B1;B2), т.е. [година; месец; ден]. Датата си си присвоява година 2013.

в) В същата колона (D), във клетка D8 прилагаме формулата

=DATE(2013;MONTH(B8);DAY(B8)), която копиране надолу до края на списъка.

г) Оцветяването става с формулата (маркираме област А8:В15) =$D$1=($D8:$D15)

д) Преброяването става с формулата =COUNTIF (D8:D15;D1)

3. Скриваме помощните колони

В2) Може да комбинираме споненатите функции с една допълнителна –търсене по точна дата. Добявяме нова клетка В3- Точна дата.

Ще комбинираме 2 задачи - ако не е вписана точната рожденна, калкулаторът да показва и преброи тези които са родени на посочения месец и дата, без значение годината. Втората задача е, ако е вписана точната дата, да показва и преброи рожденниците на тази дата.

Вкарваме тези условия в Conditional Formatting, вместо предишното условие

Първо условие с един цвят – маркираме А8:В15

=IF($B$3="";($D$1)=($D8:$D15))

Второ условие с различен цвят- същото маркиране

=IF($B$3<>"";$B$3=($B8:$B15))

Така изписаните формули показват:

а) когато са посочени само месеца и датата (липсва точно посочена дата), калкулаторът показва родените през месеца и на датата, но през различни години

б) ако е попълнена точно посочена дата, калкулаторът показва само тези, които са родени на тази точна дата.

За преброяване в първия случай формулата е =COUNTIF(D8:D15;D1)

За втория =COUNTIF(B8:B15;B3)

От примера се вижда, че на 18.2 (18 Февруари) са родени двама души през различни години и те са маркирани При отбелязване на точната дата (19.2.1954), родения е един.

Забележка:Точната дата може да се ползва и за директно самостоятелно неззависимо търсене.

В3) По име на месец и дата

Тук се обединява два начина за броене и оцветяване-този за този за конкретен месец и този за точна определена дата.

1. Определяме 2 клетки, B1 за месец като име и В2 за дата.

2.В отделна помощна колона (напр. в колона E) изписваме на месеците-от E1 до E12

3.Валидираме клетката за месец като име както по горе

4.В отделна клетка (напр. в D1) превръщаме името на месеца в номер чрез формулата =MONTH(1&В1). Получаваме напр. 1 за Януари (Ако се появи дата, клетката се форматира –General)

5.В клетка D2 директно присвояване на дата и месеца произволна година с формулата
=date(2013; D1;B2)

6.От тук нататък стъпките са същите, както са описани в т В1 -г) и д) и В1.3

Забележка: При това конвертиране в примерите, при празна колона В (рожденна дата), колона D ри се появява предишна година, което е нормално. При попълване на рожденните дати, при конвертирането, датати си присвояват указаната година.

II. ИЗВЛИЧАНЕ РОЖДЕННА ДАТА OT ЕГН

Важно: Поради факта, че някои ЕГН започват с 0, преди въвеждането им, предварително колоната с ЕГН трябва да се форматира като текст (Format cells-Text)

Чрез ЕГН също може да се намери броя на рожденници и да се изчисли възрастта. Някои автори предлагат доста дълги и сложни формули.. Тук ще използваме малко заобиколен начин, но с по-прости формули

В Единния граждански номер първата и втората цифра обозначават годината на раждане, третата и четвърта цифра – месеца на раждане, а петата и шеста –датата..

Ще се прилагат почти същите формули и начини както по-горе

Тук обаче има една особеност. За всики родени след 1999 към месеца се прибявя 40 или март 2000 в ЕГН става 0043** или ноември 2011 – 1151** Затова предлагам два варианта:

A) За родени между 1900-1999

1. Ако искаме да търсим директно по ЕГН, използваме начина по-горе (I.A).

2. Търсене и преброяване чрез конвертиране

Към списъка с имената и ЕГН добавме още 1 колона (С) – за извличане на точната дата. В нея (С6) изписваме формулата, която копираме до края =DATE(MID(B6;1;2);MID(B6;3;2);MID(B7;5;2 ))

а) Формула за оцветяване (маркираме областа А6:С14) в Conditional Formatting

=$C$1=($C6:$C14)

б) Формула за преброяване

=COUNTIF(C6:C14;C1)

Забележка: При празна колона В, в колона С се появява грешка (#VALUE).С въвеждането на данни, грешката изчезва. Ако искаме да скрием грешката, добавяме едно допълнително условие в Conditional Formatting. Маркираме С6:С14 и формулата е =ISERROR(C6:C14) и форматираме с бял шрифт.

Б) За родени между 2000-2099 (Прилага се и за периода 1900-2099)

Поради промяната в записа за месец за родените след 1999, неоходима е нова формула.

1. Правим 4 допълнитени колони към списъка с ЕГН – Година, Месец, Ден, Рожденна дата

а) в колона Е прилагаме формулата =INT(MID(B3;5;2))

б) в колона D пролагаме формулата =MOD(INT(MID(B3;3;2));40)

в) в колона С прилагаме формулата =IF(OR(INT(MID(B3;3;2))>40);MID(B3;1;2)+2000;(MID(B3;1;2)+1900)

г) в колона F прилагаме формулата =DATE(C3;D3;E3)

2. След въвеждането на формулите, при празна колона В, навсякъде излиза грешка (#VALUE).

3. Скриваме колони C, D и Е,

4. За колона F прилагаме горната формула за грешка =ISERROR(F2:F9) и форматиране с бял шрифт

Тук с познатите вече формули може да се оцветява или брои по ЕГН или рожденна дата

Уточнение: За директно конвертиране може да се използва преглаганата в някои форуми доста сложна формула, където в А1 е изписано ЕГН

=DATE(1900+LEFT(A1;2)+INT(MID(A1;3;2)/40)*100;MOD(MID(A1;3;2);40);MID(A1;5;2))

III. ДРУГИ ФОРМУЛИ

A) Изчисляване възраст

Независимо как ще се въведе рожденната дата-дали директно или извлечена от ЕГН, точната възраст може да се изчисли в години, месеци дни.

1 Правим табличка със следните колони: Име, рожденна дата, посочена дата, години месеци, дни. В карайна дата

2. на колонката (без антетката) Години прилагаме формулата =DATEDIF($B2;$C2;”y”)

3. на колонката Месеци =DATEDIF($B2;$C2;”ym”)

4. на колонката Дни =DATEDIF($B2;$C2;”md”)

Сега като напшем в Крайна дата произволна дата, интервалът между двете дати (рожденната и датата на събитието) се показва в години, месеци и дни. Ако се напише текущата дата, в клетките се показва точната възраст на лицето

За колоните D, E и F прилагаме формулата за грешка

Б) Отброяване дни до/от рожден ден до текуща дата

Когато в А2 се въведе датата на предтоящ или минал рожден ден, в С2 се показват дните до/от посочената дата. В А2 може да се въведе дата и на предстоящо или минало събитие. Разликата и в двата случая е към текущата дата. Формулите са:

В С2 =IF(A2-TODAY()=0;0;(A2-TODAY()))

В В2 =IF(C2>0;"Остават";IF(C2=0;"Честито!";IF(C2<0;"Минали"))) Клетка може да сеоцвети чрез Conditional Formatting при различните условия.

Забележки:

а) Формулата в В2 е условна и не е задължителна.

Б) за да се скрият данните при празна А2, маркира се B2:B Conditional formatting постявяме условие =$А$=”” 9 форматираме с бял шрифт.

Материалът (урокът) е авторски и при копиране на целия урок, части или формули от него, моля да се споменава автора и източника.

Автор: oldstаr

Сподели |

Рожденни дни и възраст в Ексел

Регистрирайте се, за да добавите коментар