Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Формула для телефонного справочника - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Формула для телефонного справочника (Формулы/Formulas)
Формула для телефонного справочника
rico_crazy Дата: Среда, 10.11.2021, 14:47 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Всем привет. Возникла проблема, при созданию таблицы.
Была создана таблица в Excel, после чего была загружена на гугл диск, дабы дать доступ другим участникам к её редактированию.

В таблице есть два столбца:
1) Контакт
2) Номер мобильного телефона

Прикол в том что в таблице более 1000 контактов. Из-за того что данные вносили разные люди, каждый вносил номер телефона по своему.
Например:
Есть контакт "Цукровая фабрика". На этот контакт каждый кто имеет доступ к таблице внёс номер телефона, НО каждый вносил по своему:
1) 0983957399
2) +38 (067) 22-95-884
3) (0400) 675439

Все эти номера находятся в одной ячейке. Нужно что бы формула отсортировала эти номера по стандарту, то есть нужно что бы номера выглядели:
1) +38 (098) 39 57 399
2) +38 (067) 22 95 884
3) +38 (040) 06 75 439

Я пошёл след. путём:
1) Наша ячейка
0983957399
+38 (067) 22-95-884
(0400) 675439

2) Удаляем из ячейки все символы "(", ")", "-", "+", "’", ".", ",", """, ";", также удаляем все пробелы и переносы строк, и получаем:
Ячейка: 09839573993806722958840400675439

Формула:
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(КООРДИНАТЫ_ЯЧЕЙКИ; СИМВОЛ(10);"");")";"");"(";"");"+";"");"-";"");" ";"")


3) Каждый номер телефона состоит минимум из 9 цифр, две из которых обозначают оператора, и есть стандартными для многих номеров.
К примеру 0983957399, где 98 - цифры оператора. Все остальные после 98 набор цифр из номера телефона, может быть разным.
Операторов есть 48 шт.
Идея следующая: Мы берем ячейку и ищем в ней первый оператор(Которых 48шт), когда находим оператор отсчитываем от него 7 цифр, вместе с оператором 9 цифр, и выводим этот номер отдельно в другую ячейку, ну или туда где можно хранить этот номер к след. этапу. И повторяем эту процедуру 5 раз(В ячейке может быть не более 5-ти номеров). Если в ячейке осталось менее 7 цифр, это значит что недостаточно цифр для создания тел. номера, поэтому эти цифры удаляем.

4) После того как мы нашли номера, вывели их в отдельные ячейки, или хз куда, мы должны эти номера записать назад в первую ячейку, но уже в формате
1) +38 (098) 39 57 399
2) +38 (067) 22 95 884
3) +38 (040) 06 75 439

Как делал я:
Ячейка
98 36)-6(8)+811+-( 68 36)-6(8)+811( )98 34)-
5(8)+628+-( )
38067 34)-5(7)+677+-( )

Удаляю лишние символы
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(G4; СИМВОЛ(10);"");")";"");"(";"");"+";"");"-";"");" ";"")

Получаем
983668811683668811983458628380673457677

Находим номер с оператором 67
Код
=ПСТР(I4;НАЙТИ("67";I4);9)

Получаем
673457677

повторяю этот процес, но проблема в том что в формуле я вписываю вручную номер оператора, а их 48, что сделает формулу очень громадной, нужно сделать как то что бы формула проверяла ячейку на наличие определенных операторов

Проверка достаточно ли символов для номера
Код
=ЕСЛИ(ДЛСТР(I11)=7;I11;"пусто")


Сводим все найденные номера в нужный вид
Код
=СЦЕПИТЬ("+380"; I6; СИМВОЛ(10); "+380"; I8; СИМВОЛ(10); "+380"; I10)


Получили
+380673457677
+380688116836
+380983668811

КАК СДЕЛАТЬ ЭТО ВСЕ В ОДНУ ФОРМУЛУ. И ЕСТЬ ЛИ ВОЗМОЖНОСТЬ ИНТЕГРИРОВАТЬ КАК ТО ФОРМУЛУ ЧТО БЫ ЗАМЕНЯЛСЯ РЕЗУЛЬТАТ ПРЯМО В ПЕРВОЙ ЯЧЕЙКЕ КУДА БУДУТ ВНОСИТЬ НОМЕРА ТЕЛЕФОНОВ
 
Ответить
СообщениеВсем привет. Возникла проблема, при созданию таблицы.
Была создана таблица в Excel, после чего была загружена на гугл диск, дабы дать доступ другим участникам к её редактированию.

В таблице есть два столбца:
1) Контакт
2) Номер мобильного телефона

Прикол в том что в таблице более 1000 контактов. Из-за того что данные вносили разные люди, каждый вносил номер телефона по своему.
Например:
Есть контакт "Цукровая фабрика". На этот контакт каждый кто имеет доступ к таблице внёс номер телефона, НО каждый вносил по своему:
1) 0983957399
2) +38 (067) 22-95-884
3) (0400) 675439

Все эти номера находятся в одной ячейке. Нужно что бы формула отсортировала эти номера по стандарту, то есть нужно что бы номера выглядели:
1) +38 (098) 39 57 399
2) +38 (067) 22 95 884
3) +38 (040) 06 75 439

Я пошёл след. путём:
1) Наша ячейка
0983957399
+38 (067) 22-95-884
(0400) 675439

2) Удаляем из ячейки все символы "(", ")", "-", "+", "’", ".", ",", """, ";", также удаляем все пробелы и переносы строк, и получаем:
Ячейка: 09839573993806722958840400675439

