Евгений Смирнов, - Макрос работает. Огромная благодарность!


aka_kd, Вам так-же огромная благодарность, но макрос на файле примере работает, а в рабочей среде - выдаёт ошибку, скрины привёл ниже:  

Добрый день.

Есть у меня макрос "Redesigner" который преобразовывает двумерную таблицу (вкладка "база") в одномерный массив (вкладка "результат").

Принцип макроса прост: Выделяем нужный двумерный массив, запускаем макрос, вводим в окошках количество строк с заголовками ( в примере "1") и количество столбцов с заголовками (в примере "1") - и макрос преобразовывает данные.

Всё бы круто, но макрос преобразовывает все данные, и пустые и не пустые ( вкладка "результат", ячейки С1, С2, С3, С4, С6, С7 и т.д., а нужно что бы пустые значения игнорировал. Вариант с преобразованием и удалением пустых после преобразования - не подходит. т.к. ексель не позволяет вставить такое количество строк и макрос выдаёт ошибку: в оригинальном рабочем файле в двумерной таблице 6000 столбцов и 450 строк,  что на выходе даст 2.7 миллиона строк, чего ексель не поддерживает, хотя фактических значений которые должны остаться в одномерном массиве - около 15000.

На вкладке "желаемый результат" показал как макрос должен работать.

Буду благодарен за помощь.  

Благодарю, но с формулой у меня комп ложиться ))) 11-12 милионов формул - чуть многовато.

Добрый день уважаемые знатоки.

Столкнулся с такой проблемой:

Есть в колонке "А" список магазинов, в строке "1" название товаров, в строке "2" количество товара.   Нужно распределить количество товара по магазинам, в случайном порядке не последовательно.

При этом желательно пользоваться логикой:

1) если товара меньше чем магазинов - распределить по 1 товару в случайные магазины не последовательно.
2) Если товаров больше чем магазинов - распределить на все магазины с максимально возможным количеством ( на вкладке желаемый результат отобразил)


Загвоздка в том, что количество товаров изо дня в день меняется ( в среднем около 450), и количество магазинов так же меняется постоянно их количество около 25000), соответственно обрабатывать это руками или формулами - мягко говоря проблемно.

Возможно кто-то сможет помочь?  

Обновляю файл примера

Добрый день.  Поднимаю тему )))  

Самое странное, что в ней были ещё сообщения, но кудато пропали. Тут  мне выкладывали рабочий макрос, который делал поставленную задачу.  Видимо сообщения удалили. Можно как то восстановить те сообщения что были удалены в данной теме?  

Добрый день. Есть файл прогнозов, в котором коловертнадцать строк и столбцов (в примере упрощённый вариант), нужно вот что:

На уровне автоматики (как действует условное форматирование) подсвечивать в колонке Прогноз топ 5 наибольших значение внутри заказчика и названия акции.
Как пример выделил вручную красным по клиенту 1.

Как это возможно автоматизировать ? Очень надеюсь на вашу помощь.
Заранее спасибо  

Stics, Спасибо огромное. Всё работает.  

Добрый день.


Нужно найти среднее значение за 7 дней(А7-G7), исключая пиковый всплески (A1). При этом ориентируясь что пиковым всплеском считается трехкратное значение ячейки Т7.

В идеале, нужно что бы считало таким алгоритмом:

Если значение какой-либо ячейки в диапазоне в 3 раза больше чем ячейка Т7 то вместо этой ячейки считает значение Т7, и исходя из этого высчитывает среднее значение. Т.е. ячейки В7-G7 берёт фактические данные к ним добавлеет значение Т7, и из этой суммы считает среднее.

п.с. Диапазон будет значительно больше чем 7 ячеек. Может доходить до нескольких тысяч. Так-же по строкам, до 5 тысяч строк. Менять структуру файла, и добавлять кучу столбцов/строк нельзя. максимум 1-2 столбца.

