Страницы: 1 2 След.
RSS
Выборка строк из таблицы по критерию и копирование результата на другой лист.
 
Добрый день всем, с наступившим праздником!  
Кто нибудь сможет помочь мне решить такую задачку с помощью макроса?  
Мне нужно отобрать из листа "Вывозка" строки по условию, заданному на листе "Условие", т.е. строки, в которых дата в столбце D на листе "Вывозка" больше или равна ячейка F1 и меньше или равна ячейка F2 на листе "Условие". И далее скопировать полученные строки на лист "Результат", если можно, то только указанные там столбцы, если нет, то полностью строки.  
И еще один вопрос, я создал запрос (импорт внешних данных из файла xls), почему если в исходном файле в первой строке любого столбца не стоит 0 вместо "пустой ячейки", данные этого столбца не импортируются? И можно ли это попраить как то?
 
Очень сложная задача или я может толком не объяснил что нужно?
 
Решил Ваш задачу с помощью расширенного фильтра (в принципе это тот же запрос).  
При импорте внешних данных формат данных (число, текст, дата) определяется по первой строке каждого столбца данных при создании запроса, если формат в следующих строках отличается, эти данные не отображаются.
 
Уважаемый Лесник  
раз вы используете в своей работе запросы то есть еще вариант решения задачи запросом по условию -больше или равна ячейка F1 и меньше или равна ячейка F2 на листе "Условие"-
 
Спасибо LAD, хорошо, что пояснения написали в коде, разобрался довольно таки быстро, как раз то, что нужно было. Только вот появилась одна загвоздка, как можно сделать, чтобы месяц, выбранный в поле со списком появился в заданной ячейке (дело в том, что эта ячейка на другие формулы влияет и тогда приходится дважды месяц выбирать: в поле со списком и в ячейке)?
 
nik, Ваш вариант пока не смотрел, дома буду разбираться. Спасибо Вам за уделенное мне внимание.
 
Можно в Private Sub ComboBox1_Change() в любом месте вставить строку:  
Sheets("имя листа").Range("адрес ячейки").Value = ComboBox1.Text  
 
Или в свойстве LinkedCell ComboBox1 прописать :  
Условие!C5  
Условие – имя листа на котором находится ячейка  
C5 – адрес ячейки    
С вашими объемами данных возможно удобнее использовать Сводные таблицы, чем формулы (скорость вычислений будет на много больше, по крайней мере извлекать данные проще) .
 
Сделал выборку сводной таблицей. Постарался максимально выполнить Ваши пожелания. Это, в общем – то черновик, но похоже не плохой черновик.
 
{quote}{login=LAD}{date=09.01.2009 01:45}{thema=}{post}Сделал выборку сводной таблицей. Постарался максимально выполнить Ваши пожелания. Это, в общем – то черновик, но похоже не плохой черновик.{/post}{/quote}  
 
Интересная штука, только не все выбирает, например посмотрите за Февраль, водитель Мочалов, выбираются только Квартала где Дел.№1, хотя есть на листе Результат и с дел.№2 и Дел.№3
 
На листе Вывозка у водителя Мочалова в феврале все записи только с д.1. Откуда могут взяться записи с другими делянками на листе Результат? Отфильтруйте с помощью автофильтра для наглядности.
 
{quote}{login=LAD}{date=09.01.2009 03:42}{thema=}{post}На листе Вывозка у водителя Мочалова в феврале все записи только с д.1. Откуда могут взяться записи с другими делянками на листе Результат? Отфильтруйте с помощью автофильтра для наглядности.{/post}{/quote}  
Извиняюсь, действительно так, просто я нескольких водителей перепробовал, у всех только Д.1, оказывается просто совпадение такое. Выборка на самом деле работает значительно быстрее по сравнению с формулами. Спасибо большое!
 