Формула:
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(КООРДИНАТЫ_ЯЧЕЙКИ; СИМВОЛ(10);"");")";"");"(";"");"+";"");"-";"");" ";"")


3) Каждый номер телефона состоит минимум из 9 цифр, две из которых обозначают оператора, и есть стандартными для многих номеров.
К примеру 0983957399, где 98 - цифры оператора. Все остальные после 98 набор цифр из номера телефона, может быть разным.
Операторов есть 48 шт.
Идея следующая: Мы берем ячейку и ищем в ней первый оператор(Которых 48шт), когда находим оператор отсчитываем от него 7 цифр, вместе с оператором 9 цифр, и выводим этот номер отдельно в другую ячейку, ну или туда где можно хранить этот номер к след. этапу. И повторяем эту процедуру 5 раз(В ячейке может быть не более 5-ти номеров). Если в ячейке осталось менее 7 цифр, это значит что недостаточно цифр для создания тел. номера, поэтому эти цифры удаляем.

4) После того как мы нашли номера, вывели их в отдельные ячейки, или хз куда, мы должны эти номера записать назад в первую ячейку, но уже в формате
1) +38 (098) 39 57 399
2) +38 (067) 22 95 884
3) +38 (040) 06 75 439

Как делал я:
Ячейка
98 36)-6(8)+811+-( 68 36)-6(8)+811( )98 34)-
5(8)+628+-( )
38067 34)-5(7)+677+-( )

Удаляю лишние символы
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(G4; СИМВОЛ(10);"");")";"");"(";"");"+";"");"-";"");" ";"")

Получаем
983668811683668811983458628380673457677

Находим номер с оператором 67
Код
=ПСТР(I4;НАЙТИ("67";I4);9)

Получаем
673457677

повторяю этот процес, но проблема в том что в формуле я вписываю вручную номер оператора, а их 48, что сделает формулу очень громадной, нужно сделать как то что бы формула проверяла ячейку на наличие определенных операторов

Проверка достаточно ли символов для номера
Код
=ЕСЛИ(ДЛСТР(I11)=7;I11;"пусто")


Сводим все найденные номера в нужный вид
Код
=СЦЕПИТЬ("+380"; I6; СИМВОЛ(10); "+380"; I8; СИМВОЛ(10); "+380"; I10)


Получили
+380673457677
+380688116836
+380983668811

КАК СДЕЛАТЬ ЭТО ВСЕ В ОДНУ ФОРМУЛУ. И ЕСТЬ ЛИ ВОЗМОЖНОСТЬ ИНТЕГРИРОВАТЬ КАК ТО ФОРМУЛУ ЧТО БЫ ЗАМЕНЯЛСЯ РЕЗУЛЬТАТ ПРЯМО В ПЕРВОЙ ЯЧЕЙКЕ КУДА БУДУТ ВНОСИТЬ НОМЕРА ТЕЛЕФОНОВ