п.с.2.  Пытался сделать через =СРЗНАЧЕСЛИ(A7:G7;"<(T7*3)"), но как не пишу аргумент - он не работает. Работает только статичный аргумент. А мне нужно вот так, чтобы именно на ячейку ссылалось.

Заранее всем спасибо.

С Уважением Антон

проще? попробую.

Данные в столбце L рассчитваются буз учёта предыдущих значений. Грубо говоря если в столбце А и в столшбце С и в столбце I данные повторяются, то формула в столбце Л должна принимать в рассчёт что уже были недопоставки по коду и добавлять их к формуле.
Тоесть значение ячейки Л7 должно быть не 3.1 а 2,76 ( 2.76=L7-СУММ(L3:L6) ). как это прописать внутрь формулы? или доп столбцец сделать как то?

Добрвый день.

Столкнулся с проблемой: Есть дата начала акции и дата окончания акции. Очень часто эти даты попадают в разный месяц года(14,02,17-10,03,17). Поставили мне задачу определить к какому месяцу относить период по условию: в какой месяц попадает больше дней - такой месяц и ставить, и ставить его числом. Периодов очень много, и делать это руками нереально. Подскажите на примере файла каими методами это можно сделать?  

Или я неправильно что то делаю, или  что то неправильно делаю ((((   Но у меня не работает...    формула для ячейки G3:

=ЕСЛИОШИБКА(ВПР($A3;Лист2!$A$3:$S$11;ИНДЕКС(Лист2!$A$1:$S$11;ПОИСКПОЗ(G$2;Лист2!$A$1:$S$1);ПОИСКПОЗ($E$1;Лист2!$A$2:$S$2);););)

п.с. Я видимо просто не понимаю последовательностей, что за чем должно идти

Спасибо. Это отлично и удобно, если бы не одно "но": на листе 2 столбцы "Заказано", "Доставлено","Урезано" переодически меняются местами, и иногда может появлятся 4-й и 5-й столбец, а бывает и такое что по групе 2.2 и 2.5 столбцы стоят в разной последовательности.  по этому и пытался внести услови по "Урезано".

Добрый день.

Сталкнулся с такой проблемой: Есть данные в таблице, в которой постоянно меняются местами столбцы, какие-то пропадают, потом появляются (лист 2).
Нужно постоянно вытягивать данные в таблицу чёткого формата (лист 1).

Пытаюсь вытянуть это всё через массивы. но проблема вот в чём:
Через связку "=СУММ(ЕСЛИ((Лист2!$E$1:$S$1=Лист3!G$2)*(Лист2!$E$2:$S$2=Лист3!$E$1)*(Лист2!$A$3:$A$11=Лист3!$A3);Лист2!$E$3:$S$11;0))" - всё получается, но только ексель выдаёт "Бракує ресурсів для обчислення". (Лист 3) (суммарное количество формул массива - порядка 3000).

А через связку Индекс+Поискпоз - не получается. Всё криво работает. К тому-же не могу понять как зделать так, что бы данная связка работала по 2 горизонтальным условиям и 1 вертикальному условию, в случае примера: диапазон "лист 2 строка1" условие - лист один ячейки G2-N2, и диапазон 2: "Лист 2 строка 2" условие "лист 1 ячейка  Е1", Диапазон 3 "Лист 2 ячейки "А3-А11", условие лист 1 ячейки "А3-А11".

Подскажите где я ошибся в связке для поиска с 1-м горизонтальному условию, и как добавить 2-е горизонтальное условие?  

Добрый день.

Есть товар с разными сроками, но одним и тем-же кодом. В данном файле ,на вкладке "ок", указан только товар у которого остался малый срок годности, но это-го же товара есть много с хорошим сроком. Но часто бывает что логисты дают одни данные(столбец Д) а по факту есть иное количество(вкладка Сток, столбец Ф), но на вкладке нет разделения по срокам. Нужна формула, которая будет в столбце Е(остаток) искать значение с квладки "Сток" столбца Ф и делать следующее:  если значение с вкладки "Сток" больше суммарного значения кода с вкладки "ОК" то будет подставлять соответствующее сроку годности количество, как в столбце "Д". А если меньше чем суммарно по коду, то максимум в плохой срок, что осталось в следующий, и если останется то в самый дальний срок. понятнее можно увидеть в примере. в примере только код 555 есть на обоих вкладках.
Сколько не пытался написать что то - фигня получается.
Подскажите пожалуйста.

Bema, Огромное Спасибо. Так всё работает.

Тему прошу закрыть.  

Добрый день всем.

Столкнулся с нюансом условного форматирования:
Если я на ячейку В1  ставлю уф с такими параметрами:  Если больше чем А1 то цвет красный, Если меньше А1 то цвет зелёный.
Вопрос в том, что при протягивании правила на столбец В2 работает корректно (ориентируется на А2), а всё что дальше - ориентируется только на А2 (а не на а3, а4 и т.д) При создании правила значёк $ убираю, но ситуация остаётся такой же.

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

Зарание всем спасибо.  

Добрый вечер. Не могу понять как сделать такую весч:

Есть стобец А в котром данные в днях, и столбец В в котором данные в внутренних единицах. Нужно просуммировать данные из столбца В но только те, которые соответствуют критерию ячейка слева (В данном случае А) - больше 45.

Подскажите пожалуйста. надеюсь на вашу помощь.
Пример во вложении.
Заранее спасибо.  

 Excel Angel, благодарю за вариант. но я уже  сделал это через функцию СУММЕСЛИМН. С её помощью гораздо меньше изменений в файле.

Прошк тему закрыть. У руководства уже 5 раз изменились требования

Добрый день.

Есть файл, в котором много населённых  пунктов, и сбор огромного количества данных по ним (лист 1 - данные идут столбцах, удалил за ненужностью), которые считаются в критерии.
Нужно вывести данные в формат, как в листе2 по каждому городу, выбирая данные по строке 15.  При этом нужно выводить за 30 дней и за 60 дней а иногда и за 90, в зависимости от параметра на листе 1 ячейка D1.

Кроме как макросом - я не понимаю как это можно сделать. А в макросах я дуб дубом...
Возможно вы сможете подсказать?
Заранее благодарен.  

Добрый день.

Есть файл, с огромным набором данных на одном листе, и выбор нужных данных на другом листе.
На первом листе на одну позицию товара несколько строк, и в каждой строке различный остаток товара, в зависимости от остатка высчитывается результат по товару (Столбец L). На второй лист нужно вытянуть результат, соответсвующий строке кода с максимальным остатком. Не могу сообразить как это сделать.

Подскажите пожалуйста.
Спасибо.  

JayBhagavan, Спасибо огромное.  Работает ))
vikttur, Прошу прощения, но что значит "летучие" ?  

Добрый день.

Есть на одном листе 2 таблицы: 1 - таблица с последовательными датами и результатами. 2- выборочные даты.
НЕ могу придумать формулу, что бы она искала дату из второй таблицы( ячейка F9) в колонке А, и если находит дату то в колонке В сумирует 60 ячеек вверх от найденной даты.

Надеюсь на помощь.

п.с. перестроить последовательность построения дат - не могу. должно остатся именно так как в примере.  

Dima S,  Спасибо. Немного переработал вашу формулу, добавив 2-й параметр отбора по внутреннему коду. и всё получилось ))

