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

Вход

Регистрация

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

 

= Мир MS Excel/К дате прибавить ячейку, в которой формула с текстом - Страница 4 - Мир MS Excel

Старая форма входа
  • Страница 4 из 4
  • «
  • 1
  • 2
  • 3
  • 4
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » К дате прибавить ячейку, в которой формула с текстом (Формулы/Formulas)
К дате прибавить ячейку, в которой формула с текстом
Pelena Дата: Среда, 14.02.2018, 15:33 | Сообщение № 61
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

Excel 365 & Mac Excel
Никанор, Вы точно мой файл проверяете?

Попутно вопрос: зачем нужны эти разные таблицы? Нельзя в один столбец выписать все праздники и использовать функцию РАБДЕНЬ? Тему не смотрела, если был такой вариант, сорри

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

Попутно вопрос: зачем нужны эти разные таблицы? Нельзя в один столбец выписать все праздники и использовать функцию РАБДЕНЬ? Тему не смотрела, если был такой вариант, сорри
Автор - Pelena
Дата добавления - 14.02.2018 в 15:33
Никанор Дата: Среда, 14.02.2018, 15:58 | Сообщение № 62
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Цитата Pelena, 14.02.2018 в 15:33, в сообщении № 61 ()
Вы точно мой файл проверяете?

Извините я ошибся проверял не Ваш файл.
Теперь формула стала в больших случаях правильно считать.

1. Если в F14 1 календарный день,
а в Е15 24 декабря января 2017,
то в F15 должно быть 24 декабря.
Результат правильный 24 декабря.
Формула улучшилась –раньше в этом примере считала неправильно.

2. Если в F14 1 календарный день,
а в Е15 6 января 2018,
в F15 должно быть 6 января.
Результат правильный 6 января.
Формула улучшилась – раньше в этом примере считала неправильно.

3. Если в F14 1 календарный день,
а в Е15 30 апреля 2017,
то в F15 должно быть 30 апреля.
Результат правильный 30 апреля.

4. Если в F14 2 календарных дня,
а в Е15 30 апреля 2017,
то в F15 должно быть 3 мая.
Результат неправильный 2 мая. 2 мая был праздник

5. Этот пример добавлен после редактирования сообщения:
Если в F14 8 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 10 мая.
Результат неправильный 9 мая. 9 мая был праздник


6. Этот пример добавлен после редактирования сообщения:
Если в F14 9 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 11 мая.
Результат неправильный 10 мая.


7. Этот пример добавлен после редактирования сообщения:
Если в F14 10 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 12 мая.
Результат правильный 12 мая.

8. Этот пример добавлен после редактирования сообщения:
Если в F14 11 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 13 мая.
Результат правильный 13 мая.

Я заметил закономерность: ЕСЛИ в Е15 30 апреля и ЕСЛИ 10 дней и больше результат правильный.

9. Если в F14 3 календарных дня,
а в Е15 30 апреля 2017,
то в F15 должно быть 4 мая.
Результат правильный 4 мая.

10. Если в F14 10 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 12 мая.
Результат правильный 12 мая.


11. Если в F14 7 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 8 мая.
Результат правильный 8 мая.

12. Если в F14 2 календарных дня,
а в Е15 24 декабря января 2017,
то в F15 должно быть 26 декабря.
Результат правильный 26 декабря.

13. Если в F14 10 календарных дней,
а в Е15 29 декабря января 2017,
то в F15 должно быть 9 января.
Результат правильный 9 января.

14. Сейчас формула стала считать правильно в примерах ниже, а раньше считала неправильно. А вот примеры, если в периоде нет праздников:
Если в F14 1 календарный день,
а в Е15 5 декабря января 2017,
то в F15 должно быть 5 декабря.
Результат правильный 5 декабря.
Теперь Результат неправильный.


Сообщение отредактировал Никанор - Среда, 14.02.2018, 17:03
 
Ответить
Сообщение
Цитата Pelena, 14.02.2018 в 15:33, в сообщении № 61 ()
Вы точно мой файл проверяете?

Извините я ошибся проверял не Ваш файл.
Теперь формула стала в больших случаях правильно считать.

1. Если в F14 1 календарный день,
а в Е15 24 декабря января 2017,
то в F15 должно быть 24 декабря.
Результат правильный 24 декабря.
Формула улучшилась –раньше в этом примере считала неправильно.