Автор - rico_crazy
Дата добавления - 10.11.2021 в 14:47
rico_crazy Дата: Среда, 10.11.2021, 16:25 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Для наглядности прилагаю скрин таблицы, проблема в том что выделено красным цветом. Во первых там только 3 оператора: 67, 68, 98.
На каждый формула:
Код
=ПСТР(I4;НАЙТИ("67";I4);9)

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



Нужно что бы из этого:


получилось это:


Сообщение отредактировал Serge_007 - Четверг, 11.11.2021, 09:46
 
Ответить
СообщениеДля наглядности прилагаю скрин таблицы, проблема в том что выделено красным цветом. Во первых там только 3 оператора: 67, 68, 98.
На каждый формула:
Код
=ПСТР(I4;НАЙТИ("67";I4);9)

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



Нужно что бы из этого:


получилось это:

Автор - rico_crazy
Дата добавления - 10.11.2021 в 16:25
Serge_007 Дата: Среда, 10.11.2021, 16:28 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
rico_crazy, используйте теги для оформления формул


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщениеrico_crazy, используйте теги для оформления формул

Автор - Serge_007
Дата добавления - 10.11.2021 в 16:28
bigorq Дата: Среда, 10.11.2021, 16:51 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 197
Репутация: 47 ±
Замечаний: 0% ±

Нет
rico_crazy, Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный?
ps таким же будет неудачно цифры подобрал :) Но есть вероятность, что Ваша формула найдет код оператора в номере телефона, и тогда она неверно вырежет следующие 7 цифр.


Сообщение отредактировал bigorq - Среда, 10.11.2021, 16:53
 
Ответить
Сообщениеrico_crazy, Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный?
ps таким же будет неудачно цифры подобрал :) Но есть вероятность, что Ваша формула найдет код оператора в номере телефона, и тогда она неверно вырежет следующие 7 цифр.

Автор - bigorq
Дата добавления - 10.11.2021 в 16:51
rico_crazy Дата: Среда, 10.11.2021, 17:08 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный?


Ну смотри в моей голове схема простая:
1) Есть куча телефонных номеров в ОДНОЙ ячейке, разделены пробелом, или же переносом строки, или же другими символами.
Номера записаны по разному, ибо каждый записывает так как ему удобно.
2) Главным элементом телефонного номера есть конечно же цифры. Поэтому с помощью формулы убираем все лишние знаки, пробелы, переносы.
Итог, у нас получается длинная строка цифр в таком же порядке, в котором записали участники таблицы.
3) Нам нужно найти из этой строки номера телефона, поэтому начинаем поиск в строке операторов(Которых есть 48шт), поиск начинаем с самого начала строки.
Когда первый номер найден, его нужно удалить из строки, и записать куда то отдельно, на потом. А в строке где мы уже удалили прежний номер, повторяем поиск, и делаем аналогично до тех пор, пока в строке не окажется меньше 7 цифр.
Я реализовал такое с помощью формул, но проблема в том что я сделал только 3 формулы где вручную внесено 3 оператора. А их 48шт, и каждый записывать это долго, и много места занимает. Нужно как то сделать что бы одна формула искала все 48 операторов.
4) Ну и в самом конце нужно что бы формула все найденые номера собирала обратно в одну ячейку в строго заданом формате.
В идеале было бы, если бы все это просчитывалось в одной и той же ячейке
 
Ответить
Сообщение
Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный?