Пожалуйста. Скорость выборки – не единственное достоинство. Сводную таблицу можно построить, не имея понятия о формулах и программировании.    
  В свою очередь я то же извиняюсь. В предложенной книге сводная таблица построена немного неправильно. Прилагаю правильно построенную таблицу. Так же добавлена подгонка ширины столбцов.  
  При работе с ComboBox – выбор месяца есть особенность, он нормально работает только с масштабами листа 75% и 100%.  
  Сделал черновик таблицы со столбцами Ремонт и Подъемка, а так же с разноской строк по дням месяца. Если есть необходимость сообщите – доработаю. Кстати, какие данные должны находиться в Ремонте и Подъемке.
 
{quote}{login=LAD}{date=12.01.2009 01:27}{thema=}{post}Кстати, какие данные должны находиться в Ремонте и Подъемке.{/post}{/quote}  
 
Ремонт, это просто смены у водителей, когда машина вышла из строя, ставятся 8-ки (я приложу файл, которым мы до сих пор пользовались). Насчет подъемки нужно описать техпроцесс: в лесу работают несколько лесозаготовительных бригад в разных кварталах и делянках, они срубают деревья, обрубают с них сучья и стаскивают (трелёвка леса называется) стволы деревьев (хлысты) в штабеля на погрузочные площадки. Эти бригады работают и живут в лесу вахтой, закончили рубить в одной делянке, переезжают в другую и т.д., т.к. их зарплата идет с заготовленных кубометров, а приемщиков в лесу нет, учет идет по количеству вывезенной из делянок древесины в кубометрах (каждый лесовоз с хлыстами принимают приемщицы путем обмера каждого хлыста, когда он приезжает на базу, так называемый нижний склад). На базе лес распиливается (кряжуется) на сортименты на спец. эстакаде (получают пиловочник, фанерный кряж, баланс, дрова и т.д). Весь этот сортимент при разделке также замеряется уже другими приемщиками и этот объем является окончательным и точным. Прибывший из леса лесовоз могут разгрузить сразу на эстакаду или, если она заполнена его разгружают в запас на поле. И наоборот если на эстакаде лес заканчивается, а лесовозов из делянок еще нет лес подают из запаса (подъемкой называют), могут напрямую погрузчиком или опять грузят на лесовоз, который подвозит лес к эстакаде, если штабеля рядом с эстакадой заканчиваются. Зарплата водителей лесовозов зависит от количества сделанных рейсов и расстояния до делянок. Погрузкой хлыстов в лесу на лесовозы, разгрузкой лесовозов на нижнем складе в запас и погрузкой из запаса заняты операторы погрузчиков и они могут в разные дни быть или в лесу или на нижнем складе и их зарплата зависит от количества погруженных, разгруженных машин, причем расценки разные в лесу и на базе, на погрузке и разгрузке. Окончательный объем для расчета с лесозаготовительными бригадами определяют так, берут объем принятой древесины на лесовозах (в сумме по каждой делянке), которые напрямую разгрузили на эстакаду, потом берут полученный из этих машин объем сортиментов (по разделке обычно он немного больше получается) и расчитывают процент "припила", который затем применяют на весь объем вывозки из этой делянки. Иначе не посчитать, т.к. при разгрузке в запас вся древесина уже перемешивается, могут возить в один день из 3-х разных делянок. Кроме того у нас 2 нижних склада в разных городах и вывозка может быть из одной делянки на разные склады.  
У меня на сервере 2 EXCEL файла, которые заполняют приемщицы с каждого склада (у каждого склада свой файл, аналогичный листу Вывозка). Здесь на форуме я нашел макрос, с помощью которого все записи из обоих файлов я объединяю в один.  
Дальше создал запрос на обновление внешних данных в прикрепленном файле и там уже формируются сводные данные с помощью формул по каждому водителю и оператору ежемесячно для бухгалтерии. Делают это те же приемщицы. Для лесозаг. бригад я уже сам в другом файле объем расчитываю пользуясь этими же данными (для этого там и вставлены доп. столбцы для кубометров по разделке тех машин, которые разгрузили сразу на эстакаду), там у меня все нормально получается, а вот по операторам и водителям прилично тормозит при расчетах.  
Вроде все расписал.  
И еще вопрос - можно ли создать макрос, который бы открыл файл, выполнил в нем 3 макроса, сохранил этот документ и закрыл его? Это по поводу файла, который объединяет данные из 2 файлов с разноской по складам.
 