2. Если в F14 1 календарный день,
а в Е15 6 января 2018,
в F15 должно быть 6 января.
Результат правильный 6 января.
Формула улучшилась – раньше в этом примере считала неправильно.

3. Если в F14 1 календарный день,
а в Е15 30 апреля 2017,
то в F15 должно быть 30 апреля.
Результат правильный 30 апреля.

4. Если в F14 2 календарных дня,
а в Е15 30 апреля 2017,
то в F15 должно быть 3 мая.
Результат неправильный 2 мая. 2 мая был праздник

5. Этот пример добавлен после редактирования сообщения:
Если в F14 8 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 10 мая.
Результат неправильный 9 мая. 9 мая был праздник


6. Этот пример добавлен после редактирования сообщения:
Если в F14 9 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 11 мая.
Результат неправильный 10 мая.


7. Этот пример добавлен после редактирования сообщения:
Если в F14 10 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 12 мая.
Результат правильный 12 мая.

8. Этот пример добавлен после редактирования сообщения:
Если в F14 11 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 13 мая.
Результат правильный 13 мая.

Я заметил закономерность: ЕСЛИ в Е15 30 апреля и ЕСЛИ 10 дней и больше результат правильный.

9. Если в F14 3 календарных дня,
а в Е15 30 апреля 2017,
то в F15 должно быть 4 мая.
Результат правильный 4 мая.

10. Если в F14 10 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 12 мая.
Результат правильный 12 мая.


11. Если в F14 7 календарных дней,
а в Е15 30 апреля 2017,
то в F15 должно быть 8 мая.
Результат правильный 8 мая.

12. Если в F14 2 календарных дня,
а в Е15 24 декабря января 2017,
то в F15 должно быть 26 декабря.
Результат правильный 26 декабря.

13. Если в F14 10 календарных дней,
а в Е15 29 декабря января 2017,
то в F15 должно быть 9 января.
Результат правильный 9 января.

14. Сейчас формула стала считать правильно в примерах ниже, а раньше считала неправильно. А вот примеры, если в периоде нет праздников:
Если в F14 1 календарный день,
а в Е15 5 декабря января 2017,
то в F15 должно быть 5 декабря.
Результат правильный 5 декабря.
Теперь Результат неправильный.
Автор - Никанор
Дата добавления - 14.02.2018 в 15:58
Никанор Дата: Среда, 14.02.2018, 16:10 | Сообщение № 63
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Цитата Pelena, 14.02.2018 в 15:33, в сообщении № 61 ()
Нельзя в один столбец выписать все праздники и использовать функцию РАБДЕНЬ?

Я не знаю, так как праздники наверное не совпадают на Украине и в России. На Украине праздники часто меняются. Поэтому я сделал таблицы праздников. Эти таблицы принимают и в расчётах ячеек E20:E31. Поэтому мне эти таблицы нужны для расчётов.
Может как то можно сделать формулой.


Сообщение отредактировал Никанор - Среда, 14.02.2018, 16:12
 
Ответить
Сообщение
Цитата Pelena, 14.02.2018 в 15:33, в сообщении № 61 ()
Нельзя в один столбец выписать все праздники и использовать функцию РАБДЕНЬ?

Я не знаю, так как праздники наверное не совпадают на Украине и в России. На Украине праздники часто меняются. Поэтому я сделал таблицы праздников. Эти таблицы принимают и в расчётах ячеек E20:E31. Поэтому мне эти таблицы нужны для расчётов.
Может как то можно сделать формулой.
Автор - Никанор
Дата добавления - 14.02.2018 в 16:10
Pelena Дата: Среда, 14.02.2018, 17:25 | Сообщение № 64
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

Excel 365 & Mac Excel
Проверьте так
К сообщению приложен файл: 5295922.xlsx (45.8 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПроверьте так
Автор - Pelena
Дата добавления - 14.02.2018 в 17:25
Никанор Дата: Среда, 14.02.2018, 17:59 | Сообщение № 65
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Елена!
Спасибо большое – сейчас проверяю. Тех ошибок что были уже нет.
Проверю разные варианты.
Более точно сообщу завтра.
 
Ответить
СообщениеЕлена!
Спасибо большое – сейчас проверяю. Тех ошибок что были уже нет.
Проверю разные варианты.
Более точно сообщу завтра.
Автор - Никанор
Дата добавления - 14.02.2018 в 17:59
Pelena Дата: Среда, 14.02.2018, 18:24 | Сообщение № 66
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

Excel 365 & Mac Excel
Можно ещё использовать такую формулу массива
Код
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14)

