kaufman
Участник

Offline
Сообщений: 6
|
Здравствуйте. Просьба не судить строго, ибо возможно для кого-то данная задача окажется очень простой. Вот суть задачи: есть два столбца "А" и "В", столбец "А" содержит даты и имеет формат "дата", столбец "В" содержит произвольный текст в ячейках и имеет формат "текст", но некоторые ячейки могут быть пустыми. Нужно подсчитать количество заполненных ячеек в столбце "В" по месяцам, с учетом даты в столбце "А". Т.е. в отдельных ячейках подсчитывается количество заполненных ячеек столбца "В" за январь, февраль, март и т.д. Во вложении пример того, как это должно выглядеть. Есть функция СЧЕТЕСЛИ(В:В;"<>"&""), но она считает все заполненные ячейки столбца, как её можно переписать для решения? Ну или может есть другие функции, позволяющие решить данную задачу?
|
|
|
|
eeigor
Ubuntu 18.04 LTS • LO 7.0.2.2
Форумчанин
 
Online
Пол: 
Сообщений: 379
|
=SUMPRODUCT($B$2:$B$8<>"";TEXT($A$2:$A$8;"MMMM")=$C2)
Upd1: В файле добавлен ещё один способ получения итогов через Multiple Operations: таблица подстановки с одним входом (параметром). Для корректировки диапазонов вы можете использовать функцию СМЕЩ(OFFSET) или задать диапазон с заведомо большей нижней границей. Upd2: Плюс сводная таблица. Однако, тут я... "в стадии освоения". Возможности очень малы...
|
|
« Последнее редактирование: 7 Февраль 2021, 22:08 от eeigor »
|
Записан
|
|
|
|
kaufman
Участник

Offline
Сообщений: 6
|
Спасибо большое, а возможно ли такое реализовать, если вообще убрать столбец "С" с названиями месяцев (как в прикрепленном файле, ну т.е. убрать привязку к месяцам, а отталкиваться только от дат в столбце "А")?
P.S.: понял только как работает формула - =SUMPRODUCT($B$2:$B$8<>"";TEXT($A$2:$A$8;"MMMM")=$C2), что идет дальше затруднительно. Если есть возможность, можно более подробно расписать, что происходит в других столбцах ( формулы - =Ф.ТЕКСТ(G1)&" →", или Multiple Operation). Ну ссылки, где про это можно почитать с примерами, желательно...
|
|
« Последнее редактирование: 7 Февраль 2021, 22:19 от kaufman »
|
Записан
|
|
|
|
eeigor
Ubuntu 18.04 LTS • LO 7.0.2.2
Форумчанин
 
Online
Пол: 
Сообщений: 379
|
Поправил сводную таблицу: теперь ОК возможно ли такое реализовать, если вообще убрать столбец "С" с названиями месяцев... а отталкиваться только от дат в столбце "А" Думаю, что этот простой для Excel вопрос здесь не решён. Могу ошибаться (?). Я не использую сводные таблицы в виду их малой функциональности. Но в данном примере всё вполне приемлемо, кроме форматирования (жирных рамок и отсутствия возможности сохранить форматирование). В частности, столбец C здесь нужен только для решения со сводной таблицей. В формуле SUMPRODUCT он не используется. Удалите столбец C вместе со сводной таблицей. Upd1: Ну или может есть другие функции, позволяющие решить данную задачу? Я постарался ответить на ваш вопрос. Всё это непростые темы. Но теперь вы знаете, что искать. Ответы ищите в книгах Уокенбаха (или на его принципах) по Excel. Upd2: Я обновил файл. Загрузите ещё раз. Формула =IF(ISBLANK(A2);"";DATE(YEAR(A2);MONTH(A2);"1")) предполагает, что данные будут выходить за пределы одного года (используется при группировке дат по месяцам в сводной таблице).
|
|
« Последнее редактирование: 8 Февраль 2021, 08:13 от eeigor »
|
Записан
|
|
|
|
kaufman
Участник

Offline
Сообщений: 6
|
Спасибо, буду разбираться.
|
|
|
Записан
|
|
|
|
eeigor
Ubuntu 18.04 LTS • LO 7.0.2.2
Форумчанин
 