Ну смотри в моей голове схема простая:
1) Есть куча телефонных номеров в ОДНОЙ ячейке, разделены пробелом, или же переносом строки, или же другими символами.
Номера записаны по разному, ибо каждый записывает так как ему удобно.
2) Главным элементом телефонного номера есть конечно же цифры. Поэтому с помощью формулы убираем все лишние знаки, пробелы, переносы.
Итог, у нас получается длинная строка цифр в таком же порядке, в котором записали участники таблицы.
3) Нам нужно найти из этой строки номера телефона, поэтому начинаем поиск в строке операторов(Которых есть 48шт), поиск начинаем с самого начала строки.
Когда первый номер найден, его нужно удалить из строки, и записать куда то отдельно, на потом. А в строке где мы уже удалили прежний номер, повторяем поиск, и делаем аналогично до тех пор, пока в строке не окажется меньше 7 цифр.
Я реализовал такое с помощью формул, но проблема в том что я сделал только 3 формулы где вручную внесено 3 оператора. А их 48шт, и каждый записывать это долго, и много места занимает. Нужно как то сделать что бы одна формула искала все 48 операторов.
4) Ну и в самом конце нужно что бы формула все найденые номера собирала обратно в одну ячейку в строго заданом формате.
В идеале было бы, если бы все это просчитывалось в одной и той же ячейке

Автор - rico_crazy
Дата добавления - 10.11.2021 в 17:08
rico_crazy Дата: Среда, 10.11.2021, 17:10 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

используйте теги для оформления формул

Извините, впервые на этом форуме, не до конца разобрался с тегами
 
Ответить
Сообщение
используйте теги для оформления формул

Извините, впервые на этом форуме, не до конца разобрался с тегами

Автор - rico_crazy
Дата добавления - 10.11.2021 в 17:10
bigorq Дата: Среда, 10.11.2021, 17:17 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 197
Репутация: 47 ±
Замечаний: 0% ±

Нет
про удалить, найденный номер в первом посте не нашел, в таком варианте идея наверное рабочая. Но нужен файл пример со списком операторов и вашей формулой.
Сделать формулой, что бы заменяла номера в исходной ячейке не получится. Нужно было настраивать проверку данных изначально


Сообщение отредактировал bigorq - Среда, 10.11.2021, 17:19
 
Ответить
Сообщениепро удалить, найденный номер в первом посте не нашел, в таком варианте идея наверное рабочая. Но нужен файл пример со списком операторов и вашей формулой.
Сделать формулой, что бы заменяла номера в исходной ячейке не получится. Нужно было настраивать проверку данных изначально

Автор - bigorq
Дата добавления - 10.11.2021 в 17:17
rico_crazy Дата: Среда, 10.11.2021, 17:38 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Но нужен файл пример со списком

Прикрепил файл моих формул
К сообщению приложен файл: 9395577.xlsx (12.2 Kb)
 
Ответить
Сообщение
Но нужен файл пример со списком

Прикрепил файл моих формул

Автор - rico_crazy
Дата добавления - 10.11.2021 в 17:38
jakim Дата: Среда, 10.11.2021, 18:28 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1197
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Вариант с PQ.

