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

Вход

Регистрация

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

 

= Мир MS Excel/Сопоставление похожих значений - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сопоставление похожих значений (Макросы/Sub)
Сопоставление похожих значений
Oh_Nick Дата: Четверг, 24.08.2023, 23:24 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 445
Репутация: 8 ±
Замечаний: 20% ±

Excel 2019
Всем доброго времени суток!

Файл1 Лист АА

Колонка А - Код
Колонка B - Дата
Колонка C - Начало времени
Колонка D - Конец времени

Файл2 Лист BB

Колонка E - Код
Колонка F - Дата
Колонка G - Начало времени
Колонка H - Конец времени

Подскажите с макросом (может и есть формула) которая если все 4 значения будут сходиться, то запишет их в строку Код-Дата-Начало времени-Конец времени на новом листе


Сообщение отредактировал Oh_Nick - Четверг, 24.08.2023, 23:25
 
Ответить
СообщениеВсем доброго времени суток!

Файл1 Лист АА

Колонка А - Код
Колонка B - Дата
Колонка C - Начало времени
Колонка D - Конец времени

Файл2 Лист BB

Колонка E - Код
Колонка F - Дата
Колонка G - Начало времени
Колонка H - Конец времени

Подскажите с макросом (может и есть формула) которая если все 4 значения будут сходиться, то запишет их в строку Код-Дата-Начало времени-Конец времени на новом листе

Автор - Oh_Nick
Дата добавления - 24.08.2023 в 23:24
Hugo Дата: Пятница, 25.08.2023, 00:08 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
Доброго.
Можно формулой в любом файле проставить единички в те строки, которые совпадают.
И далее фильтр и копипаст.
Формула (массива!) типа
Код
=($A$2:$A$10=E2)*($B$2:$B$10=F2)*($C$2:$C$10=G2)*($D$2:$D$10=H2)

Лень делать два файла, ну не мне одному лень...


excel@nxt.ru
webmoney: E265281470651 Z422237915069


Сообщение отредактировал Hugo - Пятница, 25.08.2023, 00:08
 
Ответить
СообщениеДоброго.
Можно формулой в любом файле проставить единички в те строки, которые совпадают.
И далее фильтр и копипаст.
Формула (массива!) типа
Код
=($A$2:$A$10=E2)*($B$2:$B$10=F2)*($C$2:$C$10=G2)*($D$2:$D$10=H2)

Лень делать два файла, ну не мне одному лень...

Автор - Hugo
Дата добавления - 25.08.2023 в 00:08
Oh_Nick Дата: Пятница, 25.08.2023, 08:21 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 445
Репутация: 8 ±
Замечаний: 20% ±

Excel 2019
Hugo,

Можете показать на примере? Немного не понял логику

Файл Факт Лист by outlets

Колонка W - Код
Колонка N - Дата
Колонка R - Начало времени
Колонка S - Конец времени

Файл Прогноз Лист AP

Колонка E - Код
Колонка K - Дата
Колонка M - Начало времени
Колонка N - Конец времени

Мы должны вносить в таблицу только ту строчка, которая полностью совпадает с двумя файлами , т.е и Код1=Код2=Дата1=Дата2=Начало Времени1=Начало Времени2=Конец времени 1=Конец времени 2. Если что-то не сходится то строчка пустая. Попробовал ввести вашу формулу, мне выдало 0

Ссылки на гугл диск: https://docs.google.com/spreads....d=true, https://docs.google.com/spreads....sd=true
К сообщению приложен файл: itogo.xlsx (16.2 Kb)


Сообщение отредактировал Oh_Nick - Пятница, 25.08.2023, 08:29
 
Ответить
СообщениеHugo,

Можете показать на примере? Немного не понял логику

Файл Факт Лист by outlets

Колонка W - Код
Колонка N - Дата
Колонка R - Начало времени
Колонка S - Конец времени

Файл Прогноз Лист AP

Колонка E - Код
Колонка K - Дата
Колонка M - Начало времени
Колонка N - Конец времени

