{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Начислено","Уплачено","Лист1","Лист5"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":2,"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":"Начислено","index":0,"n_rows":15,"n_cols":11,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["сц","id_1","id_2","Начислено"]
[{"$f":"B2&C2","v":"1a"},1,"a",10]
[{"$f":"B3&C3","v":"1b"},1,"b",25]
["1c",1,"c",12]
["2a",2,"a",40]
["2c",2,"c",30]
["2d",2,"d",35,null,null,null,null,null,null,"Есть два листа \"Начислено\" и \"Уплачено\""]
["4a",4,"a",60,null,null,null,null,null,null,"В лист \"Начислено\" в столбец D подтянуты соответствующие суммы с листа \"Уплачено\" (связь по id_1 и id_2)"]
[null,null,null,null,null,null,null,null,null,null,"В лист \"Уплачено\" в столбец D подтянуты соответствующие суммы с листа \"Начислено\" (связь по id_1 и id_2)"]
[null,null,null,null,null,null,null,null,null,null,"В столбце Е положительные суммы соответствуют недоплате."]
[null,null,null,null,null,null,null,null,null,null,"Если начислений не было, а уплата была, то в на первом листе этого не увидать."]
[null,null,null,null,null,null,null,null,null,null,"Аналогично, если уплаты не было, а начисления были, то на втором листе соответствующий строки не будет."]
[null,null,null,null,null,null,null,null,null,null,"Можно с помощью формул все собрать на одном листе, чтобы не анализировать два листа?"]
[null,null,null,null,null,null,null,null,null,null,"Поскольку id_* в реальной таблице много, для упрощения формул можно использовать дополнительные столбцы (например id_1 и id_2 сложить, используя разделитель и тогда функция ИНДЕКС упроститься )"]
[null,null,null,null,null,null,null,null,null,null,"Макросом не хотелось бы, так как структура таблиц в разных книгах разная и формулы легче будет править, чем макросы."]

{"sheet":"Уплачено","index":1,"n_rows":7,"n_cols":4,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["сц","id_1","id_2","Уплачено"]
[{"$f":"B2&C2","v":"1c"},1,"c",12]
[{"$f":"B3&C3","v":"1a"},1,"a",15]
["1d",1,"d",25]
["2a",2,"a",33]
["2d",2,"d",35]
["3a",3,"a",50]

{"sheet":"Лист1","index":2,"n_rows":10,"n_cols":5,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[null,"id_1","id_2","Начислено","Уплачено"]
["1a",2,null,10,15]
["1b",1,null,25]
["1c",2,null,12,12]
["1d",1,null,null,25]
["2a",4,null,40,33]
["2c",2,null,30]
["2d",4,null,35,35]
["4a",4,null,60]
["3a",3,null,null,50]

{"sheet":"Лист5","index":3,"n_rows":23,"n_cols":6,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[null,null,"id_1","id_2","Начислено","Уплачено"]
[null,"Qwerty656_2 (1)",{"$f":"Начислено!$B$2","v":1},null,{"$f":"Начислено!$D$2","v":10}]
[null,"Qwerty656_2 (1)",{"$f":"Уплачено!$B$3","v":1},null,null,{"$f":"Уплачено!$D$3","v":15}]
["1a",null,{"$f":"SUM(C2:C3)","v":2},null,{"$f":"SUM(E2:E3)","v":10},{"$f":"SUM(F2:F3)","v":15}]
[null,"Qwerty656_2 (1)",{"$f":"Начислено!$B$3","v":1},null,{"$f":"Начислено!$D$3","v":25}]
["1b",null,{"$f":"SUM(C5)","v":1},null,{"$f":"SUM(E5)","v":25}]
[null,"Qwerty656_2 (1)",{"$f":"Начислено!$B$4","v":1},null,{"$f":"Начислено!$D$4","v":12}]
[null,"Qwerty656_2 (1)",{"$f":"Уплачено!$B$2","v":1},null,null,{"$f":"Уплачено!$D$2","v":12}]
["1c",null,{"$f":"SUM(C7:C8)","v":2},null,{"$f":"SUM(E7:E8)","v":12},{"$f":"SUM(F7:F8)","v":12}]
[null,"Qwerty656_2 (1)",{"$f":"Уплачено!$B$4","v":1},null,null,{"$f":"Уплачено!$D$4","v":25}]
["1d",null,{"$f":"SUM(C10)","v":1},null,null,{"$f":"SUM(F10)","v":25}]
[null,"Qwerty656_2 (1)",{"$f":"Начислено!$B$5","v":2},null,{"$f":"Начислено!$D$5","v":40}]
[null,"Qwerty656_2 (1)",{"$f":"Уплачено!$B$5","v":2},null,null,{"$f":"Уплачено!$D$5","v":33}]
["2a",null,{"$f":"SUM(C12:C13)","v":4},null,{"$f":"SUM(E12:E13)","v":40},{"$f":"SUM(F12:F13)","v":33}]
[null,"Qwerty656_2 (1)",{"$f":"Начислено!$B$6","v":2},null,{"$f":"Начислено!$D$6","v":30}]
["2c",null,{"$f":"SUM(C15)","v":2},null,{"$f":"SUM(E15)","v":30}]
[null,"Qwerty656_2 (1)",{"$f":"Начислено!$B$7","v":2},null,{"$f":"Начислено!$D$7","v":35}]
[null,"Qwerty656_2 (1)",{"$f":"Уплачено!$B$6","v":2},null,null,{"$f":"Уплачено!$D$6","v":35}]
["2d",null,{"$f":"SUM(C17:C18)","v":4},null,{"$f":"SUM(E17:E18)","v":35},{"$f":"SUM(F17:F18)","v":35}]
[null,"Qwerty656_2 (1)",{"$f":"Начислено!$B$8","v":4},null,{"$f":"Начислено!$D$8","v":60}]
["4a",null,{"$f":"SUM(C20)","v":4},null,{"$f":"SUM(E20)","v":60}]
[null,"Qwerty656_2 (1)",{"$f":"Уплачено!$B$7","v":3},null,null,{"$f":"Уплачено!$D$7","v":50}]
["3a",null,{"$f":"SUM(C22)","v":3},null,null,{"$f":"SUM(F22)","v":50}]