[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"дано", type text}}),
    #"Замененное значение" = Table.ReplaceValue(#"Измененный тип","(","",Replacer.ReplaceText,{"дано"}),
    #"Замененное значение1" = Table.ReplaceValue(#"Замененное значение",")","",Replacer.ReplaceText,{"дано"}),
    #"Замененное значение2" = Table.ReplaceValue(#"Замененное значение1"," ","",Replacer.ReplaceText,{"дано"}),
    #"Замененное значение3" = Table.ReplaceValue(#"Замененное значение2","-","",Replacer.ReplaceText,{"дано"}),
    #"Разделить столбец по положению" = Table.SplitColumn(#"Замененное значение3", "дано", Splitter.SplitTextByPositions({0, 7}, true), {"дано.1", "дано.2"}),
    #"Разделить столбец по положению1" = Table.SplitColumn(#"Разделить столбец по положению", "дано.2", Splitter.SplitTextByPositions({0, 3}, true), {"дано.2.1", "дано.2.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по положению1",{{"дано.1", Int64.Type}, {"дано.2.1", type text}, {"дано.2.2", Int64.Type}}),
    #"Разделить столбец по положению2" = Table.SplitColumn(#"Измененный тип1", "дано.2.1", Splitter.SplitTextByPositions({0, 2}, true), {"дано.2.1.1", "дано.2.1.2"}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по положению2",{{"дано.2.1.1", type text}, {"дано.2.1.2", type text}, {"дано.2.2", type text}}),
    #"Разделить столбец по положению3" = Table.SplitColumn(Table.TransformColumnTypes(#"Измененный тип2", {{"дано.1", type text}}, "ru-RU"), "дано.1", Splitter.SplitTextByPositions({0, 3}, true), {"дано.1.1", "дано.1.2"}),
    #"Разделить столбец по положению4" = Table.SplitColumn(#"Разделить столбец по положению3", "дано.1.2", Splitter.SplitTextByPositions({0, 2}, true), {"дано.1.2.1", "дано.1.2.2"}),
    #"Измененный тип3" = Table.TransformColumnTypes(#"Разделить столбец по положению4",{{"дано.1.1", Int64.Type}, {"дано.1.2.1", Int64.Type}, {"дано.1.2.2", Int64.Type}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип3",{"дано.1.2.1"}),
    #"Измененный тип4" = Table.TransformColumnTypes(#"Удаленные столбцы",{{"дано.1.2.2", type text}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип4", "Пользовательская", each "0"&[дано.1.2.2]),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"дано.1.2.2"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы1",{"дано.1.1", "Пользовательская", "дано.2.1.1", "дано.2.1.2", "дано.2.2"}),
    #"Замененное значение4" = Table.ReplaceValue(#"Переупорядоченные столбцы",null,38,Replacer.ReplaceValue,{"дано.1.1"}),
    #"Измененный тип5" = Table.TransformColumnTypes(#"Замененное значение4",{{"дано.1.1", type text}}),
    #"Замененное значение5" = Table.ReplaceValue(#"Измененный тип5","38","+38",Replacer.ReplaceText,{"дано.1.1"}),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Замененное значение5", "Пользовательская.1", each [дано.1.1]&" "&"("&[Пользовательская]&")"&" "&[дано.2.1.1]&" "&[дано.2.1.2]&" "&[дано.2.2]),
    #"Удаленные столбцы2" = Table.RemoveColumns(#"Добавлен пользовательский объект1",{"дано.1.1", "Пользовательская", "дано.2.1.1", "дано.2.1.2", "дано.2.2"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы2",{{"Пользовательская.1", "результат"}})
in
    #"Переименованные столбцы"
[/vba]
К сообщению приложен файл: 4291388.xlsx (20.7 Kb)


Сообщение отредактировал jakim - Среда, 10.11.2021, 18:29
 
Ответить
Сообщение
Вариант с PQ.

