Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Необходимо вычислить кол-во пустых ячеек в столбце, вверх - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Необходимо вычислить кол-во пустых ячеек в столбце, вверх (Формулы/Formulas)
Необходимо вычислить кол-во пустых ячеек в столбце, вверх
ToniZa Дата: Вторник, 26.05.2020, 19:14 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Приветствую гуру Excel.

Задача:

Есть ли какая-нибудь возможность без макросов (формулами) вычислить ближайшую не пустую ячейку в столбце, расположенную выше текущей? Причем "текущая" тоже, вычисляется, как соседняя, слева, от последней заполненной в соседнем столбце, справа.

Поясню - допустим, в столбце "D", в ячейке "D5" есть запись (число, время и т.п.), следующая запись (с верху вниз), расположена в соседнем столбце "Е", допустим, в ячейке - "Е9".

Соответственно - формула должна посчитать кол-во пустых ячеек в диапазоне "D9-D5", причем, исходная точка отсчета (ячейка "D9"), должна так же быть вычислена автоматически, на основе того, что соседняя, справа, ячейка "Е9", является последней заполненной в столбце "Е".

На всякий случай:

Цель - либо вернуть её координаты, либо сразу посчитать кол-во пустых ячеек между исходной и ближайшей не пустой (вверх). Если можно без вычисления координат, просто посчитать кол-во пустых ячеек между исходной и ближайшей вверх, тоже сойдет.
 
Ответить
СообщениеПриветствую гуру Excel.

Задача:

Есть ли какая-нибудь возможность без макросов (формулами) вычислить ближайшую не пустую ячейку в столбце, расположенную выше текущей? Причем "текущая" тоже, вычисляется, как соседняя, слева, от последней заполненной в соседнем столбце, справа.

Поясню - допустим, в столбце "D", в ячейке "D5" есть запись (число, время и т.п.), следующая запись (с верху вниз), расположена в соседнем столбце "Е", допустим, в ячейке - "Е9".

Соответственно - формула должна посчитать кол-во пустых ячеек в диапазоне "D9-D5", причем, исходная точка отсчета (ячейка "D9"), должна так же быть вычислена автоматически, на основе того, что соседняя, справа, ячейка "Е9", является последней заполненной в столбце "Е".

На всякий случай:

Цель - либо вернуть её координаты, либо сразу посчитать кол-во пустых ячеек между исходной и ближайшей не пустой (вверх). Если можно без вычисления координат, просто посчитать кол-во пустых ячеек между исходной и ближайшей вверх, тоже сойдет.

Автор - ToniZa
Дата добавления - 26.05.2020 в 19:14
Pelena Дата: Вторник, 26.05.2020, 19:20 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19163
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Файл с примером помог бы в понимании проблемы


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеФайл с примером помог бы в понимании проблемы

Автор - Pelena
Дата добавления - 26.05.2020 в 19:20
ToniZa Дата: Вторник, 26.05.2020, 19:39 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Файл с примером помог бы в понимании проблемы
К сообщению приложен файл: T_2.xls (37.0 Kb)


Сообщение отредактировал ToniZa - Вторник, 26.05.2020, 19:44
 
Ответить
Сообщение
Файл с примером помог бы в понимании проблемы

