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

Вход

Регистрация

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

 

= Мир MS Excel/Перенести данные штрих кода и цен на другой лист. - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенести данные штрих кода и цен на другой лист. (Формулы/Formulas)
Перенести данные штрих кода и цен на другой лист.
Mark1976 Дата: Пятница, 07.10.2016, 12:00 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Здравствуйте. У меня есть таблица. В ней данные по ШК и ценам. В колонке К штрих коды, в колонке F цены. На один ШК может быть несколько цен. Мне необходимо на отдельный лист (ЛИСТ 2) в файле, вытащить данные, построчно. А потом если строке встречаются дубли, то одну цифру удалить. Потом буду сводить данные с 1С, т.к. в нашем справочнике к одному ШК не все цены привязаны. Буду признателен за помощь.
К сообщению приложен файл: 071016_1.xlsb (27.4 Kb)
 
Ответить
СообщениеЗдравствуйте. У меня есть таблица. В ней данные по ШК и ценам. В колонке К штрих коды, в колонке F цены. На один ШК может быть несколько цен. Мне необходимо на отдельный лист (ЛИСТ 2) в файле, вытащить данные, построчно. А потом если строке встречаются дубли, то одну цифру удалить. Потом буду сводить данные с 1С, т.к. в нашем справочнике к одному ШК не все цены привязаны. Буду признателен за помощь.