Мы должны вносить в таблицу только ту строчка, которая полностью совпадает с двумя файлами , т.е и Код1=Код2=Дата1=Дата2=Начало Времени1=Начало Времени2=Конец времени 1=Конец времени 2. Если что-то не сходится то строчка пустая. Попробовал ввести вашу формулу, мне выдало 0

Ссылки на гугл диск: https://docs.google.com/spreads....d=true, https://docs.google.com/spreads....sd=true

Автор - Oh_Nick
Дата добавления - 25.08.2023 в 08:21
Oh_Nick Дата: Суббота, 26.08.2023, 11:25 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 445
Репутация: 8 ±
Замечаний: 20% ±

Excel 2019
так понимаю идей нет :(
 
Ответить
Сообщениетак понимаю идей нет :(

Автор - Oh_Nick
Дата добавления - 26.08.2023 в 11:25
i691198 Дата: Суббота, 26.08.2023, 12:38 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 200
Репутация: 83 ±
Замечаний: 0% ±

идей нет

Пример вы не приложили, а если абстрактно, то например так. Макросом скопировать данные в два двухмерных массива, циклом или несколькими циклами сравнить их, по результатам сравнения сформировать третий массив и сбросить его на лист. Правда есть вопросы:
- есть ли дубликаты в столбце Код?
- упорядочены ли данные в этом же столбце?
Приложите небольшой пример, и не обязательно с несколькими файлами.


Сообщение отредактировал i691198 - Суббота, 26.08.2023, 12:38
 
Ответить
Сообщение
идей нет

Пример вы не приложили, а если абстрактно, то например так. Макросом скопировать данные в два двухмерных массива, циклом или несколькими циклами сравнить их, по результатам сравнения сформировать третий массив и сбросить его на лист. Правда есть вопросы:
- есть ли дубликаты в столбце Код?
- упорядочены ли данные в этом же столбце?
Приложите небольшой пример, и не обязательно с несколькими файлами.

Автор - i691198
Дата добавления - 26.08.2023 в 12:38
Hugo Дата: Суббота, 26.08.2023, 17:02 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
Идея выше в посте.
Гуглдиск закрыт.
Формула в файле не такая как моя идея ))
Если делать макросом то просто куча всяких вариантов, чтоб выбрать оптимальный нужно знать задачу целиком.


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеИдея выше в посте.
Гуглдиск закрыт.
Формула в файле не такая как моя идея ))
Если делать макросом то просто куча всяких вариантов, чтоб выбрать оптимальный нужно знать задачу целиком.

Автор - Hugo
Дата добавления - 26.08.2023 в 17:02
MikeVol Дата: Суббота, 26.08.2023, 20:55 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 291
Репутация: 51 ±
Замечаний: 0% ±

Excel LTSC 2021 EN
Oh_Nick, Доброго времени суток. Дело было вечером, делать было нечего. Может вам и не так вам надо, но как вариант. Предположим что у вас есть три книги: Oh_Nick_Book1.xlsx, Oh_Nick_Book2.xlsx и третья книга куда вы хотите копировать совпавшие данные, а именно
Файл1 Лист АА

Колонка А - Код
Колонка B - Дата
Колонка C - Начало времени
Колонка D - Конец времени

Файл2 Лист BB

Колонка E - Код
Колонка F - Дата
Колонка G - Начало времени
Колонка H - Конец времени

То вот вам код:
[vba]
Код
Option Explicit