Скачал файл. Попробую разобраться.
 
Судя по приложенной книге, приемщицы в книгах Вывозка_Тура.xls и Вывозка_Качканар.xls заполняют по два листа. Один лист  Вывозка, другой лист  Подъемка. Данные листов Вывозка объединяются, а данные листов Подъемка объединяются? Что бы получить результат надо иметь исходные данные, а у меня, их нет.  
 
Если я правильно понял, данные в лист Ремонт вносятся только за один месяц и в дальнейшем не сохранятся. Это первый недостаток, второй – при такой структуре таблицы, данные трудновато извлекать, третий – если в таблице хранить данные за год, то она будет занимать много места и размер файла намного увеличится. Предложение – на листе Ремонт оставить только три колонки – дата, водитель, время.  
 
Кстати о размере файла, что бы файл был компактнее, старайтесь хранить всевозможные списки на отдельном листе.  
 
Если Вы используете запрос на обновление каждый раз при открытии файла, то возможно будет удобнее использовать запрос для выборки данных за месяц, чем хранить одни и те же данные в двух книгах.  
 
По поводу макроса. Да возможно, но проще наверно сразу открыть файл, в котором хранятся макросы и одной кнопкой или по событию Workbook_Open() выполнять макросы.  
 
Private Sub Workbook_Open()  
Call Макрос1  
Call Макрос2  
Call Макрос3  
End Sub
 
В этом году навенрное все же заставлю заполнять лист подъемка в обеих книгах, до сих пор только в Туре заполняли, т.к в Качканаре на подъемке всегда один и тот же водитель задействован. А данные в прикрепленном файле есть, там 3 листа скрыты, в т.ч. и подъемка. Насчет ремонта, наверное Вы правы, лучше будет изменить табличку и тогда можно будет сохранять данные за все месяца.  
Списки я стараюсь сделать всегда на отдельном листе, но приходится их переносить на другие листы, т.к. в ячейках>данные>проверка>список для ячеек в разноске вроде невозможно указать список, находящийся на другом листе, даже если он в той же книге (или я не прав?).  
Запрос конечно можно было бы отфильтровывать данные только за месяц при открытии файла если бы этим занимался один человек, и он бы более менее разбирался в компах. А у нас на приемке сидят женщины, которые порой например даже свернутое окно файла не видят и открывают этот же локумент второй раз и не понимают что там "он" им какие то сообщения выдает и т.п. А ведь здесь придется для смены месяца как минимум закрывать файл и снова открывать, указать условие и т.д.  
Насчет макроса, у меня сейчас так и сделано, нужно открыть файл, нажать кнопку, при этом выполняются все 3 макроса, потом закрыть файл с сохранением. Но по вышеописанной причине и хотелось бы чтобы все это делалось автоматически при открытии файла операторы, т.к. у меня основная работа в лесу и меня не бывает на базе по нескольку дней, а у них всегда заморочки начинаются если нужно какие то действия произвести с разными файлами или папками.
 
Насчет подъемки, я увидел в формуле ссылку только на книгу из Туры поэтому и задал такой вопрос. В Качканаре даже если и один водитель количество рейсов и объемы, разные каждый день. А как учитывается работа операторов, они – то меняются?  
 
Насчет >данные>проверка>список Вы абсолютно правы.  
 
Насчет запросов прав nik. В своем сообщении он указывает, что для запроса можно выставлять условия, а обновить запрос можно из макроса (программно) в любое время. Если в свойствах внешнего диапазона (запроса) убрать птичку  “обновление при открытии файла”, а потом обновить запрос программно, то не будет сообщения насчет возможной вредоности запроса.    
 