или для Excel 2010 и выше
Код
=АГРЕГАТ(15;6;(E15+СТРОКА($1:$100)-1)/(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0);F14)

100 - это ограничение дней отпуска. Если может быть больше, увеличьте
В файле эти формулы в оранжевых ячейках
К сообщению приложен файл: 9308106.xlsx (45.9 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМожно ещё использовать такую формулу массива
Код
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14)

или для Excel 2010 и выше
Код
=АГРЕГАТ(15;6;(E15+СТРОКА($1:$100)-1)/(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0);F14)

100 - это ограничение дней отпуска. Если может быть больше, увеличьте
В файле эти формулы в оранжевых ячейках
Автор - Pelena
Дата добавления - 14.02.2018 в 18:24
Che79 Дата: Среда, 14.02.2018, 19:12 | Сообщение № 67
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Pelena, Лена, варианты просто блеск!! Не хватает мне мозга, часто даже на простые вещи. А уж на такие варианты...
Наверное, рано мне быть помогающим.....

Делай нормально и будет нормально!
 
Ответить
СообщениеPelena, Лена, варианты просто блеск!! Не хватает мне мозга, часто даже на простые вещи. А уж на такие варианты...
Наверное, рано мне быть помогающим.....
Автор - Che79
Дата добавления - 14.02.2018 в 19:12
Pelena Дата: Среда, 14.02.2018, 20:56 | Сообщение № 68
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

Excel 365 & Mac Excel
[offtop]Ой-ой-ой, хватит прибедняться. На комплимент напрашиваешься? :D [/offtop]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение[offtop]Ой-ой-ой, хватит прибедняться. На комплимент напрашиваешься? :D [/offtop]
Автор - Pelena
Дата добавления - 14.02.2018 в 20:56
Che79 Дата: Среда, 14.02.2018, 21:39 | Сообщение № 69
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
[offtop]
Цитата Pelena, 14.02.2018 в 20:56, в сообщении № 68 ()
На комплимент напрашиваешься?
даже и не думал. Просто, здоровая самокритика. Какие-то моменты явно нужно подтянуть. Сорри за оффтоп.

Делай нормально и будет нормально!
 
Ответить
Сообщение[offtop]
Цитата Pelena, 14.02.2018 в 20:56, в сообщении № 68 ()
На комплимент напрашиваешься?
даже и не думал. Просто, здоровая самокритика. Какие-то моменты явно нужно подтянуть. Сорри за оффтоп.
Автор - Che79
Дата добавления - 14.02.2018 в 21:39
Никанор Дата: Четверг, 15.02.2018, 12:22 | Сообщение № 70
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Che79, Алексей, Pelena, Елена Здравствуйте!
Большое спасибо за помощь в написании таких сложных формул.
Вы вместе вдвоём – помогая один одному, а в конечном итоге мне пришли к правильному решению.
Я проверял многие варианты.

1. Формула из файла Елены
Цитата Pelena, 14.02.2018 в 17:25, в сообщении № 64 ()
5295922.xlsx(46Kb)
Код
=E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1))
даёт правильный результат.

2. Формула массива (сочетание клавиш Ctrl+Shift+Enter) из файла Елены
Цитата Pelena, 14.02.2018 в 18:24, в сообщении № 66 ()
9308106.xlsx(46Kb)
Код
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14)

тоже даёт правильный результат.

3. Формулы для Excel 2010 и выше из файла Елены
Цитата Pelena, 14.02.2018 в 18:24, в сообщении № 66 ()
308106.xlsx(46Kb)
сейчас проверить не могу – так как у меня сейчас нет Excel 2010. Но думаю, они считают правильно. Так как наверное существует алгоритм или правила "перевода" формул из одной версии Excel в другую версию. И Елена этим воспользовалась. Я так думаю.
Формула в сообщении Елены для Excel 2010 и выше из Дата: Среда, 14.02.2018, 18:24 | Сообщение № 66
Код
=АГРЕГАТ(15;6;(E15+СТРОКА($1:$100)-1)/(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0);F14)

