Post date: Mar 9, 2016 3:08:49 PM
Для того щоб формули можна було вставляти в будь яке місце на листі (щоб формули не мали абсолютної А1B2, а адресацію (рядки колонки R1C1, потрібно поставити галочку в Excel в лаштуваннях
"файл" > "Параметры" > "Формули" > "Работа з формулами" > "стиль ссылок R1C1"
в налаштуваннях Winodows системним роздільником списків потрібно поставити замість "крапки з комою" поставити "кому", інакше дивитись розділ рос";"
Формули нижче подані для Україномовного МS Office 2016 , за адресації RC
Якщо у вас Російськомовний офіс потрібно завантажити приклад example.xlsx (внизу сторінки посилання) на комп'ютер і переглянути формули, що подаються.
Розставити двокрапки в суцільному номері
На вході 6822455800050040172 на виході 6822455800:05:004:0172
=LEFT(RC[-1],10)&":"&RIGHT(LEFT(RC[-1],12),2)&":"&RIGHT(LEFT(RC[-1],15),3)&":"&RIGHT(RC[-1],4)
Перетворити кадастровий в суцільний номер
На вході 6822455800:05:004:0172 на виході 6822455800050040172
укр","
=SUBSTITUTE(RC[-1],":","")
=SUBSTITUTE(A1,":","")
рос";"
=ПОДСТАВИТЬ(RC[-1];":";"")
=ПОДСТАВИТЬ(A1;":";"")
Формула для тих хто знає iMacros
На вході 6822455800:05:004:0172 на виході 6822455800:05:004:0172,6822455800050040172
укр","
=RC[-1]&","&SUBSTITUTE(RC[-1],":","")
=A1&","&SUBSTITUTE(A1,":","")
рос","
=RC[-1]&","&ПОДСТАВИТЬ(RC[-1],":","")
=A1&","&ПОДСТАВИТЬ(A1,":","")
рос";"
=RC[-1]&","&ПОДСТАВИТЬ(RC[-1];":";"")
=A1&","&ПОДСТАВИТЬ(A1;":";"")
На вході 6822455800050040172.xml або город094927_петренка_6822455800050040172.xml на виході 6822455800:05:004:0172
укр","RC[-1]
=LEFT(RIGHT(LEFT(RC[-1],LEN(RC[-1])-4),19),10)&":"&RIGHT(LEFT(RIGHT(LEFT(RC[-1],LEN(RC[-1])-4),19),12),2)&":"&RIGHT(LEFT(RIGHT(LEFT(RC[-1],LEN(RC[-1])-4),19),15),3)&":"&RIGHT(RIGHT(LEFT(RC[-1],LEN(RC[-1])-4),19),4)
рос";"A5
=ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(A5;ДЛСТР(A5)-4);19);10)&":"&ПРАВСИМВ(ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(A5;ДЛСТР(A5)-4);19);12);2)&":"&ПРАВСИМВ(ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(A5;ДЛСТР(A5)-4);19);15);3)&":"&ПРАВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(A5;ДЛСТР(A5)-4);19);4)
рос";"RC[-1]
=ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(RC[-1];ДЛСТР(RC[-1])-4);19);10)&":"&ПРАВСИМВ(ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(RC[-1];ДЛСТР(RC[-1])-4);19);12);2)&":"&ПРАВСИМВ(ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(RC[-1];ДЛСТР(RC[-1])-4);19);15);3)&":"&ПРАВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(RC[-1];ДЛСТР(RC[-1])-4);19);4)
До дати додати повну кількість років, з округленням до щонайближчого мінімального цілого значення:
На вході 5 12.04.2012 на виході 12.04.2017
=DATEVALUE(TEXT(DAY(RC[-1]),"00")&"."&TEXT(MONTH(RC[-1]),"00")&"."&YEAR(RC[-1])+ROUND(RC[1],0))
Зібрати чотири ліві колонки і проставити двокрапки
На вході 6822455800 05 004 0172 на виході 6822455800:05:004:0172
=RC[-4]&":"&RC[-3]&":"&RC[-2]&":"&RC[-1]
на вході колонки без ведучих нулів, потрібно розтавити двокрапки з форматом
На вході 6822455800 5 4 172 на виході 6822455800:05:004:0172
=TEXT(RC[-4],"0000000000")&":"&TEXT(RC[-3],"00")&":"&TEXT(RC[-2],"000")&":"&TEXT(RC[-1],"0000")
Перше не пусте значення
=INDEX(RC[-5]:RC[-1],INDEX(MATCH(1=1,RC[-5]:RC[-1]<>"",),))
=INDEX(SP50:ST50,INDEX(MATCH(1=1,SP50:ST50<>"",),))
=ИНДЕКС(SP50:ST50;ИНДЕКС(ПОИСКПОЗ(1=1;SP50:ST50<>"";);))
Макрост транспонування всіх листів з рядків в стовбці в діапазоні A1:B200
Sub MacrosTransp()
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
sheet.Activate
Range("A1:B200").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Next
End Sub
У випадку зависання EXCEL при роботі з великими файлами, спробуйте видалити всі правила умовного форматування з листа