Страницы: 1
RSS
Сравнить два столбца, добавить разный % к числам
 
Здравствуйте. В ходе работы возникли две проблемы:

1. Есть два столбца с разными номерами. В 90% случаев они повторяются. Нужны сравнить первый столбец со вторым и вывести в отдельный столбец номера которых нет в первом столбце. Аналогично со вторым, сравнить и вывести в отдельный столбец номера которых нет во втором столбце, но есть в первом.

К примеру:
1 столбец

wel-62413  
wel-63001  
wel-63007  
wel-62704  
wel-63104  
wel-63204  
wel-63109
wel-63709  
wel-63304  
wel-62709  
wel-64004  
wel-63504  
wel-63509  
wel-63902

2 столбец

wel-62413  
wel-63001  
wel-63007  
wel-62704
wel-64429  
wel-63104  
wel-63204  
wel-63109  
wel-63304  
wel-62709  
wel-64004  
wel-63504  
wel-63509  
wel-63902

В первом столбце есть номер wel-63709, которого нет во втором столбце, во втором столбце это номер wel-64429, которого нет в первом столбце.



2. Есть столбец с числами. Нужно к ним прибавить % и вывести это в отдельный столбец, к примеру:

0-200=+5%
200-500=+10%
500-2000=+5%

235  
235  
235  
235  
1370  
1340  
1210  
1170  
1170  
1240  
590  
590


Буду благодарен за любую помощь.
 
Добрый день.
Первая задача легко решается запросом, с методом поиска "Анти-соединение слева", а вторая - обычными условными функциями, а если условий будет много, то ВПР с интервальным поиском.
 
Напишите пожалуйста пошагово как сделать "Анти-соединение слева". Первый раз с таким сталкиваюсь. Вычитал, что это относится к Power Query, установил 2016 офис (у меня до этого 2010 был установлен). Перечитал кучу инструкций, но абсолютно ничего не понятно.  
 
https://www.youtube.com/watch?v=WKRL3NE926M
 
Создал два подключения к таблицам, при "слиянии" у меня нет выбора "тип соединения".

Слияние
 
Вариант SQL запросом:
problem_1
Код
Private Function problem_1(SheetName As String, tblOneName As String, tblTwoName As String) As Variant
Dim myConnect As String, mySQL As String, myRecord As Object, QT As QueryTable
Dim DataRange As String, strAddress As String, conn
Dim TargetRange As String, arr
For Each conn In ThisWorkbook.Connections
    conn.Delete
Next conn
With ThisWorkbook.Worksheets(SheetName)
    strAddress = .ListObjects(tblOneName).DataBodyRange.Address(0, 0)
    DataRange = "[" & .Name & "$" & strAddress & "]"
    TargetRange = "[" & .Name & "$" & _
                    .ListObjects(tblTwoName).DataBodyRange.Address(0, 0) & "]"
End With
        myConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & ActiveWorkbook.FullName & ";" & _
           "Extended Properties=""Excel 12.0 Macro;HDR=NO"""
    Set myRecord = CreateObject("ADODB.Recordset")
    mySQL = "SELECT [F1] FROM " & DataRange & " as t WHERE t.[F1] NOT IN (" & _
            " SELECT [F1] FROM " & TargetRange & ")"
    myRecord.Open mySQL, myConnect

    arr = myRecord.getrows()
    problem_1 = arr
    myRecord.Close
    Set myRecord = Nothing
End Function
Sub run_macro()
   Dim a, b
   a = problem_1("1 задача", "Table1", "Table2")
   b = problem_1("1 задача", "Table2", "Table1")
   ' выгрузка на лист
   Worksheets("1 задача").Range("E5").Resize(UBound(a, 2) + 1, 1) = Application.Transpose(a)
   Worksheets("1 задача").Range("G5").Resize(UBound(b, 2) + 1, 1) = Application.Transpose(b)
End Sub

problem_2
Код
Private Function problem_2(SheetName As String, nameRng As String) As Variant
Dim myConnect As String, mySQL As String, myRecord As Object, QT As QueryTable
Dim DataRange As String, strAddress As String, conn
Dim TargetRange As String, arr
For Each conn In ThisWorkbook.Connections
    conn.Delete
Next conn
With ThisWorkbook.Worksheets(SheetName)
    strAddress = .Range(nameRng).Address(0, 0)
    DataRange = "[" & .Name & "$" & strAddress & "]"
End With
        myConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & ActiveWorkbook.FullName & ";" & _
           "Extended Properties=""Excel 12.0 Macro;HDR=YES"""
    Set myRecord = CreateObject("ADODB.Recordset")
    mySQL = "SELECT IIF(Вход < 200, Вход * 1.05, " & _
            "IIF(Вход >= 200 AND Вход < 500, Вход * 1.1, " & _
            "IIF(Вход >= 500 AND Вход < 2000, Вход *1.1 , 0))) as Выход FROM " & _
            DataRange & ""
    myRecord.Open mySQL, myConnect

    arr = myRecord.getrows()
    problem_2 = arr
    myRecord.Close
    Set myRecord = Nothing
End Function
Sub run_macro_2()
Dim a
a = problem_2("2 задача", "A1:A13")
' выгрузка на лист
Worksheets("2 задача").Range("E1").Resize(UBound(a, 2) + 1, 1) = Application.Transpose(a)
End Sub
Изменено: artemkau88 - 27.11.2022 16:02:08
 
Antickas, здравствуйте
2 проблемы = 2 темы.
Для первого вопроса можно обойтись одним списком: Полное (расширенное) сравнение двух списков (столбцов)
Изменено: Jack Famous - 28.11.2022 10:06:04
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Я как человек который первый раз видит Power Query, SQL запросы, вообще без понятия, что делать с Вашими решениями. То то есть, что, куда, зачем? Что за чем делать чтобы прийти к виду как в прикрепленных файлах.  
 
Antickas, с вас — файл, с меня — решение  ;)
Изменено: Jack Famous - 28.11.2022 19:13:22
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Возможно будет подставлять каждый раз новые значения, чтобы автоматически показывались значения которые разные по столбцам, пересчитывались значения на втором листе?

На втором листе нужно добавить к числам:

в столбец В:
0-200=121%
200-250=96%
250-300=84%
300-400=83%
400-900=71%
900-до конца=60%

В столбец С:
0-200=185%
200-250=160%
250-300=145%
300-400=145%
400-900=135%
900-до конца=120%

И округлить значение в этих столбцах до 5. К примеру: 364.65 до 365, 469.2 до 470
 
Antickas, по файлу ничего непонятно + вы продолжаете решать 2 задачи в одной теме = помойная тема.
Я пас…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1
Наверх