У меня почему то формула в скачанном на компьютер файле выглядит по другому. Вот так:
Код
=_xlfn.AGGREGATE(15;6;(E15+СТРОКА($1:$100)-1)/(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0);F14)

Скажите пожалуйста, это одно и тоже что в сообщении?

Ещё вопрос: как можно изменить формулы Елены из этого сообщения.
Формулу 1-ую и формулу массива 2-ую с УСЛОВИЕМ что в ячейке F14 не число, а формула с ТЕКСТом:
Код
="на "&ОКРУГЛВВЕРХ((F13-F12)/365*24;0)& " календарных дней"

Файл прилагаю
К сообщению приложен файл: 5987729.xlsx (43.5 Kb)


Сообщение отредактировал Никанор - Четверг, 15.02.2018, 12:38
 
Ответить
СообщениеChe79, Алексей, Pelena, Елена Здравствуйте!
Большое спасибо за помощь в написании таких сложных формул.
Вы вместе вдвоём – помогая один одному, а в конечном итоге мне пришли к правильному решению.
Я проверял многие варианты.

1. Формула из файла Елены
Цитата Pelena, 14.02.2018 в 17:25, в сообщении № 64 ()
5295922.xlsx(46Kb)
Код
=E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1))
даёт правильный результат.

2. Формула массива (сочетание клавиш Ctrl+Shift+Enter) из файла Елены
Цитата Pelena, 14.02.2018 в 18:24, в сообщении № 66 ()
9308106.xlsx(46Kb)
Код
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14)

тоже даёт правильный результат.

3. Формулы для Excel 2010 и выше из файла Елены
Цитата Pelena, 14.02.2018 в 18:24, в сообщении № 66 ()
308106.xlsx(46Kb)
сейчас проверить не могу – так как у меня сейчас нет Excel 2010. Но думаю, они считают правильно. Так как наверное существует алгоритм или правила "перевода" формул из одной версии Excel в другую версию. И Елена этим воспользовалась. Я так думаю.
Формула в сообщении Елены для Excel 2010 и выше из Дата: Среда, 14.02.2018, 18:24 | Сообщение № 66
Код
=АГРЕГАТ(15;6;(E15+СТРОКА($1:$100)-1)/(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0);F14)

У меня почему то формула в скачанном на компьютер файле выглядит по другому. Вот так:
Код
=_xlfn.AGGREGATE(15;6;(E15+СТРОКА($1:$100)-1)/(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0);F14)

Скажите пожалуйста, это одно и тоже что в сообщении?

Ещё вопрос: как можно изменить формулы Елены из этого сообщения.
Формулу 1-ую и формулу массива 2-ую с УСЛОВИЕМ что в ячейке F14 не число, а формула с ТЕКСТом:
Код
="на "&ОКРУГЛВВЕРХ((F13-F12)/365*24;0)& " календарных дней"

Файл прилагаю
Автор - Никанор
Дата добавления - 15.02.2018 в 12:22
Pelena Дата: Четверг, 15.02.2018, 12:38 | Сообщение № 71
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата Никанор, 15.02.2018 в 12:22, в сообщении № 70 ()
выглядит по другому
Функции АГРЕГАТ() нет в Excel 2007, поэтому там она работать не будет

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата Никанор, 15.02.2018 в 12:22, в сообщении № 70 ()
выглядит по другому
Функции АГРЕГАТ() нет в Excel 2007, поэтому там она работать не будет
Автор - Pelena
Дата добавления - 15.02.2018 в 12:38
Никанор Дата: Четверг, 15.02.2018, 13:13 | Сообщение № 72
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Вот так будет выглядеть первая формула, если к первой формуле добавить ТЕКСТ вначале:
Код
="по "& ТЕКСТ(E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1));"ДД.ММ.ГГ")


Вот так будет выглядеть вторая формула массива (сочетание клавиш Ctrl+Shift+Enter), если к второй формуле добавить ТЕКСТ вначале:
Код
= "по "& ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14);"ДД.ММ.ГГ")


Ещё позвольте задать вопрос: как изменить формулу
Код
=E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1))
и формулу массива (сочетание клавиш Ctrl+Shift+Enter)
Код
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14)
с УСЛОВИЕМ что в ячейке F14 не число, а формула с ТЕКСТом:
Код
="на "&ОКРУГЛВВЕРХ((F13-F12)/365*24;0)& " календарных дней"