' все три файла которые указаны в коде должны быть открытыми
' совпашие строки между "Oh_Nick_Book1.xlsx" и "Oh_Nick_Book2.xlsx" заносяться в книгу "Oh_Nick_Main.xlsm" из книги "Oh_Nick_Book1.xlsx"
Sub Oh_Nick_Main()
    Dim i           As Long
    Application.ScreenUpdating = False
    Dim sh          As Worksheet: Set sh = Workbooks("Oh_Nick_Book1.xlsx").Worksheets(1)
    Application.ScreenUpdating = False

    With Workbooks("Oh_Nick_Book2.xlsx").Worksheets(1)
        Dim iRow    As Long: iRow = .Cells(.Rows.Count, "E").End(xlUp).Row

        For i = 2 To iRow

            If .Cells(i, "E") <> "" Then

                If sh.Cells(i, "A").Value = .Cells(i, "E").Value _
                        And sh.Cells(i, "B").Value = .Cells(i, "F").Value _
                        And sh.Cells(i, "C").Value = .Cells(i, "G").Value _
                        And sh.Cells(i, "D").Value = .Cells(i, "H").Value Then
                    Dim ws As Worksheet: Set ws = Workbooks("Oh_Nick_Main.xlsm").Worksheets(1)
                    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

                    ws.Cells(lastRow, 1).Value = .Cells(i, 5).Value
                    ws.Cells(lastRow, 2).Value = .Cells(i, 6).Value
                    ws.Cells(lastRow, 3).Value = .Cells(i, 7).Value
                    ws.Cells(lastRow, 4).Value = .Cells(i, 8).Value

                    ws.Cells(lastRow, 3).NumberFormat = "h:mm"
                    ws.Cells(lastRow, 4).NumberFormat = "h:mm"
                End If

            End If

            iRow = iRow + 1
        Next

    End With

    Application.ScreenUpdating = True
End Sub
[/vba]
Не судите строго, как понял я так и сделал. Повторюсь, дело было вечером, делать было нечего. Данный код вставить в третью книгу Oh_Nick_Main.xlsm, от туда и запускаете данный макрос. Удачи.


Ученик.

Сообщение отредактировал MikeVol - Суббота, 26.08.2023, 20:58
 
Ответить
СообщениеOh_Nick, Доброго времени суток. Дело было вечером, делать было нечего. Может вам и не так вам надо, но как вариант. Предположим что у вас есть три книги: Oh_Nick_Book1.xlsx, Oh_Nick_Book2.xlsx и третья книга куда вы хотите копировать совпавшие данные, а именно
Файл1 Лист АА

Колонка А - Код
Колонка B - Дата
Колонка C - Начало времени
Колонка D - Конец времени

Файл2 Лист BB

Колонка E - Код
Колонка F - Дата
Колонка G - Начало времени
Колонка H - Конец времени

То вот вам код:
[vba]
Код
Option Explicit

' все три файла которые указаны в коде должны быть открытыми
' совпашие строки между "Oh_Nick_Book1.xlsx" и "Oh_Nick_Book2.xlsx" заносяться в книгу "Oh_Nick_Main.xlsm" из книги "Oh_Nick_Book1.xlsx"
Sub Oh_Nick_Main()
    Dim i           As Long
    Application.ScreenUpdating = False
    Dim sh          As Worksheet: Set sh = Workbooks("Oh_Nick_Book1.xlsx").Worksheets(1)
    Application.ScreenUpdating = False

    With Workbooks("Oh_Nick_Book2.xlsx").Worksheets(1)
        Dim iRow    As Long: iRow = .Cells(.Rows.Count, "E").End(xlUp).Row

        For i = 2 To iRow

            If .Cells(i, "E") <> "" Then

                If sh.Cells(i, "A").Value = .Cells(i, "E").Value _
                        And sh.Cells(i, "B").Value = .Cells(i, "F").Value _
                        And sh.Cells(i, "C").Value = .Cells(i, "G").Value _
                        And sh.Cells(i, "D").Value = .Cells(i, "H").Value Then
                    Dim ws As Worksheet: Set ws = Workbooks("Oh_Nick_Main.xlsm").Worksheets(1)
                    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

                    ws.Cells(lastRow, 1).Value = .Cells(i, 5).Value
                    ws.Cells(lastRow, 2).Value = .Cells(i, 6).Value
                    ws.Cells(lastRow, 3).Value = .Cells(i, 7).Value
                    ws.Cells(lastRow, 4).Value = .Cells(i, 8).Value

                    ws.Cells(lastRow, 3).NumberFormat = "h:mm"
                    ws.Cells(lastRow, 4).NumberFormat = "h:mm"
                End If

            End If

            iRow = iRow + 1
        Next

    End With

    Application.ScreenUpdating = True