Online
Пол: 
Сообщений: 379
|
@kaufman, вот, например, ссылка на таблицы данных или, по другому, подстановки (то, что здесь назвали MULTIPLE.OPERATIONS – в Excel это формула ТАБЛИЦА). В моём примере – с одним входом (параметром или переменной «месяц»). …если это прибавит ясности. Это целый скрытый механизм работы, и выполняемых операций действительно много. Но сложность всё-таки не в этом, а в понимании операций с массивами, и вы, как написали, разобрались с работой функции СУММПРОИЗВ (SUMPRODUCT). Это главное. В таблице подстановки используется именно она, только много раз, поэтому операции множественные (multiple*). Upd1: * Впрочем, этот термин может означать и другое: множественные операции с данными и ячейками, которые остаются «за кадром».
|
|
« Последнее редактирование: 8 Февраль 2021, 08:16 от eeigor »
|
Записан
|
|
|
|
Bigor
|
Есть функция СЧЕТЕСЛИ =COUNTIFS($B$2:$B$8;"<>";$A$2:$A$8;">="&C26;$A$2:$A$8;"<="&EOMONTH(C26;0)) месяц вводится как 01.01.2021 и форматом ячейки выводим только месяц см ячейки залитые желтым
|
|
|
Записан
|
|
|
|
eeigor
Ubuntu 18.04 LTS • LO 7.0.2.2
Форумчанин
 
Online
Пол: 
Сообщений: 379
|
@Bigor, тоже оригинально реализовано условие BETWEEN: $A$2:$A$8;">="&C26;$A$2:$A$8;"<="&EOMONTH(C26;0) 
|
|
« Последнее редактирование: 8 Февраль 2021, 08:40 от eeigor »
|
Записан
|
|
|
|
eeigor
Ubuntu 18.04 LTS • LO 7.0.2.2
Форумчанин
 
Online
Пол: 
Сообщений: 379
|
Пожалуй, собраны все решения. В стороне осталось только одно: промежуточные итоги с условием (subtotal with condition*), но это больше похоже на трюк, а сами данные требуют сортировки по дате. Зато данные будут собраны в группы. Upd1: Ответ на это решение размещён здесь, если кому-то интересно (автор Laurent Longre).
|
|
« Последнее редактирование: 8 Февраль 2021, 13:18 от eeigor »
|
Записан
|
|
|
|
kaufman
Участник

Offline
Сообщений: 6
|
А можно в формулах указывать диапазон ячеек от заданной (допустим "А1" до последней заполненной на листе допустим "А8"). Если на листе появляется заполненная ячейка "А9", то диапазон автоматически в формулах становится как "А1:А9" вместо "А1:А8"? Т.е. возможно ли задавать диапазон ячеек в формулах типа "А1:ENDLIST", просто данных много с такими формулами, и если указывать весь диапазон ("А:А" или "А1:А1048576"), то libre office calc очень сильно тормозит.
|
|
|
Записан
|
|
|
|
|
eeigor
Ubuntu 18.04 LTS • LO 7.0.2.2
Форумчанин
 
Online
Пол: 
Сообщений: 379
|
Чем вас не устраивает диапазон «с запасом»? A2:A999. Только ниже ничего не размещать. Можно использовать функцию СМЕЩ (OFFSET)*, тогда диапазон будет динамическим, как вы просите, но эта функция волатильная или, как их здесь называют, – «летучая», то есть пересчитываемая при любом обновлении данных, даже не затрагиваемом зависимые с ней ячейки. Это плохо, но при умеренном объёме данных не критично. Со сводной таблицей не работает: там можно использовать только абсолютные ссылки, в отличие от Excel.
Upd1: * Хотя есть и неволатильная вариация с функцией ИНДЕКС (INDEX), но это тоже из области «сложного для восприятия». Может и приведу пример, но самому требуется обратиться к «источнику». Вам это надо (неволатильный динамический диапазон без всяких «запасов»)?
|
|
« Последнее редактирование: 8 Февраль 2021, 09:29 от eeigor »
|
Записан
|
|
|
|
kaufman
Участник

Offline
Сообщений: 6
|
Спасибо большое всем, остановлюсь пока на варианте с диапазоном с запасом. Есть другой вопрос: в ячейках столбца "А" находятся символы "+" или "-", нужно в соседнюю ячейку вывести сумму значений всех вхождений каждого символа.
|
|
|
|
Bigor
|
Это как бы другой вопрос, нехорошо объединять все в одну тему. И хотелось бы пример в формате ods
|
|
|
Записан
|
|
|
|
economist
|
символы "+" или "-", нужно в соседнюю ячейку вывести сумму значений СЧЁТЕСЛИ()
|
|
|
Записан
|
Руб. за сто, что Питоньяк Любит водку и коньяк! Потому что мне, без оных, - Не понять его никак...
|
|
|
|