Сообщение отредактировал Никанор - Четверг, 15.02.2018, 13:23
 
Ответить
СообщениеВот так будет выглядеть первая формула, если к первой формуле добавить ТЕКСТ вначале:
Код
="по "& ТЕКСТ(E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1));"ДД.ММ.ГГ")


Вот так будет выглядеть вторая формула массива (сочетание клавиш Ctrl+Shift+Enter), если к второй формуле добавить ТЕКСТ вначале:
Код
= "по "& ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14);"ДД.ММ.ГГ")


Ещё позвольте задать вопрос: как изменить формулу
Код
=E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1))
и формулу массива (сочетание клавиш Ctrl+Shift+Enter)
Код
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);F14)
с УСЛОВИЕМ что в ячейке F14 не число, а формула с ТЕКСТом:
Код
="на "&ОКРУГЛВВЕРХ((F13-F12)/365*24;0)& " календарных дней"
Автор - Никанор
Дата добавления - 15.02.2018 в 13:13
Che79 Дата: Четверг, 15.02.2018, 13:25 | Сообщение № 73
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Цитата Никанор, 15.02.2018 в 12:22, в сообщении № 70 ()
как можно изменить формулы
Код
="по "&ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);ПСТР(F14;4;ПОИСК("кал";F14)-5));"ДД.ММ.ГГ")
Ввод Ctrl+Shift+Enter. Вторую по аналогии, справитесь сами?

Делай нормально и будет нормально!
 
Ответить
Сообщение
Цитата Никанор, 15.02.2018 в 12:22, в сообщении № 70 ()
как можно изменить формулы
Код
="по "&ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);ПСТР(F14;4;ПОИСК("кал";F14)-5));"ДД.ММ.ГГ")
Ввод Ctrl+Shift+Enter. Вторую по аналогии, справитесь сами?
Автор - Che79
Дата добавления - 15.02.2018 в 13:25
Никанор Дата: Четверг, 15.02.2018, 14:50 | Сообщение № 74
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Che79, Алексей Здравствуйте!
Спасибо за формулу.
Цитата Che79, 15.02.2018 в 13:25, в сообщении № 73 ()
Вторую по аналогии, справитесь сами?

Пробую – не получается. Появляется Диалоговое окно: «Ошибка в формуле»
Вот что я написал – но это не правильно.
Код
="по "&ТЕКСТ(E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)); ПСТР(F14;4;ПОИСК("кал";F14)-5));"ДД.ММ.ГГ")

После ввода этой формулы, когда нажимаю Enter – выделяется "ДД.ММ.ГГ" Что то надо изменить, а что я не знаю.
Скажите пожалуйста, что значит цифра 5 (пять) в формуле. Вот я скопировал часть формулы где цифра 5 (пять) и выделил её красным шрифтом. ПОИСК("кал";F14)-5))


Сообщение отредактировал Никанор - Четверг, 15.02.2018, 15:06
 
Ответить
СообщениеChe79, Алексей Здравствуйте!
Спасибо за формулу.
Цитата Che79, 15.02.2018 в 13:25, в сообщении № 73 ()
Вторую по аналогии, справитесь сами?

Пробую – не получается. Появляется Диалоговое окно: «Ошибка в формуле»
Вот что я написал – но это не правильно.
Код
="по "&ТЕКСТ(E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)); ПСТР(F14;4;ПОИСК("кал";F14)-5));"ДД.ММ.ГГ")

После ввода этой формулы, когда нажимаю Enter – выделяется "ДД.ММ.ГГ" Что то надо изменить, а что я не знаю.
Скажите пожалуйста, что значит цифра 5 (пять) в формуле. Вот я скопировал часть формулы где цифра 5 (пять) и выделил её красным шрифтом. ПОИСК("кал";F14)-5))
Автор - Никанор
Дата добавления - 15.02.2018 в 14:50
Никанор Дата: Четверг, 15.02.2018, 15:44 | Сообщение № 75
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Вот формула для определения первого рабочего дня после даты, указанной в ячейке E15, если в ячейке E15 Формат ячейки Дата.
Код
=РАБДЕНЬ(F15;1)

А вот у меня получилось написать формулу, из формулы выше, если в ячейке E15 формула с ТЕКСТом.
Если в ячейке Е15 формула
Код
="по "&ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);ПСТР(F14;4;ПОИСК("кал";F14)-5));"ДД.ММ.ГГ"