[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"дано", type text}}),
    #"Замененное значение" = Table.ReplaceValue(#"Измененный тип","(","",Replacer.ReplaceText,{"дано"}),
    #"Замененное значение1" = Table.ReplaceValue(#"Замененное значение",")","",Replacer.ReplaceText,{"дано"}),
    #"Замененное значение2" = Table.ReplaceValue(#"Замененное значение1"," ","",Replacer.ReplaceText,{"дано"}),
    #"Замененное значение3" = Table.ReplaceValue(#"Замененное значение2","-","",Replacer.ReplaceText,{"дано"}),
    #"Разделить столбец по положению" = Table.SplitColumn(#"Замененное значение3", "дано", Splitter.SplitTextByPositions({0, 7}, true), {"дано.1", "дано.2"}),
    #"Разделить столбец по положению1" = Table.SplitColumn(#"Разделить столбец по положению", "дано.2", Splitter.SplitTextByPositions({0, 3}, true), {"дано.2.1", "дано.2.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по положению1",{{"дано.1", Int64.Type}, {"дано.2.1", type text}, {"дано.2.2", Int64.Type}}),
    #"Разделить столбец по положению2" = Table.SplitColumn(#"Измененный тип1", "дано.2.1", Splitter.SplitTextByPositions({0, 2}, true), {"дано.2.1.1", "дано.2.1.2"}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по положению2",{{"дано.2.1.1", type text}, {"дано.2.1.2", type text}, {"дано.2.2", type text}}),
    #"Разделить столбец по положению3" = Table.SplitColumn(Table.TransformColumnTypes(#"Измененный тип2", {{"дано.1", type text}}, "ru-RU"), "дано.1", Splitter.SplitTextByPositions({0, 3}, true), {"дано.1.1", "дано.1.2"}),
    #"Разделить столбец по положению4" = Table.SplitColumn(#"Разделить столбец по положению3", "дано.1.2", Splitter.SplitTextByPositions({0, 2}, true), {"дано.1.2.1", "дано.1.2.2"}),
    #"Измененный тип3" = Table.TransformColumnTypes(#"Разделить столбец по положению4",{{"дано.1.1", Int64.Type}, {"дано.1.2.1", Int64.Type}, {"дано.1.2.2", Int64.Type}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип3",{"дано.1.2.1"}),
    #"Измененный тип4" = Table.TransformColumnTypes(#"Удаленные столбцы",{{"дано.1.2.2", type text}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип4", "Пользовательская", each "0"&[дано.1.2.2]),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"дано.1.2.2"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы1",{"дано.1.1", "Пользовательская", "дано.2.1.1", "дано.2.1.2", "дано.2.2"}),
    #"Замененное значение4" = Table.ReplaceValue(#"Переупорядоченные столбцы",null,38,Replacer.ReplaceValue,{"дано.1.1"}),
    #"Измененный тип5" = Table.TransformColumnTypes(#"Замененное значение4",{{"дано.1.1", type text}}),
    #"Замененное значение5" = Table.ReplaceValue(#"Измененный тип5","38","+38",Replacer.ReplaceText,{"дано.1.1"}),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Замененное значение5", "Пользовательская.1", each [дано.1.1]&" "&"("&[Пользовательская]&")"&" "&[дано.2.1.1]&" "&[дано.2.1.2]&" "&[дано.2.2]),
    #"Удаленные столбцы2" = Table.RemoveColumns(#"Добавлен пользовательский объект1",{"дано.1.1", "Пользовательская", "дано.2.1.1", "дано.2.1.2", "дано.2.2"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы2",{{"Пользовательская.1", "результат"}})
in
    #"Переименованные столбцы"
[/vba]

Автор - jakim
Дата добавления - 10.11.2021 в 18:28
прохожий2019 Дата: Среда, 10.11.2021, 20:16 | Сообщение № 10
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
[vba]
Код
let
    lst = List.Buffer({"0".."9"}),
    frm = {{10," "},{8," "},{6,") "},{3," ("}},
    
    f=(x)=>[a=Text.Split(x,"#(lf)"),
            b = List.Transform(a,g),
            c = Text.Combine(b,"#(lf)")][c],
    g=(x)=>[a=Text.Select(x,lst),
            b=Text.PadStart(a,13,"0"),
            c=Text.ReplaceRange(b,0,3,"+38"),
            d=List.Accumulate(frm,c,(x,y)=>Text.Insert(x,y{0},y{1}))][d],
    
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    to = Table.TransformColumns(from,{"Номера",f})
in
    to
[/vba]
К сообщению приложен файл: 9831402.xlsx (21.4 Kb)
 
Ответить
Сообщение [vba]
Код
let
    lst = List.Buffer({"0".."9"}),
    frm = {{10," "},{8," "},{6,") "},{3," ("}},
    
    f=(x)=>[a=Text.Split(x,"#(lf)"),
            b = List.Transform(a,g),
            c = Text.Combine(b,"#(lf)")][c],
    g=(x)=>[a=Text.Select(x,lst),
            b=Text.PadStart(a,13,"0"),
            c=Text.ReplaceRange(b,0,3,"+38"),
            d=List.Accumulate(frm,c,(x,y)=>Text.Insert(x,y{0},y{1}))][d],
    
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    to = Table.TransformColumns(from,{"Номера",f})
in
    to
[/vba]

Автор - прохожий2019
Дата добавления - 10.11.2021 в 20:16
прохожий2019 Дата: Среда, 10.11.2021, 22:04 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
ну и в новой эксельке:
Код
=ОБЪЕДИНИТЬ(СИМВОЛ(10);;MAP(split(E8;СИМВОЛ(10));telephone))

