{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["SQL Statement","Лист2"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"SQL Statement","index":0,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":true,"merged":[]}
["select dt.a_subs_number \"Номер абонента\"\n,cl.legal_status \"Тип клиента\"\n,dt.connection_type \"Тип звонка\"\n,d.name \"Направление\"\n,dt.direction_code \"Код направления\"\n,round(dt.call_duration/60,2) \"Точное количество минут\"\n,sf.chargeble_volume \"Протариф. количество минут\"\n,t.name \"Название ТП\"\n,case when sf.service_date between to_date('0115', 'mmrr') and add_months(to_date('0115', 'mmrr'),1)-1/86400 and sf.servkind_code in ('CNT_TLF_199_МГ','CT_TLF_199_МГ') then sf.summa\n      else null\n  end \"Сумма за МГ 012015\"\n,case when sf.service_date between to_date('0115', 'mmrr') and add_months(to_date('0115', 'mmrr'),1)-1/86400 and sf.servkind_code in ('CNT_TLF_199_МН','CT_TLF_199_МН') then sf.summa\n      else null\n  end \"Сумма за МН 012015\"\n,case when sf.service_date between to_date('0215', 'mmrr') and add_months(to_date('0215', 'mmrr'),1)-1/86400 and sf.servkind_code in ('CNT_TLF_199_МГ','CT_TLF_199_МГ') then sf.summa\n      else null\n  end \"Сумма за МГ 022015\"\n,case when sf.service_date between to_date('0215', 'mmrr') and add_months(to_date('0215', 'mmrr'),1)-1/86400 and sf.servkind_code in ('CNT_TLF_199_МН','CT_TLF_199_МН') then sf.summa\n      else null\n  end \"Сумма за МН 022015\"\n,case when sf.service_date between to_date('0315', 'mmrr') and add_months(to_date('0315', 'mmrr'),1)-1/86400 and sf.servkind_code in ('CNT_TLF_199_МГ','CT_TLF_199_МГ') then sf.summa\n      else null\n  end \"Сумма за МГ 032015\"\n,case when sf.service_date between to_date('0315', 'mmrr') and add_months(to_date('0315', 'mmrr'),1)-1/86400 and sf.servkind_code in ('CNT_TLF_199_МН','CT_TLF_199_МН') then sf.summa\n      else null\n  end \"Сумма за МН 032015\"\nfrom tr_t_service_facts sf, tr_t_cdr_data dt, ct_t_object o, ct_t_contract c, cl_t_client cl, rf_t_direction_codes dc, rf_t_directions d, tf_t_tariffplane_hist th, tf_t_tariff_planes t\nwhere sf.service_date between to_date('0115', 'mmrr') and add_months(to_date('0315', 'mmrr'),1)-1/86400\n  and sf.servclass_code = 'PHONE'\n  and sf.servkind_code in ('CNT_TLF_199_МГ','CNT_TLF_199_МН','CT_TLF_199_МГ','CT_TLF_199_МН')\n  and dt.id = sf.session_id\n  and dt.start_datetime between to_date('0115', 'mmrr') and add_months(to_date('0315', 'mmrr'),1)-1/86400\n  and o.id = sf.object_id\n  and c.id = sf.contract_id\n  and cl.id = c.client_id\n  and dt.dircode_id = dc.id\n  and dc.direction_id = d.id\n  and sf.trfplnhist_id = th.id\n  and th.tarifplane_code = t.code\n  --and c.id = 1697175"]

{"sheet":"Лист2","index":1,"n_rows":36,"n_cols":13,"rows_kept":36,"cut":false,"hidden":false,"merged":[[18,0,21,9],[0,0,0,1],[0,3,0,5],[0,7,0,9],[2,7,2,9],[8,0,8,5],[1,0,1,5]]}
["Таблица 1",null,null,"Таблица 2"]
["C использованием впр по первому слову"]
["ID","name","Nat.ID","ID_Ins","name","Nat.ID"]
[1,"Novartis Ag",{"$f":"VLOOKUP(\"*\"&LEFTB(B4,SEARCH(\" \",B4,1))&\"*\",$E$3:$F$18,2,0)","v":6.17},1.1000000000000001,"Novartis Pharma Ag",6.17,{"$f":"f(B4,E4:E8)","v":"Novartis Pharma Ag"}]
[2,"Medintek GMBH",{"$f":"VLOOKUP(\"*\"&LEFTB(B5,SEARCH(\" \",B5,1))&\"*\",$E$3:$F$18,2,0)","v":{"$error":"#N/A"}},2.1,"Royal Bournemouth & Christchurch",3.89,{"$f":"f(B5,E5:E9)","v":""},null,null,null,{"$f":"INDEX($F$4:$F$7,SUMPRODUCT(ROW($E$4:$E$7)*(MMULT(1/$E$4:$E$7,VALUE(LEFTB(B4,LEN(B4)-COLUMN(OFFSET($A$1,,,,LEN(B4)))+1)))=1))-ROW($E$4:$E$7)+1)","v":{"$error":"#VALUE!"}},{"$f":"INDEX(F$4:F$7,MIN(IF(MMULT(1/E$4:E$7,VALUE(LEFTB(B4,LEN(B4)-COLUMN(OFFSET(A$1,,,,LEN(B4)))+1)))=1,ROW($4:$7)))-ROW(E$4)+1)","v":{"$error":"#VALUE!"}},{"$f":"VLOOKUP(--LEFTB(B4,MAX(IF(ISNUMBER(MATCH(--LEFTB(B4,ROW($1:$18)),E$4:E$7,)),ROW($1:$18)))),E$4:F$7,2,)","v":{"$error":"#VALUE!"}}]
[3,"Läns Landsting",2.14,3.1,"Västerbottens Läns Landsting VLL",2.14,{"$f":"f(B6,E6:E18)","v":"Västerbottens Läns Landsting VLL"},null,null,null,{"$f":"INDEX($F$4:$F$7,SUMPRODUCT(ROW($E$4:$E$7)*(MMULT(1/$E$4:$E$7,VALUE(LEFTB(B5,LEN(B5)-COLUMN(OFFSET($A$1,,,,LEN(B5)))+1)))=1))-ROW($E$4:$E$7)+1)","v":{"$error":"#VALUE!"}},{"$f":"INDEX(F$4:F$7,MIN(IF(MMULT(1/E$4:E$7,VALUE(LEFTB(B5,LEN(B5)-COLUMN(OFFSET(A$1,,,,LEN(B5)))+1)))=1,ROW($4:$7)))-ROW(E$4)+1)","v":{"$error":"#VALUE!"}},{"$f":"VLOOKUP(--LEFTB(B5,MAX(IF(ISNUMBER(MATCH(--LEFTB(B5,ROW($1:$18)),E$4:E$7,)),ROW($1:$18)))),E$4:F$7,2,)","v":{"$error":"#VALUE!"}}]
[4,"Brighton & Sussex Year 3",7.15,4.0999999999999996,"Brighton & Sussex",7.15,{"$f":"f(B7,E7:E19)","v":"Brighton & Sussex"},null,null,null,{"$f":"INDEX($F$4:$F$7,SUMPRODUCT(ROW($E$4:$E$7)*(MMULT(1/$E$4:$E$7,VALUE(LEFTB(B6,LEN(B6)-COLUMN(OFFSET($A$1,,,,LEN(B6)))+1)))=1))-ROW($E$4:$E$7)+1)","v":{"$error":"#VALUE!"}},{"$f":"INDEX(F$4:F$7,MIN(IF(MMULT(1/E$4:E$7,VALUE(LEFTB(B6,LEN(B6)-COLUMN(OFFSET(A$1,,,,LEN(B6)))+1)))=1,ROW($4:$7)))-ROW(E$4)+1)","v":{"$error":"#VALUE!"}},{"$f":"VLOOKUP(--LEFTB(B6,MAX(IF(ISNUMBER(MATCH(--LEFTB(B6,ROW($1:$18)),E$4:E$7,)),ROW($1:$18)))),E$4:F$7,2,)","v":{"$error":"#VALUE!"}}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"INDEX($F$4:$F$7,SUMPRODUCT(ROW($E$4:$E$7)*(MMULT(1/$E$4:$E$7,VALUE(LEFTB(B7,LEN(B7)-COLUMN(OFFSET($A$1,,,,LEN(B7)))+1)))=1))-ROW($E$4:$E$7)+1)","v":{"$error":"#VALUE!"}},{"$f":"INDEX(F$4:F$7,MIN(IF(MMULT(1/E$4:E$7,VALUE(LEFTB(B7,LEN(B7)-COLUMN(OFFSET(A$1,,,,LEN(B7)))+1)))=1,ROW($4:$7)))-ROW(E$4)+1)","v":{"$error":"#VALUE!"}},{"$f":"VLOOKUP(--LEFTB(B7,MAX(IF(ISNUMBER(MATCH(--LEFTB(B7,ROW($1:$18)),E$4:E$7,)),ROW($1:$18)))),E$4:F$7,2,)","v":{"$error":"#VALUE!"}}]
["C использованием UDF"]
["ID","name","Nat.ID","ID_Ins","name","Nat.ID"]
[1,"Novartis Ag",{"$f":"VLOOKUP(f(B11,$E$11:$E$15),$E$11:$F$26,2,0)","v":6.17},1.1000000000000001,"Novartis Pharma Ag",6.17]
[2,"Medintek GMBH",{"$f":"VLOOKUP(f(B12,$E$11:$E$15),$E$11:$F$26,2,0)","v":{"$error":"#N/A"}},2.1,"Royal Bournemouth & Christchurch",3.89]
[3,"Läns Landsting",2.14,3.1,"Västerbottens Läns Landsting VLL",2.14]
[4,"Brighton & Sussex Year 3",7.15,4.0999999999999996,"Brighton & Sussex",7.15]
[]
[]
[]
[]
["При сравнении имени из таблицы 2 с именами из таблицы 1, если первая итерация не обнаруживает совпадений, то из имени таблицы 2 удаляется правый символ и проверка происходит ещё раз. Правые символы отбрасываются до тех пор, пока имя из таблицы 2 не будет частью имени в таблице 1. В этом случае, к таблице 1 подставляется номер Nat.ID"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,{"$f":"[1]!Поиск_максимально_схожих1(B18,$E$4:$E$18)","v":""}]