End Sub
[/vba]
Не судите строго, как понял я так и сделал. Повторюсь, дело было вечером, делать было нечего. Данный код вставить в третью книгу Oh_Nick_Main.xlsm, от туда и запускаете данный макрос. Удачи.

Автор - MikeVol
Дата добавления - 26.08.2023 в 20:55
Hugo Дата: Суббота, 26.08.2023, 21:17 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
Быстрее будет если заменить And на вложенные If.
Ибо зачем проверять далее, если уже sh.Cells(i, "A").Value <> .Cells(i, "E").Value
К тому же же очень вряд ли строки в разных файлах будут/должны совпадать... Но эти детали в файлах, которых не видим.


excel@nxt.ru
webmoney: E265281470651 Z422237915069


Сообщение отредактировал Hugo - Суббота, 26.08.2023, 21:37
 
Ответить
СообщениеБыстрее будет если заменить And на вложенные If.
Ибо зачем проверять далее, если уже sh.Cells(i, "A").Value <> .Cells(i, "E").Value
К тому же же очень вряд ли строки в разных файлах будут/должны совпадать... Но эти детали в файлах, которых не видим.

Автор - Hugo
Дата добавления - 26.08.2023 в 21:17
MikeVol Дата: Суббота, 26.08.2023, 22:02 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 291
Репутация: 51 ±
Замечаний: 0% ±

Excel LTSC 2021 EN
Hugo, Приветствую вас! Сделал как понял по описанию
вносить в таблицу только ту строчка, которая полностью совпадает с двумя файлами , т.е и Код1=Код2=Дата1=Дата2=Начало Времени1=Начало Времени2=Конец времени 1=Конец времени 2

а вот тут
Если что-то не сходится то строчка пустая
я не понял, просто пропускаем ту строку или же вносим следудуйщюю строку. Ну а про файлы примеры тут беда, сделал сам так как: дело было вечером, делать было нечего.
К сообщению приложен файл: oh_nick_book.zip (28.6 Kb)


Ученик.

Сообщение отредактировал MikeVol - Суббота, 26.08.2023, 22:04
 
Ответить
СообщениеHugo, Приветствую вас! Сделал как понял по описанию
вносить в таблицу только ту строчка, которая полностью совпадает с двумя файлами , т.е и Код1=Код2=Дата1=Дата2=Начало Времени1=Начало Времени2=Конец времени 1=Конец времени 2

а вот тут
Если что-то не сходится то строчка пустая
я не понял, просто пропускаем ту строку или же вносим следудуйщюю строку. Ну а про файлы примеры тут беда, сделал сам так как: дело было вечером, делать было нечего.

Автор - MikeVol
Дата добавления - 26.08.2023 в 22:02
Oh_Nick Дата: Воскресенье, 27.08.2023, 18:30 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 445
Репутация: 8 ±
Замечаний: 20% ±

Excel 2019
MikeVol, спасибо, а без макроса возможно сравнить формулой? например

Берем код из столбца А ,проверяем его в столбце B ,если находит идем дальше
К коду из столбца А относится дата из столбца C, ее проверяем с датой из столбца D , если находит идем дальше
К коду из столбца А относится начало времени из столбца E, ее проверяем с началом времени из столбца F, если находит идем дальше
К коду из столбца A относится конец времени из столбца F, его проверяем с концом времени из столбца G и если все ок то записываем значение в колонки как вы указали, если нет. то ничего не пишем
 
Ответить
СообщениеMikeVol, спасибо, а без макроса возможно сравнить формулой? например

Берем код из столбца А ,проверяем его в столбце B ,если находит идем дальше
К коду из столбца А относится дата из столбца C, ее проверяем с датой из столбца D , если находит идем дальше
К коду из столбца А относится начало времени из столбца E, ее проверяем с началом времени из столбца F, если находит идем дальше
К коду из столбца A относится конец времени из столбца F, его проверяем с концом времени из столбца G и если все ок то записываем значение в колонки как вы указали, если нет. то ничего не пишем