Насчет макроса. В своем последнем сообщении я упомянул событие Workbook_Open, это событие возникает при открытии файла, т.е. если файл открывается, то автоматически выполняется этот макрос, а из этого макроса можно запустить любые другие макросы в этой книге. Он всегда располагается в модуле  “ЭтаКнига”.  
 
Продолжаю работу над Вашей задачей.
 
Лесник, извините за долгое молчание. Я хотел сделать что-то более-менее приличное. Результат прилагается.
 
Лесник.Отзовитесь.
 
Добрый день, LAD!  
Давно сюда не заглядывал, извините. Сейчас просмотрел Ваш файл, все просто отлично работает, единственно что нужно бы поправить - возможность выбрать нужный год, т.к. я так понял, что в макросе "жестко" задан 2008 год. А разноска продолжается в один файл и в 2009 г. Желательно в какой либо ячейке на листе Лесовозники можно было выбрать или просто ввести нужный для выборки год. Еще не нужны итоги по складам, правда я здесь сам разобрался, убрал.  
Спасибо Вам огромное, позже поизучаю как у Вас все устроено, сейчас просто нет времени, у меня основная работа в лесу, компы как добровольная обязанность, когда есть время что то придумываю.
 
Странно, что нет замечаний. Обычно не удается сразу вычистить всех блох.  
Год добавлю. Что еще надо сделать? Операторов?
 
Да, остались операторы. Я прикреплю файлик в котором форма таблички будет, в которой привыкли получать данные бухгалтера (все операторы на одном печатном листе). Для экономии места хорошо бы скрывались пустые столбцы.
 
Добавил год. Список формируется на основании исходных данных -  из всех таблиц выбираются минимальная дата и максимальная дата и на их основе строится список.  
Принялся за операторов.
 
LAD, еще ошибку нашел, если очистить все данные на листе Ремонт, то при смене месяца или года появляется ошибка и после этого ничего не работает. Если хотя бы 1 строку оставить, все нормально.
 
Странно, что Вам не пришла идея очисть полностью листы Вывозка и Подъемка.  
Листы Вывозка, Подъемка, Ремонт предназначены для хранения данных и наличие строки заголовка обязательно, даже если нет никаких данных. Листы с данными должны быть скрыты от пользователя во избежание подобных действий, а добавление или удаление должно производиться программно или, по крайней мере, корректно. Я не скрывал листы специально, что бы Вам было проще разобраться. Если есть еще какие – то вопросы, постараюсь ответить.  
Операторы на подходе.
 
Постарался учесть все Ваши пожелания. Извините, что мало комментариев, там и так более 600 строк кода (подробные комментарии выльются еще в 200-300 строк). Что еще надо сделать? Сложности со слиянием файлов еще остались? Попробовать упростить ввод данных на нижних складах?
 
{quote}{login=LAD}{date=27.01.2009 02:17}{thema=}{post}наличие строки заголовка обязательно, даже если нет никаких данных.{/post}{/quote}  
 
Насчет строки заголовка я и сам понял, она оставалась на месте, я удалял только данные, и если не оставить ни одной строки с данными происходила ошибка. Сейчас посмотрю Ваш файл, спасибо огромное.
 
LAD, затащил запросом всю базу по вывозке, появились ошибки, например за январь 2009 если выбрать Все, то в Качканар итоговые цифры встают, а по числам нет. За 2008 г. вроде такого нет. И у операторов за 2009 г. такая же штука на подъемке и разгрузке.
 
Лесник, насчет пустых таблиц Вы правы, я не учел особенность версий Excel и кроме этого не правильно формируется список лет. Это исправлю.  
Про ошибки в расчетах за 2009 год разговор особый. Пожалуйста, объясните как Вы “затаскиваете базу”. Дело в том, что в результате объединения на листе Вывозка даты со строки 2797 по строку 4978 хранились в текстовом формате, строки 2796, 4117 – 4118, 4979 – 10001 были заполнены нулями. После приведения дат к нужному формату (дата) все считается правильно.  
На листе Подъемка данные хранятся  в виде ссылок на ячейки другой книги, это неоправданно увеличивает размер файла и.
 
