Последняя цена....

Обсуждение вопросов по использованию Excel, Access и других программ оптимизирующих работу закупщика
PM
Пользователь
Пользователь
Сообщений: 101
Зарегистрирован: 05 ноя 2007 03:00
Откуда: Spb

Последняя цена....

Сообщение PM » 26 июн 2009 10:16

Дамы и Господа,
Есть сводная таблица экселе:
В строках товар, в столбцах даты, в данных цена...грубо говоря, видно, какой товар по какой цене продавался в какой день:

Как бы мне добавить в самом конце такой столбик, в который бы подставлялась последняя цена, по котороый продавался тот или иной товар. Иными словами подставить ближайшую слева непустую ячейку.
Поможете?)

Реклама
RazVal
Гуру
Гуру
Сообщений: 895
Зарегистрирован: 28 май 2015 17:58
Имя: Валерий
Фамилия: Разгуляев
Должность: эксперт по управлению величиной запасов и оптимизации затрат
Откуда: http://upravlenie-zapasami.ru/

Re: Последняя цена....

Сообщение RazVal » 26 июн 2009 11:15

PM писал(а):Есть сводная таблица экселе
Обращение к ячейкам сводной таблицы, сформированной Excel-ем, - своеобразное, поэтому если будут проблемы с функцией из вложения, советую полностью скопировать сводную таблицу и вставить как значения (можно на то же место).

PM писал(а):В строках товар, в столбцах даты, в данных цена... видно, какой товар по какой цене продавался в какой день:
Как добавить в самом конце такой столбик, в который бы подставлялась последняя цена, по котороый продавался тот или иной товар. Иными словами подставить ближайшую слева непустую ячейку?
Во вложении файлик с самописной VBA-функцией LastVal, которая для заданного диапазона (выделено жёлтым) выдаёт значение из последней не пустой ячейки (выделено малиновым). Вам надо только запустить этот файл у себя в Excel, поверить, что никаких вирусов там нет, и в последнем столбце вашей сводной таблицы написать: =LastVal([диапазон, по которому вы хотите найти значение последней не пустой ячейки])
Так как функция самая простейшая, то она не отрабатывает случаи, когда в ячейках вместо цифр вы пишите буквы (пробелы, апострофы, точки и запятые, не являющиеся разделителем знака - это всё тоже буквы) - в таком случае функция выводит: #ЗНАЧ! - ищите, где у вас ошибка в водимом диапазоне данных.
У вас нет необходимых прав для просмотра вложений в этом сообщении.

Аватар пользователя
sf13

Сообщение sf13 » 26 июн 2009 11:39

А без макросов.... по-простому:
=ПРАВСИМВ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(СЦЕПИТЬ(C2;" ";D2;" ";E2;" ";F2;" ";G2;" ";H2;" ";I2));" ";" ");8)+0

При расположении данных в столбцах с C по I со второй строки
пишем формулу по второй строке и копируем вниз.

Максимальное число столбцов данных, увы, при этом фиксируется, но какая, собственно, разница...

ДЕМО в файле.
У вас нет необходимых прав для просмотра вложений в этом сообщении.

Аватар пользователя
sf13

Сообщение sf13 » 26 июн 2009 11:42

Ну вот, умный редактор форума тоже сжал пробелы...
В формуле перед восьмёркой в кавычках 10 пробелов, а не один. В общем, смотрите файл.

PM
Пользователь
Пользователь
Сообщений: 101
Зарегистрирован: 05 ноя 2007 03:00
Откуда: Spb

Сообщение PM » 26 июн 2009 11:50

Понимаю, что работает, но не понимаю как)) И потом...это хорошо на отрезке в неделю..а если период 5 лет...)
Все же, если будет время, опишите в личку, как все это работает. Зачем пробелы, зачем +0..не понятно. Понял, что мы превращаем массив данных в текстовую строку, потом как то по признаку откидываем все, кроме хвоста..и хвост оставляем.

Аватар пользователя
sf13

Сообщение sf13 » 26 июн 2009 12:49

PM писал(а):Понимаю, что работает, но не понимаю как)) И потом...это хорошо на отрезке в неделю..а если период 5 лет...)
Все же, если будет время, опишите в личку, как все это работает. Зачем пробелы, зачем +0..не понятно. Понял, что мы превращаем массив данных в текстовую строку, потом как то по признаку откидываем все, кроме хвоста..и хвост оставляем.


1. На произвольном массиве данных вероятно сработает без проблем макрос Валерия. (Судя по тому, что он ещё ни разу не скачан, Вы его не ещё не смотрели).

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

3. В формуле использованы последовательно 4 стандартных функции обработки текста в Excel.
1) Сцепили все данные в одну строку через пробел
2) Сжали пробелы, чтобы пробелы за последней ценой убрались
3) заменили каждый пробел на десяток (чтобы можно было "откусить" последнюю правую цену, не боясь зацепить предпоследнюю)
4) "Откусили" последнюю цену
5) Прибавили ноль, чтобы текст стал числом.

Можно всё это сделать последовательно в 5-ти ячейках.
Так обычно и делается, а после проверки работоспособности собирается в одну фопмулу.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Сообщение Роман Бодряков » 26 июн 2009 17:21

Я чуть по другому сделал.

В строку надо добавить последнюю дату. Она нормально в сводной определится как максимальное значение по полю дата.

Потом функцией поиск позиции в таблице - текущая строка и последняя дата


Второй вариант. В аксессе есть функции первая и последняя запись в группирующих запросах

третий вариант. В опорной таблице можно отсортировать по товару и дате, потом "если" найти последнюю запись по товару и проставить в отдельном поле единички. По ним потом вывести последние цены в сводную

Вообщем вариантов масса и на любой вкус. Но они все сподвыпердвертом :-):
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

PM
Пользователь
Пользователь
Сообщений: 101
Зарегистрирован: 05 ноя 2007 03:00
Откуда: Spb

Сообщение PM » 27 июн 2009 00:02

Спасибо всем за советы. Валерию особое спасибо за макрос, в ВБА я никогда не сомневался) Будем дерзать)


Вернуться в «Программы для закупщиков»

Кто сейчас на форуме

Количество пользователей, которые сейчас просматривают этот форум: CommonCrawl [Bot] и 0 гостей