==================================================================================== === Подставить значения с другого листа/книги, используя общий паттерн для поиска соответствий (например, на обоих листах должны быть идентичные порядковые номера или набор ФИО): ==================================================================================== # =INDEX('79'!K:K; MATCH(B19; '79'!A:A; 0)) =INDEX - формула для подстановки значений из другой ячейки другого листа или другой книги; '79' - название листа (можно не писать вручную, а кликнуть один раз на искомый лист); !K:K - столбец на листе '79', из какого берутся данные (год рождения, например); MATCH - формула для проверки совпадений, исходя из которых будет подставляться значение ячейки K листа '79' в текущую ячейку. B19 - ячейка другого листа, в которой находится значение, которое будет использоваться для сравнения с содержимым '79'!A:A; (конкретное ФИО) '79'!A:A - колонка с набором ФИО, которая будет использоваться в процессе сравнения с B19; 0 - точное совпадение значение (может еще быть 1 - приблизительное совпадение). ==================================================================================== === Сравнить значения A1 и B1: ==================================================================================== # =A1=B1 ==================================================================================== === Убрать все лишние символы из ячейки: ==================================================================================== # =CLEAN(TRIM(B5)) ==================================================================================== === Добавить 0 ко значению ячейки: ==================================================================================== # ="0" & AR5 ==================================================================================== === Убирает лишние пробелы в ячейках с несколькими строками текста, разделенными символом переноса строки (CHAR(10)): ==================================================================================== # =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2;CHAR(160);" ");" " & CHAR(10);CHAR(10));CHAR(10) & " ";CHAR(10))) ==================================================================================== === Подставить значение из колонки G листа sheet1 книги 11.xlsx в строку, содержащую в ячейке J6 такое значение, которое совпадает с одним из значений колонки A листа sheet1 книги 11.xlsx. Если значения в колонке G нет, то подставляется значение из ячейки U6: ==================================================================================== # =IFERROR(INDEX([11.xlsx]РС!$G:$G; MATCH(J6; [11.xlsx]РС!$A:$A; 0));U6) ==================================================================================== === Подставить значение из колонки G листа sheet1 книги 11.xlsx в строку, содержащую в ячейке J6 такое значение, которое совпадает с одним из значений колонки A листа sheet1 книги 11.xlsx. Если значения в колонке G нет, то подставляется значение из ячейки U6. Если в U6 нет значения, то подставляется кастомная заглушка: ==================================================================================== # =IFERROR(INDEX([11.xlsx]sheet1!$G:$G; MATCH(J6; [11.xlsx]sheet1!$A:$A; 0));IF(U6="";"<--->";U6)) ==================================================================================== === Создает ссылку с кастомным текстом на ячейку в колонке J листа sheet1, которая совпадает со значением ячейки P7: ==================================================================================== # =HYPERLINK("#'sheet1'!J"&MATCH(P7;'sheet1'!J:J;0);"На другую страницу") ==================================================================================== === Вставить в ячейку две даты, разделенные символом переноса строки. Даты приводятся к конкретному формату, вне зависимости от типа данных в ячейках X7 и R7: ==================================================================================== # =TEXT(X7;"dd.mm.yyyy") & CHAR(10) & TEXT(R7;"dd.mm.yyyy") ==================================================================================== === Подставить значение из колонки G листа sheet1 книги 11.xlsx с использованием кастомного формата отображения. Например, если подставляться будет число 211, то в ячейке с формулой оно будет выглядеть как 000211: ==================================================================================== # =TEXT(INDEX('[11.xlsx]sheet1'!$G:$G; MATCH(J6; '[11.xlsx]sheet1'!$A:$A; 0));"000000") ==================================================================================== === Подставить даты из колонки AD листа sheet1 книги 11.xlsx. Для отображения полученной даты используется кастомный формат: ==================================================================================== # =TEXT(INDEX('[11.xlsm]sheet1'!$AD:$AD; MATCH(B2; '[11.xlsm]sheet1'!$J:$J; 0));"dd.mm.yyyy") ==================================================================================== === Сделать замену символа # на перенос строки и #: ==================================================================================== # =SUBSTITUTE(G2; "#"; CHAR(10) & "#") ==================================================================================== === Вставить в одну ячейку две даты, разделив их символом переноса строки: ==================================================================================== # =TEXT(B2;"dd.mm.yyyy") & CHAR(10) & TEXT(E2;"dd.mm.yyyy") ==================================================================================== === Аркуш first, в колонці A є список прізвищ. Аркуш first в колонці B є інший список. Деякі рядки в цій колонці заповнені, деякі - ні. Аркуш second в колонці A є список даних у наступному форматі: ЩОСЬ ТУТ ЩОСЬ ТАМ ЩЕ === Треба зробити так, щоб колонка B на аркуші first була заповнена повністю. Порожні клітинки у цій колонці мають заповнюватися даними з аркушу second колонки A. Підставляючи рядок за рядком у колонку B аркушу first, ексель має замінювати на прізвище, яке знаходиться в колонці A аркушу first відповідного рядка. ==================================================================================== # =IF( AC6<>""; AC6; SUBSTITUTE( INDEX(second!A:A; COUNTIF('sheet1'!AC$6:AC6; "") ); ""; D6 ) ) ==================================================================================== === Привести текст, записанный буквами разного регистра в A1, ко грамматически корректной записи - первая большая, остальные маленькие: ==================================================================================== # =PROPER(LOWER(A1)) ==================================================================================== === Забрать первое слева слово из ячейки и вставить её в текущую без добавления пробелов: ==================================================================================== # =TRIM(LEFT(C6; FIND(" "; C6 & " ") - 1)) ==================================================================================== === Об'єднати контент двох клітинок, у яких є два і більше рядків з інформацією, розділених символом переносу рядка. Наприклад: Клітинка A1: test1 test2 Клітинка B1: data1 data2 Клітинка C1 (як має бути): test1 data1 test2 data2 ==================================================================================== === Создаются три дополнительные колонки. Сначала забираются "верхние этажи" и собираются в одной клетке. Потом забираются нижние этажи и собираются в другой клетке. Потом объединяются записи из двух клеток в двухэтажную запись: # =LEFT(L6; FIND(CHAR(10); L6 & CHAR(10)) - 1) & " " & LEFT(M6; FIND(CHAR(10); M6 & CHAR(10)) - 1) =MID(L6; FIND(CHAR(10); L6) + 1; 255) & " " & MID(M6; FIND(CHAR(10); M6) + 1; 255) =N6 & CHAR(10) & O6 ==================================================================================== === Подставить в клетку записи из колонки A страницы sheet1, если ячейка U6 пустая. Если непустая - подставить значения из неё: ==================================================================================== # =IF(U6<>""; U6; INDEX(sheet1!$A:$A; COUNTIF($U$6:U6;"") )) ==================================================================================== === Если P6 заполнена - подставить в ячейку тримнутое значение из P6. Если она пустая - подставить из Z6: ==================================================================================== # =IF(TRIM(P6)<>""; P6; Z6) ==================================================================================== === Сделать автоматическую нумерацию строк, отталкиваясь от ячеек, заполненных в колонке D: ==================================================================================== # =IF(D4="";"";COUNTA($D$3:D4)) ==================================================================================== === Забрать данные из верхней строчки клетки и вставить в соседнюю колонку: ==================================================================================== # =IFERROR(LEFT(A1, FIND(CHAR(10), A1) - 1), A1) ==================================================================================== === Вырезать весь текст после третьего пробела и оставить всё, что было до него: ==================================================================================== # =LEFT(A1;FIND("¶";SUBSTITUTE(A1;" ";"¶";3))-1) ==================================================================================== === Получить количество полных лет (без округлений) между двумя датами: ==================================================================================== # =DATEDIF(A1;B1;"Y") ==================================================================================== === Получить количество полных лет (без округлений) между датой в клетке и сегодня: ==================================================================================== # =DATEDIF(A1;TODAY();"Y") ==================================================================================== === Забрать в соседнюю клетку только первые три слова (набор символов, разделенных тремя пробелами, включая пробел перед следующим, четвертым словом) из всего набора символов: ==================================================================================== # =LEFT(A1;FIND("¶";SUBSTITUTE(A1;" ";"¶";3))-1) ==================================================================================== === Поиск последовательности в колонке D. Если последовательность найдена, то вывести "Yes", иначе "No": ==================================================================================== # =IF(ISNUMBER(SEARCH("последовательность";D2));"Yes";"No") ==================================================================================== === С помощью INDEX+MATCH подставить числа в текстовом виде (чтобы не съедались нули в начале): ==================================================================================== # =TEXT(INDEX(A:A,MATCH(...)),"@") ==================================================================================== === Выделить строку и удалить её: ==================================================================================== # Shift+пробел -> Ctrl+- ==================================================================================== === Сравнивать две даты как текст: ==================================================================================== # =TEXT(A1,"dd.mm.yyyy") = TEXT(B1,"dd.mm.yyyy") ==================================================================================== === Проставить автоматическую нумерацию строк, отталкиваясь от заполненности колонки E: ==================================================================================== # =IF(E2="";"";COUNTA($E$2:E2)) ==================================================================================== === В клітинці (скажімо, A1) екселю є текст (без лапок): Київська обл., м. Київ, вул. Хрещатик, буд. 1. Якою формулою я можу забрати перше слово, вставити його у клітинку B1 з формулою, а в клітинці A1 залишити тільки те, що йде після другого пробілу? Тобто: B1 - Київська A1 - м. Київ, вул. Хрещатик, буд. 1 ==================================================================================== # =MID(M5; FIND(" "; M5; FIND(" "; M5)+1)+1; 999) ==================================================================================== === Разбить по колонкам данные, записанные в ячейке через пробел (всего три слова, два пробела): ==================================================================================== # =LEFT(U5; FIND(" "; U5)-1) =MID(U5; FIND(" "; U5)+1; FIND(" "; U5; FIND(" "; U5)+1) - FIND(" "; U5) - 1) =MID(U5; FIND(" "; U5; FIND(" "; U5)+1)+1; 999)