Добрый день, Lad!  
Насчет "затаскивания базы". Я здесь на форуме нашел макрос, который объединяет в один файл данные из любого количества файлов Excel, находяшихся в одной с ним папке и имеющих листы с одним именем и одинаковые шапки таблиц. Вот этот макрос:  
Sub CollectAllClients()  
Dim BazaWb As Workbook 'текущая книга (общий файл)  
Dim BazaSht As Worksheet 'лист База покупателей в общем файле  
Dim iTempWb As Workbook 'по-очерёдно открываемый файл  
Dim iTempSht As Worksheet 'лист База покупателей в по-очерёдно открываемом файле  
Dim iTempFileName As String 'имя по-очерёдно открываемого файла  
Dim iRowIndex As Long 'количество заполненных строк в общем файле  
Dim iPath As String 'путь к папке, где лежат все файлы  
Dim iLastRowBazaA As Long 'последняя заполненная строка в общем файле в столбце A  
Dim iLastRowBazaB As Long 'последняя заполненная строка в общем файле в столбце B, т.к. могут различаться  
Dim iLastRowTempWbA As Long 'последняя заполненная строка в по-очерёдно открываемом файле в столбце A  
Dim iLastRowTempWbB As Long 'последняя заполненная строка в по-очерёдно открываемом файле в столбце B, т.к. могут различаться  
Dim iNumFiles As Long 'количество открываемых файлов  
 
   With Application  
       .ScreenUpdating = False  
       .DisplayAlerts = False  
       .Calculation = xlManual  
       .EnableEvents = False '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
       '.EnableEvents = True  
       Set BazaWb = ThisWorkbook  
       Set BazaSht = BazaWb.Sheets("Вывозка")  
       iPath = BazaWb.Path & "\"  
       iTempFileName = Dir(iPath & "*.xls")  
       Do While iTempFileName <> ""  
          If iTempFileName = BazaWb.Name Then GoTo iNext:  
          With .Workbooks.Open _  
               (Filename:=iPath & iTempFileName, UpdateLinks:=False, ReadOnly:=True)  
               iNumFiles = iNumFiles + 1  
               'Рабочая книга не должна быть защищена паролем  
               With .Worksheets("Вывозка") '.UsedRange  
                    iLastRowTempWbA = .Cells(Rows.Count, 1).End(xlUp).Row  
                    iLastRowTempWbB = .Cells(Rows.Count, 2).End(xlUp).Row  
                    iLastRowTempWbA = IIf(iLastRowTempWbA >= iLastRowTempWbB, iLastRowTempWbA, iLastRowTempWbB)  
                    iLastRowBazaA = BazaSht.Cells(Rows.Count, 1).End(xlUp).Row  
                    iLastRowBazaB = BazaSht.Cells(Rows.Count, 2).End(xlUp).Row  
                    iLastRowBazaA = IIf(iLastRowBazaA >= iLastRowBazaB, iLastRowBazaA, iLastRowBazaB) + 1  
                    .Range(Cells(2, 1), Cells(iLastRowTempWbA, 27)).Copy Destination:=BazaSht.Cells(iLastRowBazaA, 1)  
               End With  
               .Close saveChanges:=False  
          End With  
iNext:  
          iTempFileName = Dir  
       Loop  
       .Calculation = xlAutomatic  
       .DisplayAlerts = True  
       .ScreenUpdating = True  
       .EnableEvents = True  
   End With  
     
   End Sub  