Автор - ToniZa
Дата добавления - 26.05.2020 в 19:39
Pelena Дата: Вторник, 26.05.2020, 20:27 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19163
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Маловато вариантов. Строго по примеру
Код
=ЕСЛИ(D4="";"";(B4-ПРОСМОТР(1;$C$4:C4;$B$4:B4))*8+(D4-ПРОСМОТР(1;$C$4:C4))*24)
К сообщению приложен файл: 1935949.xls (37.5 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМаловато вариантов. Строго по примеру
Код
=ЕСЛИ(D4="";"";(B4-ПРОСМОТР(1;$C$4:C4;$B$4:B4))*8+(D4-ПРОСМОТР(1;$C$4:C4))*24)

Автор - Pelena
Дата добавления - 26.05.2020 в 20:27
ToniZa Дата: Вторник, 26.05.2020, 20:48 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Маловато вариантов. Строго по примеру


Могу и больше вариантов приложить с удовольствием. На самом деле, задача для меня очень сложная. Просто пытался решить её поэтапно. Но, если бы вы смогли предложить мне полное готовое решение, был бы очень благода...

Нужна помощь в составлении шаблона таблицы, для учета де-факто затраченных часов на выполнение задач сотрудниками, в течении месяца.

Вводные данные:

1. Список сотрудников (у каждого из них свои, индивидуальные, задачи)
2. Календарь на месяц, разбитый на дни
3. Задачи могут быть, как мелкие (выполнение нескольких разных задач за один день, но не более 3 в день), но, чаще задачи выполняются в течении нескольких дней. Т.е. (для примера) - начало выполнения задачи - Пн, 11:00, окончание - Ср. 15:30 и т.д.

Задача:

1. должны фиксироваться - время начала выполнения задачи и время окончания выполнения задачи
2. необходимо посчитать затраченное время в часах (не в формате времени! Т.е - 25,5 часа, или 4,33 часа и т.д), на каждую отдельную задачу. Причем, если время начала и окончания задачи расположены в разные дни, то, разумеется, нужно считать часы за вычетом выходных дней.

Я прилагаю файл excel, в котором находится не рабочий макет таблицы, так, как это я себе представляю. При этом, если будут альтернативные идеи - какой вид таблицы для этого может подойти лучше, с удовольствием рассмотрю эти варианты. Правда, тут упор делается на обозримость. У таблицы две, равно приоритетные задачи:

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

В выделенных зеленым, ячейках, доп. комментарии
К сообщению приложен файл: _3.xls (59.0 Kb)
 
Ответить
Сообщение
Маловато вариантов. Строго по примеру


Могу и больше вариантов приложить с удовольствием. На самом деле, задача для меня очень сложная. Просто пытался решить её поэтапно. Но, если бы вы смогли предложить мне полное готовое решение, был бы очень благода...

Нужна помощь в составлении шаблона таблицы, для учета де-факто затраченных часов на выполнение задач сотрудниками, в течении месяца.

Вводные данные:

1. Список сотрудников (у каждого из них свои, индивидуальные, задачи)
2. Календарь на месяц, разбитый на дни
3. Задачи могут быть, как мелкие (выполнение нескольких разных задач за один день, но не более 3 в день), но, чаще задачи выполняются в течении нескольких дней. Т.е. (для примера) - начало выполнения задачи - Пн, 11:00, окончание - Ср. 15:30 и т.д.

Задача:

1. должны фиксироваться - время начала выполнения задачи и время окончания выполнения задачи
2. необходимо посчитать затраченное время в часах (не в формате времени! Т.е - 25,5 часа, или 4,33 часа и т.д), на каждую отдельную задачу. Причем, если время начала и окончания задачи расположены в разные дни, то, разумеется, нужно считать часы за вычетом выходных дней.

Я прилагаю файл excel, в котором находится не рабочий макет таблицы, так, как это я себе представляю. При этом, если будут альтернативные идеи - какой вид таблицы для этого может подойти лучше, с удовольствием рассмотрю эти варианты. Правда, тут упор делается на обозримость. У таблицы две, равно приоритетные задачи:

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

В выделенных зеленым, ячейках, доп. комментарии

Автор - ToniZa
Дата добавления - 26.05.2020 в 20:48
Pelena Дата: Вторник, 26.05.2020, 21:42 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19163
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Код
=ЕСЛИ(D5="";"";ЕСЛИ(C5>0;(D5-C5)*24;(ЧИСТРАБДНИ(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ПРОСМОТР(0;-1/($C$5:C5>0)/($D$5:D5=0);$A$5:A5);ПРОСМОТР(0;-1/($G$5:G5>0)/($H$5:H5=0);$A$5:A5));ПРОСМОТР(0;-1/($K$5:K5>0)/($L$5:L5=0);$A$5:A5));0);A5)-1)*8+(D5-ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ПРОСМОТР(0;-1/($C$5:C5>0)/($D$5:D5=0);$C$5:C5);ПРОСМОТР(0;-1/($G$5:G5>0)/($H$5:H5=0);$G$5:G5));ПРОСМОТР(0;-1/($K$5:K5>0)/($L$5:L5=0);$A$5:A5));0))*24))
К сообщению приложен файл: 1881364.xls (73.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Код
=ЕСЛИ(D5="";"";ЕСЛИ(C5>0;(D5-C5)*24;(ЧИСТРАБДНИ(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ПРОСМОТР(0;-1/($C$5:C5>0)/($D$5:D5=0);$A$5:A5);ПРОСМОТР(0;-1/($G$5:G5>0)/($H$5:H5=0);$A$5:A5));ПРОСМОТР(0;-1/($K$5:K5>0)/($L$5:L5=0);$A$5:A5));0);A5)-1)*8+(D5-ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ПРОСМОТР(0;-1/($C$5:C5>0)/($D$5:D5=0);$C$5:C5);ПРОСМОТР(0;-1/($G$5:G5>0)/($H$5:H5=0);$G$5:G5));ПРОСМОТР(0;-1/($K$5:K5>0)/($L$5:L5=0);$A$5:A5));0))*24))

Автор - Pelena
Дата добавления - 26.05.2020 в 21:42
ToniZa Дата: Вторник, 26.05.2020, 22:43 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Спасибо огромное за ваши старания! Весьма признателен! Правда, выяснилось, что в присланный вами пример, начинает работать не корректно, если в один день добавляется окончание задачи №2 (и, затем начало задачи №3, тоже, уже не отрабатывает)

В примере ячейки выделены красным
К сообщению приложен файл: 003.xls (74.5 Kb)
 