Добрый день.

Есть файл, в котором указываются зарезервированный товар, и выборка товара по резервам. Задача вывести в сводную данные так что бы получить % выборки резерва по коду документа.  То есть нужно увидеть что по данному документу, выбрали столько то % зарезервированного товара. Задача не сложная, если бы не одно НО:

Данные вытягиваются из системы в ексель не совсем корректно (Проблемные моменты выделены цветом).  Есть такие моменты, когда резерв делает одна корпорация и несколькими покупателями, а потом каждый покупатель выбирает товар отдельно. В итоге на корпорацию идёт 3 покупателя, и на каждого система делает отдельную строку, с общей сумой заказа, но с личной для конкретного покупателя цифрой выборки. Как итог по одному документу :

Корпорация	Клиент	Резерв	Выбрано   резерва	остаток   резерва
3	4	455	48	407
3	4	455	27	428
3	5	455	312	143

При выводе в сводную идёт некорректная цифра резерва. Корректная цифра выборки и некорректная цифра остатков и выведенный % не корректен.

Пробовал сделать через среднее значение столбца Резерв, но сумму по полю выбрано резерва, а дальше через вычисляемое поле (Выбрано резерва/резерв) – всё равно цифра % не корректная выходит. (лист 2 сводная таблица Поле 11). Подскажите как решить данную проблему.
Заранее благодарен за помощь.

