Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

12 Ноябрь 2019, 14:50 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Здесь можно поблагодарить участников форума Улыбка
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1 2 3 4 »   Вниз
  Печать  
Автор Тема: Подсчёт остатка чисел  (Прочитано 1724 раз)
0 Пользователей и 1 Гость смотрят эту тему.
Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Стартовое сообщение: 15 Октябрь 2019, 11:22 »

Доброго времени суток. К сожалению поиск по форуму/гегле/ютубу ничего не дал. Так же не совсем понимаю как правильно сформулировать запрос.
Задача следующая:
В первом столбце строки в которых содержится номер/наименование ящика, например: ящик деревянный, ящик пластмассовый
Все последуюище столбцы в первой строке имеют наименования содержимого ящика, например: апельсины, бананы, груши
В рабочих ячейках вручную указывается количество загруженных фруктов в определённый ящик.
Каждый день из ящиков достаются фрукты, их количество известно и вносится в аналогичную таблицу с пустыми ячейками.
Внимание вопрос: как вычесть съеденные фрукты из остатка в ящиках что бы в первой таблице ячейки стали отображать остаток?

P.S. таблица с остатками должна обновляется ежедневно, таблица со съеденным создаётся только для вычета из остатков.

Благодарю за внимание.


* calc.png (26.61 Кб, 899x298 - просмотрено 11 раз.)
« Последнее редактирование: 15 Октябрь 2019, 13:46 от Mary1984 » Записан
kompilainenn
Мастер
*****
Offline Offline

Сообщений: 2 760



« Ответ #1: 15 Октябрь 2019, 13:43 »

Файл, образец с данными, приложите
Записан

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Ответ #2: 15 Октябрь 2019, 13:49 »

На картинке пример. Всё до боли примитивно но у меня возникли трудности т.к. работаю с таблицами совсем недавно. Надеюсь здесь не нужны сверхсложные формулы.
Добавлю лишь то что наименования строк и столбцов могут менять своё расположение, т.е. по принципу vlookup значения должны находиться и подставляться независимо от конкретных координат ячейки.


* calc.png (26.61 Кб, 899x298 - просмотрено 6 раз.)
« Последнее редактирование: 15 Октябрь 2019, 13:56 от Mary1984 » Записан
Yakov
Администратор
**
Offline Offline

Сообщений: 2 443


WWW
« Ответ #3: 15 Октябрь 2019, 13:58 »

Так можно и файл с таблицей приложить, а не только скриншот таблицы...
Записан

Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Ответ #4: 15 Октябрь 2019, 14:13 »

Файл, образец с данными, приложите
Так можно и файл с таблицей приложить, а не только скриншот таблицы...

* template.ods (13.72 Кб - загружено 8 раз.)
Записан
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 731


« Ответ #5: 15 Октябрь 2019, 17:12 »

В C15 и протянуть вниз и вправо
Код:
=HLOOKUP(C$14;$C$6:$E$9;MATCH($B15;$B$7:$B$9;0)+1;0)-HLOOKUP(C$14;$H$6:$J$9;MATCH($B15;$G$7:$G$9;0)+1;0)
Записан
Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Ответ #6: 15 Октябрь 2019, 17:24 »

=HLOOKUP(C$14;$C$6:$E$9;MATCH($B15;$B$7:$B$9;0)+1;0)-HLOOKUP(C$14;$H$6:$J$9;MATCH($B15;$G$7:$G$9;0)+1;0)
СПАСИБО НАЧАЛЬНИК!
Если не сложно, будьте добры, расшифруйте формулу человеческим языком, что откуда и куда) вижу что нет VLOOKUP только HLOOKUP

Очень простое решение и в то же время много ручной работы.Необходимо создвать ещё одну таблицу. Возможно эти расчёты провести в первой таблице? то есть в целевой. нужно создать не отдельную, а рассчитать в текущей таблице..
« Последнее редактирование: 15 Октябрь 2019, 17:36 от Mary1984 » Записан
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 731


« Ответ #7: 15 Октябрь 2019, 17:47 »

Функции Hlookup и Vlookup похожи, одна ищет в строках вторая в столбцах. 

HLOOKUP(C$14;$C$6:$E$9;MATCH($B15;$B$7:$B$9;0)+1;0) - ищет содержимое C$14 в диапазоне $C$6:$E$9, и выводит значение из строки найденной MATCH($B15;$B$7:$B$9;0)+1

Где в текущей выводить результат? Если под каждым ящиком, то не будет протягивание работать
Записан
Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Ответ #8: 15 Октябрь 2019, 18:00 »

Где в текущей выводить результат? Если под каждым ящиком, то не будет протягивание работать
Ну моя таблица ниже - для примера.
Допустим у меня база остатков и её мне нужно переодически обновлять. Возможно ли, вычесть "съеденное" из остатков не создавая новую таблицу?
Так же обращу внимание на то что каждый раз придётся копировать все остатки и вставлять обычным текстом что бы провести следующие расчёты Вашим способом.