Ответить
СообщениеСпасибо огромное за ваши старания! Весьма признателен! Правда, выяснилось, что в присланный вами пример, начинает работать не корректно, если в один день добавляется окончание задачи №2 (и, затем начало задачи №3, тоже, уже не отрабатывает)

В примере ячейки выделены красным

Автор - ToniZa
Дата добавления - 26.05.2020 в 22:43
Pelena Дата: Вторник, 26.05.2020, 23:39 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19163
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Дело не в этом. Издержки копи-паста, не исправила в одном месте диапазон
К сообщению приложен файл: 7107919.xls (73.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеДело не в этом. Издержки копи-паста, не исправила в одном месте диапазон

Автор - Pelena
Дата добавления - 26.05.2020 в 23:39
ToniZa Дата: Среда, 27.05.2020, 08:31 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, Примите мою огромнейшую и сердечную благодарность!

Теперь всё работает именно так, как я и хотел! Спасибо огромное! Вы - спасительница! Спасаете жизни людей!
 
Ответить
СообщениеPelena, Примите мою огромнейшую и сердечную благодарность!

Теперь всё работает именно так, как я и хотел! Спасибо огромное! Вы - спасительница! Спасаете жизни людей!

Автор - ToniZa
Дата добавления - 27.05.2020 в 08:31
ToniZa Дата: Среда, 27.05.2020, 12:16 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, Всё таки обнаружил один Баг:

Если в ячейке "C10" (начало первой задачи) не указано время начала (например, потому, что время начала задачи - другой день), а в ячейках "G10", или "K10" (начало второй и третей задач - выделено красным) указать время начала и не указать время окончания (ячейки, выделены синим), то в ячейке "сумма" первой задачи, отображается неправильный результат.

Буду признателен, если поможете исправить эту ошибку.
------------------------------

И ещё такой (дополнительный) вопрос - в вашей формуле вы используете абсолютные ссылки. Если я, для каких-то целей скопирую колонку с формулой (например, на том же самом листе, хочу разместить еще одного сотрудника, рядом с первым), то привязка "$C$5" уже не будет работать, ибо колонка с формулой переместится. Отсюда вопрос:

Есть ли какой-то легкий (быстрый, простой) способ, переносить формулы с жесткими ссылками, в другие столбцы так, что бы там автоматически (ну, или не автоматически, но легко (удобно, быстро) перепривязывать жесткие ссылки к другой ячейке?
К сообщению приложен файл: 004.xls (71.0 Kb)


Сообщение отредактировал ToniZa - Среда, 27.05.2020, 12:21
 
Ответить
СообщениеPelena, Всё таки обнаружил один Баг:

Если в ячейке "C10" (начало первой задачи) не указано время начала (например, потому, что время начала задачи - другой день), а в ячейках "G10", или "K10" (начало второй и третей задач - выделено красным) указать время начала и не указать время окончания (ячейки, выделены синим), то в ячейке "сумма" первой задачи, отображается неправильный результат.

Буду признателен, если поможете исправить эту ошибку.
------------------------------

И ещё такой (дополнительный) вопрос - в вашей формуле вы используете абсолютные ссылки. Если я, для каких-то целей скопирую колонку с формулой (например, на том же самом листе, хочу разместить еще одного сотрудника, рядом с первым), то привязка "$C$5" уже не будет работать, ибо колонка с формулой переместится. Отсюда вопрос:

Есть ли какой-то легкий (быстрый, простой) способ, переносить формулы с жесткими ссылками, в другие столбцы так, что бы там автоматически (ну, или не автоматически, но легко (удобно, быстро) перепривязывать жесткие ссылки к другой ячейке?

Автор - ToniZa
Дата добавления - 27.05.2020 в 12:16
AlexM Дата: Среда, 27.05.2020, 12:38 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеКросс

Автор - AlexM
Дата добавления - 27.05.2020 в 12:38
Pelena Дата: Среда, 27.05.2020, 14:26 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19163
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Так проверьте.

привязка "$C$5" уже не будет работать

Чтобы формулы копировались по столбцам, надо убрать значки $ перед именами столбцов, кроме столбца А, он остаётся зафиксированным
К сообщению приложен файл: 0101760.xls (69.5 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТак проверьте.

привязка "$C$5" уже не будет работать

Чтобы формулы копировались по столбцам, надо убрать значки $ перед именами столбцов, кроме столбца А, он остаётся зафиксированным

Автор - Pelena
Дата добавления - 27.05.2020 в 14:26
ToniZa Дата: Среда, 27.05.2020, 14:54 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, Ещё раз - громадное Пасибо! Вроде, стало как надо!
 
Ответить
СообщениеPelena, Ещё раз - громадное Пасибо! Вроде, стало как надо!

Автор - ToniZa
Дата добавления - 27.05.2020 в 14:54
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Необходимо вычислить кол-во пустых ячеек в столбце, вверх (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!