Интересные формулы в Excel

Никогда не знаешь, какие формулы тебе понадобятся, Поэтому очередная порция интересного.

Учимся! Запоминаем! Используем!

варианты формул с полом (Фамилия Имя Отчество)

а) =ЕСЛИ(ПРАВСИМВ(B7;1)=»а»;»Ж»;ЕСЛИ(ПРАВСИМВ(B7;2)=»а «;»Ж»;»Ч»))

 

б) =ЕСЛИ(ПРАВСИМВ(C3;1)=»а»;»Ж»;ЕСЛИ(ПРАВСИМВ(C3;2)=»а «;»Ж»;ЕСЛИ(ПРАВСИМВ(C3;2)=»ч»;»Ч»;ЕСЛИ(ПРАВСИМВ(C3;1)=»ч»;»Ч»;»?»))))

 

в) =ЕСЛИ(ПРАВСИМВ(Л ЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);6)=»Микола»;»Ч»;ЕСЛИ(ПРАВСИМВ( Л ЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);4)=»Ілля»;»Ч»;ЕСЛИ(ПРАВСИМВ( Л ЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);1)=»і»;»Ж»;ЕСЛИ(ПРАВСИМВ( Л ЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);6)=»Микита»;»Ч»;ЕСЛИ(ПРАВСИМВ( Л ЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);2)=»ов»;»Ж»;ЕСЛИ(ПРАВСИМВ( Л ЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);1)=»я»;»Ж»;ЕСЛИ(ПРАВСИМВ( Л ЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);1)=»а»;»Ж»;»ч»)))))))

 

г) =ЕСЛИ(ПРАВСИМВ(ЛЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);6)=»Микола»;»Ч»;ЕСЛИ(ПРАВСИМВ(ЛЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);4)=»Ілля»;»Ч»;ЕСЛИ(ПРАВСИМВ(ЛЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);1)=»і»;»Ж»;ЕСЛИ(ПРАВСИМВ(ЛЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);6)=»Микита»;»Ч»;ЕСЛИ(ПРАВСИМВ(ЛЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);2)=»ов»;»Ж»;ЕСЛИ(ПРАВСИМВ(ЛЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);1)=»я»;»Ж»;ЕСЛИ(ПРАВСИМВ(ЛЕВСИМВ(C3;ПОИСК(» «;C3;ПОИСК(» «;C3;1)+1)-1);1)=»а»;»Ж»;»ч»)))))))

 

формула для определения квартала из даты
=ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)

 

формула для ГГГГ-К
=СЦЕПИТЬ(ГОД(C2);»-«;ЦЕЛОЕ((МЕСЯЦ(C2)+2)/3))

 

формула для ГГГГ-К из год-месяц
=СЦЕПИТЬ(ЛЕВСИМВ(C26;4);»-«;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»январь»;1;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»февраль»;1;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»март»;1; ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»апрель»;2;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»май»;2;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»июнь»;2; ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»июль»;3;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»август»;3;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»сентябрь»;3; ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»октябрь»;4;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»ноябрь»;4;ЕСЛИ(ЗАМЕНИТЬ(C26;1;6;)=»декабрь»;4;»?»)))))))))))))

 

формула для замены  в первой колонке пустых значений верхними

=ЕСЛИ(A2=»»;B1;A2)

 

??? формула для нахождения непересекающегося «кусочка» дат (кому надо тот поймет)

B — колонка с датами начала

C — колонка с датами окончания

O  -колонка «какой раз повторяется объект»

S2=ЕСЛИ(O2=1;B2;S1) — первая дата начала

T2=ЕСЛИ(O2=1;C2;ЕСЛИ(T1+1<B2;T1;ЕСЛИ(T1>=C2;T1;C2))) — первая дата окончания

U2=ЕСЛИ(O2=1;»»;ЕСЛИ(T1+1<B2;B2;»»)) — вторая дата начала

V2=ЕСЛИ(O2=1;»»;ЕСЛИ(T1+1<B2;C2;»»)) — вторая дата окончания

*если есть два раздельных отрезка времени, но это не последний объект, то лучше вводить доп. проверку

————————————————————-

для 3-х:

S2=ЕСЛИ(O2=1;B2;S1) — первая дата начала

T2=ЕСЛИ(O2=1;C2;ЕСЛИ(T1+1<B2;T1;ЕСЛИ(T1>=C2;T1;C2))) — первая дата окончания

U2=ЕСЛИ(O2=1;»»;ЕСЛИ(U1=»»;ЕСЛИ(T1+1<B2;B2;»»);ЕСЛИ(V1+1>B2;B2;U1))) — вторая дата начала

V2=ЕСЛИ(O2=1;»»;ЕСЛИ(V1=»»;ЕСЛИ(T1+1<B2;C2;»»);ЕСЛИ(V1+1<B2;V1;ЕСЛИ(V1>=C2;V1;C2)))) — вторая дата окончания

W2=ЕСЛИ(ИЛИ(U1=»»;U2=»»;O2=1;O2=2);»»;ЕСЛИ(V1+1<B2;B2;»»)) — третья дата начала

X2=ЕСЛИ(ИЛИ(U1=»»;U2=»»;O2=1;O2=2);»»;ЕСЛИ(V1+1<B2;C2;»»)) — третья дата окончания

 

 

формула для удаления первых 3-х символов

=ЗАМЕНИТЬ(А1;1;3;)

 

формула для определения номера месяца

=ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»январь»;1;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»февраль»;2;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»март»;3; ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»апрель»;4;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»май»;5;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»июнь»;6; ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»июль»;7;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»август»;8;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»сентябрь»;9; ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»октябрь»;10;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»ноябрь»;11;ЕСЛИ(ЗАМЕНИТЬ(G2;1;6;)=»декабрь»;12;»?»))))))))))))

 

формула для определения номера месяца в связке с годом (ГГГГ-ММ)

=СЦЕПИТЬ(ЛЕВСИМВ(C2;4);»-«;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»январь»;1;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»февраль»;2;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»март»;3; ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»апрель»;4;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»май»;5;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»июнь»;6; ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»июль»;7;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»август»;8;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»сентябрь»;9; ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»октябрь»;10;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»ноябрь»;11;ЕСЛИ(ЗАМЕНИТЬ(C2;1;6;)=»декабрь»;12;»?»)))))))))))))

 

формула «имя листа»

=ПСТР(ЯЧЕЙКА(«filename»;A1);НАЙТИ(«]»;ЯЧЕЙКА(«filename»;A1))+1;65535)

 

формула удалить лишнии пробелы

=СЖПРОБЕЛЫ(D2)

 

формула количество слов

=ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(D2))=0;0;ДЛСТР(СЖПРОБЕЛЫ(D2))-ДЛСТР(ПОДСТАВИТЬ(D2;» «;»»))+1)

 

Как вычислить возраст или стаж работы в Excel

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

«y» разница в полных годах

«m» в полных месяцах «d» в полных днях

«yd» разница в днях с начала года без учета лет

«md» разница в днях без учета месяцев и лет «ym» разница в полных месяцах без учета лет Например,

=РАЗНДАТ(имя ячейки или дата рождения человека дд.мм.гг; СЕГОДНЯ() или имя ячейки с расчетной датой; «y»)

Самое мощное применение этой функции это вывод точного стажа работы или возраста =РАЗНДАТ(A1;A2;»y») & » г. » & РАЗНДАТ(A1;A2;»ym») & » мес. » & РАЗНДАТ(A1;A2;»md») & » дн.» где А1 — ячейка с датой поступления на работу, А2 — с датой увольнения.