П.с. В процессе понял что среднее по столбцу резерв нельзя выводить, ибо получится полный бред. Вернул сумму, что так-же не корректно.
П.с. 2 – исправить программу, что бы она корректно выводила данные – пока что не могут.

 

JayBhagavan - Огромное спасибо!!! Вы очень помогли. Буду теперь адаптировать его под свой файл.  

Добрый вечер.

Столкнулся с такой проблемой, и боюсь кроме как макросом решить её не получится ,а я в них дуб-дубом, только начинаю изучать (2-й день )
Есть таблица, в которой условия заданы и по горизонтали и по вертикали. В примере будет понятней. Нужно вытащить из неё только определённые данный, но тянуть по числовому значению продаж (если > 0 то значение нужно) на другой лист. В примере так-же более наглядно видно.

Грубо говоря,  в моём понимании макрос должен работать так:
Если значение в ячейке из таблицы (Р6:W13) больше нуля, то макрос вставляет это значение (к примеру Р7) в ячейку G3 на новом листе, а в ячейки A3 - значение P3, в B3 значение O5 и дальше значения ячеек C7-F7 И так все значения более нуля из заданного диапазона ?

Понимаю принцип работы макроса, но написать сам пока не в состоянии.  Подскажите пожалуйста.
Заранее благодарю.

п.с. В файле цветами выделены которые надо вытягивать.  

Спасибо, но это не совсем то. Видимо я не правильно описал проблему.

Грубо говоря мне надо что бы я транспортировал 2 строки (код товара внуренний и строку с названием товара(товар 1...)), задал название столбцов (как во втором листе) а все остальные данные тянулись от этих параметров.

т.е. я задаю  "код товара", "товар 1" - а "продавец", "филиал", "покупатель", "количество" - подтягивалось по коду товару. при этом возникает проблема, что один товар купило несколько продавцов, и надо на каждого выборка.

т.е.2. выходит что нужно несколько строк (точное количество не известно) на каждый товар.


П.с. Либо можно тянуть по филиалу продавца (как в 3-м листе файла ниже), а нужную сортировку я сделаю потом. а после поменяю местами столбцы.

PS2. А возможно ли это сделать макросом такого принцыпа действия:

Если значение в ячейке из таблицы (O6:W13) больше нуля, то макрос в оставляет это значение в ячейку А1 на новом листе, а в ячейки В1 и дальше значения ячеек B6-G6 и О3-О5. И так все значения более нуля, в столбец А ?  

Добрый день всем. Возможно не правильно назвал тему, прошу не ругать - только учусь.


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

Проблема в том, что в файле огромное количество данных (более 20к строк), и изменять его нельзя ( в примере упрощённый вариант) . Пробовал сводной таблицей делать, но не получается. Вариант с ВПР слишком много времени занимает.

Подскажите пожалуйста в каком направлении двигать мыслю.
Спасибо.

п.с.  Excel 2010