посчитать данные из одной таблицы с расшифровкой из другой

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

посчитать данные из одной таблицы с расшифровкой из другой (Формулы/Formulas)

chelovek

Дата: Четверг, 28.08.2014, 23:54 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Имеется таблица заказа изделий с количеством каждого изделия. Грубо говоря (Товар a1=5штук, Товар b2=10 штук, ...) Причем в ней только те товары, которые надо сделать, т.е. товаров вообще гораздо больше. И есть другая таблица, где каждое изделие расписано по комплектующим. К примеру если Товар a1 состоит из трех типов деталей, то в этой таблице идет три строки к этому товару. В каждой строке есть поля "Код Товара"=a1, Код детали, Количество детали, цена детали и стоимость этих деталей. Как из первой и второй таблицы получить третью, где будет указано, сколько каких деталей нужно на весь заказ из товаров с указанием количества и денежной суммы?
Таблицы заказов все время разные. Как быстро пересчитывать входящие в заказ детали? Прикладываю две исходные таблицы (в одну засунул) и что надо получить на выходе. Я с экселем плохо знаком, подскажите хоть какие есть варианты? А я потом сам изучу направление. Заранее спасибо.

К сообщению приложен файл: 8586047.xlsx (9.5 Kb) · _______.xlsx (9.0 Kb)

Сообщение отредактировал chelovek - Пятница, 29.08.2014, 06:36

Ответить

Rustem

Дата: Пятница, 29.08.2014, 00:52 | Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 48 ±

Замечаний: 0% ±

Excel 2013

Ваших таблиц нет

WMR: R183220163895

Ответить

chelovek

Дата: Пятница, 29.08.2014, 06:37 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Добавил еще раз, странно.

Ответить

PowerBoy

Дата: Пятница, 29.08.2014, 10:13 | Сообщение № 4

Группа: Проверенные

Ранг: Участник

Сообщений: 100

Репутация: 31 ±

Замечаний: 0% ±

2003

[vba]

Код

Public Sub RefreshData()
'Created using add-in ActiveTables
Dim strConnection As String
Dim strSQL As String
strConnection = iif(Val(Application.Version) < 12,"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=3';","OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=3';")
strSQL = "SELECT   справочник.[Наименование детали] AS [Наименование детали],  SUM(справочник.Штук*заказ.[Количество штук]) AS Штук,  SUM(справочник.[Цена общая]*заказ.[Количество штук]) AS [Цена общая]  FROM   [Лист1$a2:b5] AS заказ    RIGHT JOIN [Лист1$a10:f22] AS справочник   ON заказ.[Код Товара]=справочник.[Код Товара]   GROUP BY   справочник.[Наименование детали]  ORDER BY   справочник.[Наименование детали]  "
With ThisWorkbook.Sheets(2)
     .UsedRange.Clear
     With .QueryTables.Add(strConnection, .Range("A1"), strSQL)
          .Refresh False
          .Delete
     End With
End With
End Sub

[/vba]

Excel + SQL = ActiveTables (http://vk.com/ExcelSQL)

Ответить

Rioran

Дата: Пятница, 29.08.2014, 11:34 | Сообщение № 5

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

PowerBoy, классная техника, не думал, что совместить SQL и Excel - так легко! Слегка доработал код визуально, чтобы легче было корректировать, плюс добавил отображение столбца "Код детали":

[vba]

Код

Public Sub StoneCold()

Dim strConnection As String
Dim strSQL As String

strConnection = IIf(Val(Application.Version) < 12, "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=3';", "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=3';")

strSQL = strSQL + "SELECT справочник.[Код детали] AS [Код детали], "
strSQL = strSQL + "       справочник.[Наименование детали] AS [Наименование детали], "
strSQL = strSQL + "       SUM(справочник.Штук*заказ.[Количество штук]) AS Штук, "
strSQL = strSQL + "       SUM(справочник.[Цена общая]*заказ.[Количество штук]) AS [Цена общая] "
strSQL = strSQL + "FROM [Лист1$a10:f22] AS справочник "
strSQL = strSQL + "INNER JOIN [Лист1$a2:b5] AS заказ ON заказ.[Код Товара]=справочник.[Код Товара] "
strSQL = strSQL + "GROUP BY справочник.[Наименование детали], справочник.[Код детали] "
strSQL = strSQL + "ORDER BY справочник.[Наименование детали]"

With ThisWorkbook.Sheets(2)
     .UsedRange.Clear
     With .QueryTables.Add(strConnection, .Range("A1"), strSQL)
         .Refresh False
         .Delete
     End With
End With

End Sub

[/vba]

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Ответить

СообщениеPowerBoy, классная техника, не думал, что совместить SQL и Excel - так легко! Слегка доработал код визуально, чтобы легче было корректировать, плюс добавил отображение столбца "Код детали":

[vba]

Код