В папке Вывозка у меня 3 файла- Вывозка_Тура, Вывозка_Качканар и третий файл с макросами, которые выполняются при открытии файла (первый макрос очищает диапазон ячеек A2:R1001, т.к.  CollectAllClients добавляет при каждом выполнении данные начиная с первой свободной строки, т.е не заменяет данные, а каждый раз прибавляет, затем выполняется CollectAllClients и потом еще один, который заменяет пустые значения в ячейках на ноли в диапазоне A2:R1001, т.к без этого потом запрос не берет данные если в каком то столбце в начале стоят пустые ячейки. Где то выше я уже спрашивал об этом.) Этот диапазон A2:R1001 именован как ОбщаяВывозка. Потом уже в Вашем файле на листе Вывозка я создаю запрос из именованого диапазона ОбщаяВывозка, который работает при открытии файла (когда Вам отправлял, отключил автообновление при открытии) В файлах Вывозка_Тура и Вывозка_Качканар по 2 листа - Вывозка (шапки таблиц такие же как и в Вашем файле) и подъемка (шапки тоже одинаковые). Можно было бы Ваш файл поместить в папку Вывозка, тогда не нужен бы был промежуточный файл для объединения данных, но я использую эту же базу еще для одного файла, уже для себя, где идет анализ использования лесфонда по лесорубочным билетам, а макрос работает со всеми xls файлами в папке и тогда в нем будут собираться данные уже из 3 файлов. Хотя только что подумал, что можно же в Вашем файле диапазон именовать и из него данные получать в свой файл Лесфонд, точно, так и сделаю наверно.  
Насчет ссылок на ячейки из другой книги - пробовал скопировать макрос CollectAllClients под другим именем CollectAllClientsPod в другой модуль этой же книги изменив листы с Вывозка на Подъемка, чего то не получилось, ни один не работает после этого, может его как продолжение первого нужно сделать? Поэтому и вставил пока ссылки. Просто эти 2 дня в лесу работали, каждый день километров по 5-6 по снегу по колено лазали, домой прихожу - ноги дрожат, некогда было поэкспериментировать, вот в выходные посижу дома, потыкаюсь. Лист ремонт оставить нужно в Вашем файле для прямого заполнения, потому что их только в Туре заполняют, в Туре основная база предприятия и базирование лесовозов, где они и ремонтируются.  
Насчет текстового формата ячеек - черт его знает почему так, проверю в исходных файлах, хотя при заполнении это бы наверно выявилось.  
А вообще, LAD, Вы по жизни чем занимаетесь? Вы на меня столько времени убиваете, даже неудобно уже Вас эксплуатировать. А если есть интерес я могу Вам предложить написать программку для ведения Учета Лесного Фонда. Его ежегодно делают все лесхозы (сейчас лесничества называются)- это госучреждения, а с этого года похоже заставят делать и арендаторов (лесозаготовителей). Львиная доля этой работы насколько я знаю производится инженерами вручную, а там куча увязок и форм, мы раньше вчетвером недели 2 сидели с этими бумагами. Я 2 года назад в лесхозе директором работал, технологию всех расчетов могу объяснить. Если все получится в принципе такую программу можно наверно будет продавать лесхозам и арендаторам (если есть такой опыт - продавать), т.к. не слышал, чтобы кто то пользовался в этом деле программами. В областном управлении Лесного хозяйства есть программа московская, но она еще в DOS сделана и никаких расчетов не производит - только ручная набивка всех данных в нее (которые нужно вручную сначала получить) для контроля всех увязок и объединения всех данных по Субъекту РФ в единую базу данных, которая затем сдается в Москве по всей России.  
Я позднее сделал в Excel формулами такой файл, но он только под конкретный наш лесхоз был адаптирован, а с 1.01.08 лесхозы укрупняли, добавились новые лесничества и районы, так что сейчас он не актуален, хотя в свое время очень выручал нашего инженера лесного хозяйства, она одна делала этот учет за несколько дней, причем не нужно ни над чем задумываться особо, просто внимательно внести все изменеия за год и распечатать готовые формы.
Страницы: 1 2 След.
Читают тему
Наверх