Автор - Oh_Nick
Дата добавления - 27.08.2023 в 18:30
MikeVol Дата: Воскресенье, 27.08.2023, 18:32 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 291
Репутация: 51 ±
Замечаний: 0% ±

Excel LTSC 2021 EN
Oh_Nick, Доброго времени суток. Я не смогу
сравнить формулой

Ждём до завтра, выходные всё таки. Может кто и сможет вам помочь формулой решить ваш вопрос.


Ученик.
 
Ответить
СообщениеOh_Nick, Доброго времени суток. Я не смогу
сравнить формулой

Ждём до завтра, выходные всё таки. Может кто и сможет вам помочь формулой решить ваш вопрос.

Автор - MikeVol
Дата добавления - 27.08.2023 в 18:32
Hugo Дата: Воскресенье, 27.08.2023, 18:34 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
Чем не годится моя идея с полуавтоматом? Формула поставит единицы, по ним ставите фильтр и копипастите куда угодно, если уж так нужен этот копипаст - задача вообще в чём?


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеЧем не годится моя идея с полуавтоматом? Формула поставит единицы, по ним ставите фильтр и копипастите куда угодно, если уж так нужен этот копипаст - задача вообще в чём?

Автор - Hugo
Дата добавления - 27.08.2023 в 18:34
Oh_Nick Дата: Воскресенье, 27.08.2023, 18:52 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 445
Репутация: 8 ±
Замечаний: 20% ±

Excel 2019
Hugo, а куда вашу формулу написать? ее можно для отдельного файла сделать? допустим вот у меня есть отдельный файл с заголовками КОД ДАТА НАЧАЛО ВРЕМЕНИ КОНЕЦ ВРЕМЕНИ. как мне этот файл заполнить?
 
Ответить
СообщениеHugo, а куда вашу формулу написать? ее можно для отдельного файла сделать? допустим вот у меня есть отдельный файл с заголовками КОД ДАТА НАЧАЛО ВРЕМЕНИ КОНЕЦ ВРЕМЕНИ. как мне этот файл заполнить?

Автор - Oh_Nick
Дата добавления - 27.08.2023 в 18:52
Hugo Дата: Воскресенье, 27.08.2023, 19:26 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3249
Репутация: 707 ±
Замечаний: 0% ±

2019
В любой свободный столбец пишите мою формулу массива. В старых версиях ввод завершаете Ctrl+Shift+Enter, в новых вроде и так подхватит, но не проверял, у меня тоже 2019


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеВ любой свободный столбец пишите мою формулу массива. В старых версиях ввод завершаете Ctrl+Shift+Enter, в новых вроде и так подхватит, но не проверял, у меня тоже 2019

Автор - Hugo
Дата добавления - 27.08.2023 в 19:26
cmivadwot Дата: Воскресенье, 27.08.2023, 19:40 | Сообщение № 15
Группа: Проверенные
Ранг: Обитатель
Сообщений: 416
Репутация: 84 ±
Замечаний: 0% ±

365
Oh_Nick, колхозный вариант..
К сообщению приложен файл: bookvapapa1.xlsx (15.7 Kb)
 
Ответить
СообщениеOh_Nick, колхозный вариант..

Автор - cmivadwot
Дата добавления - 27.08.2023 в 19:40
Oh_Nick Дата: Воскресенье, 27.08.2023, 19:42 | Сообщение № 16
Группа: Проверенные
Ранг: Обитатель
Сообщений: 445
Репутация: 8 ±
Замечаний: 20% ±

Excel 2019
Hugo, хорошо, попробую
cmivadwot, вот это очень даже ничего, спасибо, буду тестить!
 
Ответить
СообщениеHugo, хорошо, попробую
cmivadwot, вот это очень даже ничего, спасибо, буду тестить!

Автор - Oh_Nick
Дата добавления - 27.08.2023 в 19:42
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сопоставление похожих значений (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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