где
split
Код
=LAMBDA(x;y;ФИЛЬТР.XML("< j>< i>"&ПОДСТАВИТЬ(x;y;"< /i>< i>")&"< /i>< /j>";"//i"))

telephone
Код
=LAMBDA(x;ТЕКСТ(ПРАВСИМВ(ТЕКСТ(numbers(x);ПОВТОР("0";12));10);"+38_-(000)_-00_-00_-000"))

numbers
Код
=LAMBDA(x;СЦЕП(ФИЛЬТР.XML("< j>< i>"&ОБЪЕДИНИТЬ("< /i>< i>";1;ПСТР(x;ПОСЛЕД(ДЛСТР(x));1))&"< /i>< /j>";"//i[number()=.]")))


[p.s.] да поправьте уже наконец поведение в тегах - НЕТ никакой ЛЯМБДА только LAMBDA
К сообщению приложен файл: lambdas.xlsm (12.2 Kb)


Сообщение отредактировал прохожий2019 - Среда, 10.11.2021, 22:08
 
Ответить
Сообщениену и в новой эксельке:
Код
=ОБЪЕДИНИТЬ(СИМВОЛ(10);;MAP(split(E8;СИМВОЛ(10));telephone))

где
split
Код
=LAMBDA(x;y;ФИЛЬТР.XML("< j>< i>"&ПОДСТАВИТЬ(x;y;"< /i>< i>")&"< /i>< /j>";"//i"))

telephone
Код
=LAMBDA(x;ТЕКСТ(ПРАВСИМВ(ТЕКСТ(numbers(x);ПОВТОР("0";12));10);"+38_-(000)_-00_-00_-000"))

numbers
Код
=LAMBDA(x;СЦЕП(ФИЛЬТР.XML("< j>< i>"&ОБЪЕДИНИТЬ("< /i>< i>";1;ПСТР(x;ПОСЛЕД(ДЛСТР(x));1))&"< /i>< /j>";"//i[number()=.]")))


[p.s.] да поправьте уже наконец поведение в тегах - НЕТ никакой ЛЯМБДА только LAMBDA

Автор - прохожий2019
Дата добавления - 10.11.2021 в 22:04
Serge_007 Дата: Четверг, 11.11.2021, 09:49 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщениеhttps://support.microsoft.com/ru-ru....54b8b67

Автор - Serge_007
Дата добавления - 11.11.2021 в 09:49
прохожий2019 Дата: Четверг, 11.11.2021, 10:00 | Сообщение № 13
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel

хотя это всего лишь реальность... ссылочка она поважнее будет
К сообщению приложен файл: 1653674.png (35.1 Kb)


Сообщение отредактировал прохожий2019 - Четверг, 11.11.2021, 10:02
 
Ответить
Сообщение
хотя это всего лишь реальность... ссылочка она поважнее будет

Автор - прохожий2019
Дата добавления - 11.11.2021 в 10:00
Serge_007 Дата: Четверг, 11.11.2021, 10:02 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Я знаю

Ссылка - на официальный сайт MS
Оттуда я и брал список переводов на русский названий функций


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЯ знаю

Ссылка - на официальный сайт MS
Оттуда я и брал список переводов на русский названий функций

Автор - Serge_007
Дата добавления - 11.11.2021 в 10:02
rico_crazy Дата: Четверг, 11.11.2021, 15:47 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Такую таблицу можно будет загрузить на гугл диск? Просто не совсем разобрался как это работает. Ибо с Excel работаю недавно.

Если были бы только формулы, тогда я бы возможно разобрался


Сообщение отредактировал rico_crazy - Четверг, 11.11.2021, 15:52
 
Ответить
СообщениеТакую таблицу можно будет загрузить на гугл диск? Просто не совсем разобрался как это работает. Ибо с Excel работаю недавно.

Если были бы только формулы, тогда я бы возможно разобрался

Автор - rico_crazy
Дата добавления - 11.11.2021 в 15:47
прохожий2019 Дата: Четверг, 11.11.2021, 17:14 | Сообщение № 16
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Такую таблицу можно будет загрузить на гугл диск?
на гугл диск загрузить можно что угодно, вопрос в том, что вы собрались с этим файлом делать - через Google Tables работать, разумеется, не будет.
 