Если не ошибаюсь, это будет очень громоздкая конструкция и проще реализовать макрос для внесения нужных значений в остатки?
« Последнее редактирование: 15 Октябрь 2019, 18:05 от Mary1984 » Записан
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 731


« Ответ #9: 15 Октябрь 2019, 21:18 »

Если менять данные по остаткам в целевой таблице, то только макросом.
Записан
Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Ответ #10: 15 Октябрь 2019, 22:57 »

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


=HLOOKUP(B$1,Sheet2.$B$1:$Z$100,MATCH($A2,Sheet2.$A$2:$A$100,0)+1,0)-HLOOKUP(B$1,Sheet3.$B$1:$Z$100,MATCH($A2,Sheet3.$A$2:$A$100,0)+1,0)
Данные для B1 извлекаются из дипазонаа Sheet2.$B$1:$Z$100 и сравнивается с диапазоном MATCH($A2,Sheet2.$A$2:$A$100,0)+1,0) для определения точной ячейки, по двум критериям, которую нужно подставить.
То же самое делается для подтягивания значений из остатков и выпоняется обычное арефметическое вычетание.
В конце двух формул я вижу "+1,0" 0 это "ложь" - точное совпадение по критерию поиска. а вот +1 - индекс. Какую роль здесь играет индекс +1 если поиск проводиться по всем ячейкам в документе? В примитивноем варианте ВПР этот индекс указывает какой столбец подставлять в качестве значений, а здесь я немного непонимаю) Формула у меня правильно составлена? Корректно работать должно? на 100 строк и на столбцы от A до Z ?!
Разжуйте пожалуйста
« Последнее редактирование: 15 Октябрь 2019, 22:59 от Mary1984 » Записан
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 731


« Ответ #11: 15 Октябрь 2019, 23:28 »

На предыдущем примере
=HLOOKUP(C$14;$C$6:$E$9;MATCH($B15;$B$7:$B$9;0)+1;0)

C$14 что ищем
$C$6:$E$9 массив где (точнее ищем только в с6:e6
MATCH($B15;$B$7:$B$9;0)+1 ищем строку которая соответствует нашему ящику, т.е в массиве $B$7:$B$9 ищем имя ящика $B15. 0 - ищем точное совпадение, +1 смещение так как индекс нужной строки должен быть от 2 до 4, а с массивом $B$7:$B$9 MATCH вернет от 1 до 3 (можно задать массив $B$6:$B$9, тогда +1 не нужен
0 - HLOOKUP ищет точное совпадение
« Последнее редактирование: 15 Октябрь 2019, 23:38 от Bigor » Записан
Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Ответ #12: 16 Октябрь 2019, 02:10 »

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

* template2.ods (10.84 Кб - загружено 6 раз.)
« Последнее редактирование: 16 Октябрь 2019, 02:51 от Mary1984 » Записан
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 731


« Ответ #13: 16 Октябрь 2019, 13:05 »

Объединенные ячейки - зло. Для людей красиво, для формул головная боль.
Поэтому разбил их, и трижды написал яблоки итп.
На листе результат в b3 копируете это:
Код:
=SUMPRODUCT((остаток.$A$3:$A$5=$A3)*(остаток.$B$3:$J$5)*(остаток.$B$1:$J$1=B$1)*(остаток.$B$2:$J$2=B$2))-SUMPRODUCT((съеденное.$A$3:$A$5=$A3)*(съеденное.$B$3:$J$5)*(съеденное.$B$1:$J$1=B$1)*(съеденное.$B$2:$J$2=B$2))
и протягиваете вниз и вправо. С вашим примером были расхождения, так что проверьте внимательно.
Записан
Mary1984
Участник
**
Offline Offline

Сообщений: 28


« Ответ #14: 16 Октябрь 2019, 19:28 »

Поэтому разбил их, и трижды написал яблоки итп.
ну теперь вобще ничего непонятно( куда делись HLOOKUP?

Прикрепляю существующую таблицу. Для наглядности в ней всё разнообразие фруктов и их видов. Как видите, градации вариантов совершенно разные, есть фркуты лишь с одним видом к примеру. Эту таблица получается после сведения данных из csv файла с большим количеством строк каждая из которых имеет значение фрукта его вида и номер ящика, а так же количество фруктов  и такая строка может иметь например 2 и больше количества фруктов в одной строке... Возможно есть смысл использовать исходный csv для таких расчётов?

* template3.ods (19.4 Кб - загружено 2 раз.)
« Последнее редактирование: 16 Октябрь 2019, 19:49 от Mary1984 » Записан
Страниц: 1 2 3 4 »   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!