То формула определения первого рабочего дня из ячейки с ТЕКСТом
Код
="по "&ТЕКСТ(РАБДЕНЬ(E15;1);"ДД.ММ.ГГ")
Это я сам написал может подскажите правильно или нет? Мне кажется правильно.

А изменить формулу
Код
=E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1))

с УСЛОВИЕМ что в ячейке F14 не число, а формула с ТЕКСТом не получается. Помогите пожалуйста.
Полный текст вопроса в моём сообщении Дата: Четверг, 15.02.2018, 14:50 | Сообщение № 74


Сообщение отредактировал Никанор - Четверг, 15.02.2018, 15:45
 
Ответить
СообщениеВот формула для определения первого рабочего дня после даты, указанной в ячейке E15, если в ячейке E15 Формат ячейки Дата.
Код
=РАБДЕНЬ(F15;1)

А вот у меня получилось написать формулу, из формулы выше, если в ячейке E15 формула с ТЕКСТом.
Если в ячейке Е15 формула
Код
="по "&ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(K20:AF31;E15+СТРОКА($1:$100)-1)=0;E15+СТРОКА($1:$100)-1);ПСТР(F14;4;ПОИСК("кал";F14)-5));"ДД.ММ.ГГ"

То формула определения первого рабочего дня из ячейки с ТЕКСТом
Код
="по "&ТЕКСТ(РАБДЕНЬ(E15;1);"ДД.ММ.ГГ")
Это я сам написал может подскажите правильно или нет? Мне кажется правильно.

А изменить формулу
Код
=E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+F14;$K$20:$AF$31;"<="&E15+F14-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+F14-1))

с УСЛОВИЕМ что в ячейке F14 не число, а формула с ТЕКСТом не получается. Помогите пожалуйста.
Полный текст вопроса в моём сообщении Дата: Четверг, 15.02.2018, 14:50 | Сообщение № 74
Автор - Никанор
Дата добавления - 15.02.2018 в 15:44
Che79 Дата: Четверг, 15.02.2018, 16:02 | Сообщение № 76
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
К сообщению #74
Цитата Никанор, 15.02.2018 в 14:50, в сообщении № 74 ()
это не правильно
да, Excel не проведёшь!
Вместо F14 везде в формуле с СЧЁТЕСЛИМН() вставьте этот кусок
Код
ПСТР(F14;4;ПОИСК("кал";F14)-5)