Автор - Mark1976
Дата добавления - 07.10.2016 в 12:00
_Boroda_ Дата: Пятница, 07.10.2016, 14:10 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Правильно ли я понял, что у Вас в файле 27377 заполненных строк?
Вариант формулами (у Вас же вопрос размещен в "Вопросах по Excel") с использованием именованных диапазонов, автоопределяющих для каждого штрих-кода свой маленький диапазончик, из которого уже вынимаем уникальные цены и сортируем их.
К сообщению приложен файл: 071016_4.xlsb (28.9 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПравильно ли я понял, что у Вас в файле 27377 заполненных строк?
Вариант формулами (у Вас же вопрос размещен в "Вопросах по Excel") с использованием именованных диапазонов, автоопределяющих для каждого штрих-кода свой маленький диапазончик, из которого уже вынимаем уникальные цены и сортируем их.

Автор - _Boroda_
Дата добавления - 07.10.2016 в 14:10
Mark1976 Дата: Пятница, 07.10.2016, 14:26 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Добрый день. Да,мой файл большой там много строк. Просто я его обрезал.
 
Ответить
СообщениеДобрый день. Да,мой файл большой там много строк. Просто я его обрезал.

Автор - Mark1976
Дата добавления - 07.10.2016 в 14:26
_Boroda_ Дата: Пятница, 07.10.2016, 14:44 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Я догадался. в большом файле-то работает?
Нужно вставить в мой файл свои данные, перейти на лист с формулами, обновить сводную в столбце А и протянуть серые ячейки вниз на сколько нужно


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЯ догадался. в большом файле-то работает?
Нужно вставить в мой файл свои данные, перейти на лист с формулами, обновить сводную в столбце А и протянуть серые ячейки вниз на сколько нужно

Автор - _Boroda_
Дата добавления - 07.10.2016 в 14:44
Mark1976 Дата: Пятница, 07.10.2016, 15:00 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Сейчас проверю. Только пришел домой.
 
Ответить
СообщениеСейчас проверю. Только пришел домой.

Автор - Mark1976
Дата добавления - 07.10.2016 в 15:00
Mark1976 Дата: Пятница, 07.10.2016, 15:16 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
_Boroda_. Нет, не получилось. Скопировал данные в Ваш файл, вышла ошибка(видимо макрос) ругнулся. И на листе 2 стало как изначально в моей файле.
 
Ответить
Сообщение_Boroda_. Нет, не получилось. Скопировал данные в Ваш файл, вышла ошибка(видимо макрос) ругнулся. И на листе 2 стало как изначально в моей файле.

Автор - Mark1976
Дата добавления - 07.10.2016 в 15:16
_Boroda_ Дата: Пятница, 07.10.2016, 15:34 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. В моем решении макросов нет. А вот у Вас на листе Реестр цен есть макрос. Перед тем, как вставить данные, этот макрос нужно отключить. Например, при открытии файла не разрешать макросы.
2. Про ЛИСТ2 забудьте. Я же написал
перейти на лист с формулами
протянуть серые ячейки
Где Вы на Листе 2 все это видите? Вам нужен Лист1


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. В моем решении макросов нет. А вот у Вас на листе Реестр цен есть макрос. Перед тем, как вставить данные, этот макрос нужно отключить. Например, при открытии файла не разрешать макросы.
2. Про ЛИСТ2 забудьте. Я же написал
перейти на лист с формулами
протянуть серые ячейки
Где Вы на Листе 2 все это видите? Вам нужен Лист1

Автор - _Boroda_
Дата добавления - 07.10.2016 в 15:34
Mark1976 Дата: Пятница, 07.10.2016, 16:10 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
_Boroda_, я туплю. Делаю как Вы говорите не получается. Пробую перенести формулу в новый файл, но тоже не получается. Не пойму принцип. Знаний очень мало. Можно в этот файл формулу прописать? Еще выходит ошибка (скрин приложил)
К сообщению приложен файл: 0026526.jpg (35.3 Kb) · lp2016-10-07-1.xlsb (55.0 Kb)


Сообщение отредактировал Mark1976 - Пятница, 07.10.2016, 16:21
 
Ответить
Сообщение_Boroda_, я туплю. Делаю как Вы говорите не получается. Пробую перенести формулу в новый файл, но тоже не получается. Не пойму принцип. Знаний очень мало. Можно в этот файл формулу прописать? Еще выходит ошибка (скрин приложил)

Автор - Mark1976
Дата добавления - 07.10.2016 в 16:10
_Boroda_ Дата: Пятница, 07.10.2016, 16:21 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Протяните в файле формулы из строки 4 вниз
К сообщению приложен файл: 071016_6.xlsb (49.3 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПротяните в файле формулы из строки 4 вниз

Автор - _Boroda_
Дата добавления - 07.10.2016 в 16:21
Mark1976 Дата: Пятница, 07.10.2016, 16:26 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
_Boroda_, работает. Я правильно понял. Теперь чтобы мне поднять весь реестр в 20000 строк мне надо:
1. Сделать сводную по полю ШК?
2. Вставить вашу формулу и протянуть все вниз?

Спасибо за помощь, очень признателен.
 
Ответить
Сообщение_Boroda_, работает. Я правильно понял. Теперь чтобы мне поднять весь реестр в 20000 строк мне надо:
1. Сделать сводную по полю ШК?
2. Вставить вашу формулу и протянуть все вниз?

Спасибо за помощь, очень признателен.

Автор - Mark1976
Дата добавления - 07.10.2016 в 16:26
_Boroda_ Дата: Пятница, 07.10.2016, 16:35 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Я правильно понял.
Нет. Сводная в моем уже есть. В Вашем файле это работать не будет. Я ж писал
с использованием именованных диапазонов
У меня в файле есть имена (посмотреть можно через Контрл F3). У Вас в файле их нет. Поэтому или делайте имена у себя по образцу из моего файла, или используйте мой файл.

1.
макрос нужно отключить

2. на Лист3
вставить в мой файл свои данные

3.
перейти на лист с формулами, обновить сводную в столбце А и протянуть серые ячейки вниз на сколько нужно


А вообще - на больших объемах все это нужно делать макросом.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Я правильно понял.
Нет. Сводная в моем уже есть. В Вашем файле это работать не будет. Я ж писал
с использованием именованных диапазонов
У меня в файле есть имена (посмотреть можно через Контрл F3). У Вас в файле их нет. Поэтому или делайте имена у себя по образцу из моего файла, или используйте мой файл.

1.
макрос нужно отключить

2. на Лист3
вставить в мой файл свои данные

3.
перейти на лист с формулами, обновить сводную в столбце А и протянуть серые ячейки вниз на сколько нужно


А вообще - на больших объемах все это нужно делать макросом.

Автор - _Boroda_
Дата добавления - 07.10.2016 в 16:35
Mark1976 Дата: Пятница, 07.10.2016, 16:42 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
_Boroda_,
Цитата
обновить сводную в столбце А

Не пойму как это сделать. При нажатии обновит все выходит ошибка. Данные в ваш файл вставил (22000 строк)


Сообщение отредактировал Mark1976 - Пятница, 07.10.2016, 16:44
 
Ответить
Сообщение_Boroda_,
Цитата
обновить сводную в столбце А

Не пойму как это сделать. При нажатии обновит все выходит ошибка. Данные в ваш файл вставил (22000 строк)

Автор - Mark1976
Дата добавления - 07.10.2016 в 16:42
_Boroda_ Дата: Пятница, 07.10.2016, 16:52 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Измените источник данных, я забыл в том файле.
Впрочем, вот, берите с автообновляемым источником

Добавлено
Попробуйте файл _8
К сообщению приложен файл: 071016_7.xlsb (45.3 Kb) · 071016_8.xlsb (45.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИзмените источник данных, я забыл в том файле.
Впрочем, вот, берите с автообновляемым источником

Добавлено
Попробуйте файл _8

Автор - _Boroda_
Дата добавления - 07.10.2016 в 16:52
Mark1976 Дата: Пятница, 07.10.2016, 17:04 | Сообщение № 14
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
_Boroda_, в этом файле не обновляется сводная. опять ругается на не существующий у меня путь. В колонке А как было 290 строк так и осталось. Файл приложить не могу он весит 400кб


Сообщение отредактировал Mark1976 - Пятница, 07.10.2016, 17:11
 
Ответить
Сообщение_Boroda_, в этом файле не обновляется сводная. опять ругается на не существующий у меня путь. В колонке А как было 290 строк так и осталось. Файл приложить не могу он весит 400кб

Автор - Mark1976
Дата добавления - 07.10.2016 в 17:04
Manyasha Дата: Пятница, 07.10.2016, 18:16 | Сообщение № 15
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
Mark1976, встаньте на сводную - вкладка Работа со сводными таблицами- Параметры - Источник данных - в поле "Таблица или диапазон" оставьте только "ДляСвод" (путь уберите) - Ок.


ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеMark1976, встаньте на сводную - вкладка Работа со сводными таблицами- Параметры - Источник данных - в поле "Таблица или диапазон" оставьте только "ДляСвод" (путь уберите) - Ок.

Автор - Manyasha
Дата добавления - 07.10.2016 в 18:16
Mark1976 Дата: Пятница, 07.10.2016, 19:55 | Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Всем спасибо за помощь. Сводная обновилась. Протянул формулу. Пересчет будет наверное очень очень долго (каждая строка по несколько секунд). _Boroda_, Вы правы процесс этот не быстрый. А макрос быстро отработает если его использовать?
 
Ответить
СообщениеВсем спасибо за помощь. Сводная обновилась. Протянул формулу. Пересчет будет наверное очень очень долго (каждая строка по несколько секунд). _Boroda_, Вы правы процесс этот не быстрый. А макрос быстро отработает если его использовать?

Автор - Mark1976
Дата добавления - 07.10.2016 в 19:55
Mark1976 Дата: Пятница, 07.10.2016, 21:35 | Сообщение № 17
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Уже 1,5 часа файл обрабатывается. А мне надо такую процедуру делать минимум 2 раза в неделю. Может можно как то ускорить данный процесс? Может есть еще какой либо способ. Уж простите за надоедание.
 
Ответить
СообщениеУже 1,5 часа файл обрабатывается. А мне надо такую процедуру делать минимум 2 раза в неделю. Может можно как то ускорить данный процесс? Может есть еще какой либо способ. Уж простите за надоедание.

Автор - Mark1976
Дата добавления - 07.10.2016 в 21:35
Manyasha Дата: Пятница, 07.10.2016, 21:54 | Сообщение № 18
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
Mark1976, вариант с макросом (за основу взяла последний файл Александра):
[vba]
Код
Sub svod()
    Dim sh1 As Worksheet, sh2 As Worksheet, barcode, price, lr&, i&, res
    Dim arrKeys, arrItems, temp, col#
    Set sh1 = ThisWorkbook.Sheets("Данные")
    Set sh2 = ThisWorkbook.Sheets("Свод")
    With sh1
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        price = .Range("f2:f" & lr).Value
        barcode = .Range("i2:i" & lr).Value
    End With
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(barcode)
            If .exists(Trim(barcode(i, 1))) Then
                .Item(Trim(barcode(i, 1))) = .Item(Trim(barcode(i, 1))) & "|" & price(i, 1)
            Else
                .Item(Trim(barcode(i, 1))) = price(i, 1)
            End If
        Next i
        arrKeys = .keys
        arrItems = .items
    End With
    With sh2
        .[a1].CurrentRegion.Offset(1).ClearContents
        For i = 0 To UBound(arrKeys)
            .Cells(i + 2, 1) = arrKeys(i)
            temp = Split(arrItems(i), "|")
            col = IIf(UBound(temp), UBound(temp) + 1, 1)
            .Cells(i + 2, 2).Resize(, col) = temp
        Next i
    End With
End Sub
[/vba]

Результат смотрите на листе Свод. Если имена листов у Вас другие (НЕ Данные и Свод), не забудьте в коде поменять.
К сообщению приложен файл: 071016_9.xlsb (69.1 Kb)


ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеMark1976, вариант с макросом (за основу взяла последний файл Александра):
[vba]
Код
Sub svod()
    Dim sh1 As Worksheet, sh2 As Worksheet, barcode, price, lr&, i&, res
    Dim arrKeys, arrItems, temp, col#
    Set sh1 = ThisWorkbook.Sheets("Данные")
    Set sh2 = ThisWorkbook.Sheets("Свод")
    With sh1
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        price = .Range("f2:f" & lr).Value
        barcode = .Range("i2:i" & lr).Value
    End With
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(barcode)
            If .exists(Trim(barcode(i, 1))) Then
                .Item(Trim(barcode(i, 1))) = .Item(Trim(barcode(i, 1))) & "|" & price(i, 1)
            Else
                .Item(Trim(barcode(i, 1))) = price(i, 1)
            End If
        Next i
        arrKeys = .keys
        arrItems = .items
    End With
    With sh2
        .[a1].CurrentRegion.Offset(1).ClearContents
        For i = 0 To UBound(arrKeys)
            .Cells(i + 2, 1) = arrKeys(i)
            temp = Split(arrItems(i), "|")
            col = IIf(UBound(temp), UBound(temp) + 1, 1)
            .Cells(i + 2, 2).Resize(, col) = temp
        Next i
    End With
End Sub
[/vba]

Результат смотрите на листе Свод. Если имена листов у Вас другие (НЕ Данные и Свод), не забудьте в коде поменять.

Автор - Manyasha
Дата добавления - 07.10.2016 в 21:54
Mark1976 Дата: Пятница, 07.10.2016, 22:14 | Сообщение № 19
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Manyasha, спасибо. Вставил в свой файл не получилось. В чем моя ошибка?
К сообщению приложен файл: 1435133.xlsb (88.4 Kb)
 
Ответить
СообщениеManyasha, спасибо. Вставил в свой файл не получилось. В чем моя ошибка?

Автор - Mark1976
Дата добавления - 07.10.2016 в 22:14
Mark1976 Дата: Пятница, 07.10.2016, 22:19 | Сообщение № 20
Группа: Проверенные
Ранг: Ветеран
Сообщений: 738
Репутация: 3 ±
Замечаний: 0% ±

Excel 2010, 2013
Manyasha, Вставил в ваш файл 23000 строк, отработало только 346. Может на листе Свод диапазон как то расширить?
 
Ответить
СообщениеManyasha, Вставил в ваш файл 23000 строк, отработало только 346. Может на листе Свод диапазон как то расширить?

Автор - Mark1976
Дата добавления - 07.10.2016 в 22:19
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенести данные штрих кода и цен на другой лист. (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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