Ответить
Сообщение
Такую таблицу можно будет загрузить на гугл диск?
на гугл диск загрузить можно что угодно, вопрос в том, что вы собрались с этим файлом делать - через Google Tables работать, разумеется, не будет.

Автор - прохожий2019
Дата добавления - 11.11.2021 в 17:14
прохожий2019 Дата: Четверг, 11.11.2021, 17:16 | Сообщение № 17
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Если были бы только формулы, тогда я бы возможно разобрался
только у гугла и Excel формулы отличаются - немножко от слова совсем. В итоге в какой программе это всё должно работать?

[offtop]для гугла -
Код
=Arrayformula(JOIN(CHAR(10);"+38"&RIGHT(TEXT(Regexreplace(SPLIT(E4;CHAR(10))&"";"\D+";"");REPT(0;12));10)))
[/offtop]


Сообщение отредактировал прохожий2019 - Пятница, 12.11.2021, 17:06
 
Ответить
Сообщение
Если были бы только формулы, тогда я бы возможно разобрался
только у гугла и Excel формулы отличаются - немножко от слова совсем. В итоге в какой программе это всё должно работать?

[offtop]для гугла -
Код
=Arrayformula(JOIN(CHAR(10);"+38"&RIGHT(TEXT(Regexreplace(SPLIT(E4;CHAR(10))&"";"\D+";"");REPT(0;12));10)))
[/offtop]

Автор - прохожий2019
Дата добавления - 11.11.2021 в 17:16
rico_crazy Дата: Четверг, 11.11.2021, 17:45 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Цитата прохожий2019, 11.11.2021 в 17:16, в сообщении № 17 ()
только у гугла и Excel формулы отличаются - немножко от слова совсем. В итоге в какой программе это всё должно работать?

Ну я все делаю в excel. После чего загружаю на гугл диск, и вроде все работало.

Нужно что бы работало все это в гугл документе
 
Ответить
Сообщение
Цитата прохожий2019, 11.11.2021 в 17:16, в сообщении № 17 ()
только у гугла и Excel формулы отличаются - немножко от слова совсем. В итоге в какой программе это всё должно работать?

Ну я все делаю в excel. После чего загружаю на гугл диск, и вроде все работало.

Нужно что бы работало все это в гугл документе

Автор - rico_crazy
Дата добавления - 11.11.2021 в 17:45
прохожий2019 Дата: Четверг, 11.11.2021, 18:03 | Сообщение № 19
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Нужно что бы работало все это в гугл документе

Цитата прохожий2019, 11.11.2021 в 17:16, в сообщении № 17 ()
Оффтоп:
для гугла -
 
Ответить
Сообщение
Нужно что бы работало все это в гугл документе

Цитата прохожий2019, 11.11.2021 в 17:16, в сообщении № 17 ()
Оффтоп:
для гугла -

Автор - прохожий2019
Дата добавления - 11.11.2021 в 18:03
rico_crazy Дата: Четверг, 11.11.2021, 18:14 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Цитата прохожий2019, 11.11.2021 в 17:14, в сообщении № 16 ()
на гугл диск загрузить можно что угодно, вопрос в том, что вы собрались с этим файлом делать - через Google Tables работать, разумеется, не будет.


Вот ссылка на таблицу. Нужно что бы номера были строго в одном формате. Что бы пропадали лишние символы, так же текст тоже выкинуть
https://docs.google.com/spreads....sd=true
 
Ответить
Сообщение
Цитата прохожий2019, 11.11.2021 в 17:14, в сообщении № 16 ()
на гугл диск загрузить можно что угодно, вопрос в том, что вы собрались с этим файлом делать - через Google Tables работать, разумеется, не будет.


Вот ссылка на таблицу. Нужно что бы номера были строго в одном формате. Что бы пропадали лишние символы, так же текст тоже выкинуть
https://docs.google.com/spreads....sd=true

Автор - rico_crazy
Дата добавления - 11.11.2021 в 18:14
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Формула для телефонного справочника (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!