Цитата Никанор, 15.02.2018 в 14:50, в сообщении № 74 ()
что значит цифра 5 (пять) в формуле
Справка по ПСТР().
В нашем случае с ПСТР():
1. В F14 содержится текст, из которого мы хотим получить какой-то кусок
2. 4 - номер позиция, начиная с которой нам покажут этот кусок (почему 4 - потому, что первые три это "На" и пробел
3. ПОИСК("кал";F14)-5 количество знаков, сколько именно должен содержать интересующий нас кусок текста. Сначала мы ищем порядковый номер первой буквы (к) слова "Календарных". ПОИСК нам даёт 7. И далее отнимаем 5 для того, чтобы получить длину (количество знаков) нашего искомого куска.
Т.е. если бы у нас кол-во календарных дней было <10, тогда порядковый номер первой буквы (к) слова "Календарных" был бы 6. Далее отнимая 5, мы бы получили единичную длину, что соответствует цифрам от 0 до 9. Также, по аналогии, и с 3 значными числами кол-ва дней отпуска.
Ну и не забывайте использовать команду "Вычислить формулу" на ленте. С помощью неё Вы всегда пошагово отследите работу формул и отловите ошибки в вычислениях.

Делай нормально и будет нормально!
 
Ответить
СообщениеК сообщению #74
Цитата Никанор, 15.02.2018 в 14:50, в сообщении № 74 ()
это не правильно
да, Excel не проведёшь!
Вместо F14 везде в формуле с СЧЁТЕСЛИМН() вставьте этот кусок
Код
ПСТР(F14;4;ПОИСК("кал";F14)-5)

Цитата Никанор, 15.02.2018 в 14:50, в сообщении № 74 ()
что значит цифра 5 (пять) в формуле
Справка по ПСТР().
В нашем случае с ПСТР():
1. В F14 содержится текст, из которого мы хотим получить какой-то кусок
2. 4 - номер позиция, начиная с которой нам покажут этот кусок (почему 4 - потому, что первые три это "На" и пробел
3. ПОИСК("кал";F14)-5 количество знаков, сколько именно должен содержать интересующий нас кусок текста. Сначала мы ищем порядковый номер первой буквы (к) слова "Календарных". ПОИСК нам даёт 7. И далее отнимаем 5 для того, чтобы получить длину (количество знаков) нашего искомого куска.
Т.е. если бы у нас кол-во календарных дней было <10, тогда порядковый номер первой буквы (к) слова "Календарных" был бы 6. Далее отнимая 5, мы бы получили единичную длину, что соответствует цифрам от 0 до 9. Также, по аналогии, и с 3 значными числами кол-ва дней отпуска.
Ну и не забывайте использовать команду "Вычислить формулу" на ленте. С помощью неё Вы всегда пошагово отследите работу формул и отловите ошибки в вычислениях.
Автор - Che79
Дата добавления - 15.02.2018 в 16:02
Che79 Дата: Четверг, 15.02.2018, 16:17 | Сообщение № 77
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
К сообщению #75
Код
="по "&ТЕКСТ(РАБДЕНЬ(E15;1);"ДД.ММ.ГГ")
Цитата Никанор, 15.02.2018 в 15:44, в сообщении № 75 ()
правильно или нет?
Скорее, нет, Вы праздники не учли.

И обратите внимание на комментарий модератора
Цитата Pelena, 12.02.2018 в 18:26, в сообщении № 46 ()
Никанор, Вы теперь все свои вопросы будете в одну тему складывать?

Делай нормально и будет нормально!
 
Ответить
СообщениеК сообщению #75
Код
="по "&ТЕКСТ(РАБДЕНЬ(E15;1);"ДД.ММ.ГГ")
Цитата Никанор, 15.02.2018 в 15:44, в сообщении № 75 ()
правильно или нет?
Скорее, нет, Вы праздники не учли.

И обратите внимание на комментарий модератора
Цитата Pelena, 12.02.2018 в 18:26, в сообщении № 46 ()
Никанор, Вы теперь все свои вопросы будете в одну тему складывать?
Автор - Che79
Дата добавления - 15.02.2018 в 16:17
Никанор Дата: Четверг, 15.02.2018, 16:27 | Сообщение № 78
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Я везде вместо F14 везде в формуле заменил на ПСТР(F14;4;ПОИСК("кал";F14)-5)
и получилась очень длинная формула:
Код
=E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5);$K$20:$AF$31;"<="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1))

вроде работает.
Правильно?
Огромное Вам спасибо за помощь в написании такой сложной формулы и объяснения!

Цитата Che79, 14.02.2018 в 19:12, в сообщении № 67 ()
Наверное, рано мне быть помогающим.....

А с этими Вашими высказываниями я категорически не согласен.
Это я наверное очень многого хочу от помогающих и от Excelя. Просто увидев возможности, хочется всё дальше усовершенствовать, улучшить и упростить.

Комментарий модератора учту.


Сообщение отредактировал Никанор - Четверг, 15.02.2018, 16:35
 
Ответить
СообщениеЯ везде вместо F14 везде в формуле заменил на ПСТР(F14;4;ПОИСК("кал";F14)-5)
и получилась очень длинная формула:
Код
=E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1)+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5);$K$20:$AF$31;"<="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1+СЧЁТЕСЛИМН($K$20:$AF$31;">="&E15;$K$20:$AF$31;"<="&E15+ ПСТР(F14;4;ПОИСК("кал";F14)-5)-1))

вроде работает.
Правильно?
Огромное Вам спасибо за помощь в написании такой сложной формулы и объяснения!

Цитата Che79, 14.02.2018 в 19:12, в сообщении № 67 ()
Наверное, рано мне быть помогающим.....

А с этими Вашими высказываниями я категорически не согласен.
Это я наверное очень многого хочу от помогающих и от Excelя. Просто увидев возможности, хочется всё дальше усовершенствовать, улучшить и упростить.

Комментарий модератора учту.
Автор - Никанор
Дата добавления - 15.02.2018 в 16:27
Мир MS Excel » Вопросы и решения » Вопросы по Excel » К дате прибавить ячейку, в которой формула с текстом (Формулы/Formulas)
  • Страница 4 из 4
  • «
  • 1
  • 2
  • 3
  • 4
Поиск:

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