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

Вход

Регистрация

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

 

= Мир MS Excel/Конвертация зарплаты из каждые две недели в месячную - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Конвертация зарплаты из каждые две недели в месячную (Формулы/Formulas)
Конвертация зарплаты из каждые две недели в месячную
RMMMM Дата: Пятница, 30.01.2015, 20:02 | Сообщение № 21
Группа: Пользователи
Ранг: Участник
Сообщений: 93
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
krosav4ig, пересохранил, ошибка исчезла, но явно как то не так вставляю формулу, не работает

Файл прилагаю
К сообщению приложен файл: Book1-23-.xlsx (10.3 Kb)
 
Ответить
Сообщениеkrosav4ig, пересохранил, ошибка исчезла, но явно как то не так вставляю формулу, не работает

Файл прилагаю
Автор - RMMMM
Дата добавления - 30.01.2015 в 20:02
_Boroda_ Дата: Пятница, 30.01.2015, 20:42 | Сообщение № 22
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
RMMMM, самый простой способ сделать мою формулу рабочей - в ячейку А1 написать любое число, большее 43-х (например, 99) и спрятать его (или сделать белым цвет шрифта, или поставить формат ячейки ;;;, или любым другим способом).
Хотя, можно и формулу немного подправить
формула массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СУММПРОИЗВ(ЕСЛИОШИБКА(($C1:$C16="ps")*$B1:$B16*((КОНМЕСЯЦА($A1:$A16+2;-1)+1=H$3)*ТЕКСТ(ДЕНЬ($A1:$A16+2);"[>14]14;0")+(КОНМЕСЯЦА($A1:$A16-12;-1)+1=H$3)*($A1:$A16+2>КОНМЕСЯЦА(H$3;0))*(КОНМЕСЯЦА(H$3;0)-$A1:$A16+12));)/14)

а если
Цитата RMMMM, 30.01.2015 в 19:49, в сообщении № 19
Желательно если возможно все таки без Ctrl-Shift-Enter формул
, то можно обычную формулу
Код
=СУММПРОИЗВ(($C1:$C16="ps")*$B1:$B16*((КОНМЕСЯЦА(ТЕКСТ(-$A1:$A16;";0;99")+2;-1)+1=H$3)*ТЕКСТ(ДЕНЬ($A1:$A16+2);"[>14]14;0")+(КОНМЕСЯЦА(ТЕКСТ(-$A1:$A16;";0;99")-12;-1)+1=H$3)*($A1:$A16+2>КОНМЕСЯЦА(H$3;0))*(КОНМЕСЯЦА(H$3;0)-$A1:$A16+12))/14)
К сообщению приложен файл: Book1-22-1-3.xls (32.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеRMMMM, самый простой способ сделать мою формулу рабочей - в ячейку А1 написать любое число, большее 43-х (например, 99) и спрятать его (или сделать белым цвет шрифта, или поставить формат ячейки ;;;, или любым другим способом).
Хотя, можно и формулу немного подправить
формула массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СУММПРОИЗВ(ЕСЛИОШИБКА(($C1:$C16="ps")*$B1:$B16*((КОНМЕСЯЦА($A1:$A16+2;-1)+1=H$3)*ТЕКСТ(ДЕНЬ($A1:$A16+2);"[>14]14;0")+(КОНМЕСЯЦА($A1:$A16-12;-1)+1=H$3)*($A1:$A16+2>КОНМЕСЯЦА(H$3;0))*(КОНМЕСЯЦА(H$3;0)-$A1:$A16+12));)/14)

а если
Цитата RMMMM, 30.01.2015 в 19:49, в сообщении № 19
Желательно если возможно все таки без Ctrl-Shift-Enter формул
, то можно обычную формулу
Код
=СУММПРОИЗВ(($C1:$C16="ps")*$B1:$B16*((КОНМЕСЯЦА(ТЕКСТ(-$A1:$A16;";0;99")+2;-1)+1=H$3)*ТЕКСТ(ДЕНЬ($A1:$A16+2);"[>14]14;0")+(КОНМЕСЯЦА(ТЕКСТ(-$A1:$A16;";0;99")-12;-1)+1=H$3)*($A1:$A16+2>КОНМЕСЯЦА(H$3;0))*(КОНМЕСЯЦА(H$3;0)-$A1:$A16+12))/14)
Автор - _Boroda_
Дата добавления - 30.01.2015 в 20:42
krosav4ig Дата: Пятница, 30.01.2015, 20:51 | Сообщение № 23
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
формулы исправил, добавил динамические ссылки, но у вас по-моему есть ошибки в исходных данных (выделил красным), в одном месте (в 7 строке) неправильный день недели и нечетный номер недели (если вы платите со второй недели через две, то должна быть четная) из-за этого формула Александра (_Boroda_) некорректно считает значение из этой строки, причем в два месяца. В другом месте тоже неправильный день недели и нечетный номер недели, в обоих местах стоит пометка ps

прочитайте мой предыдущий пост, вы могли его не заметить
К сообщению приложен файл: Book1-23-1.xlsx (10.7 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Пятница, 30.01.2015, 20:53
 
Ответить
Сообщениеформулы исправил, добавил динамические ссылки, но у вас по-моему есть ошибки в исходных данных (выделил красным), в одном месте (в 7 строке) неправильный день недели и нечетный номер недели (если вы платите со второй недели через две, то должна быть четная) из-за этого формула Александра (_Boroda_) некорректно считает значение из этой строки, причем в два месяца. В другом месте тоже неправильный день недели и нечетный номер недели, в обоих местах стоит пометка ps

прочитайте мой предыдущий пост, вы могли его не заметить
Автор - krosav4ig
Дата добавления - 30.01.2015 в 20:51
RMMMM Дата: Пятница, 30.01.2015, 21:46 | Сообщение № 24
Группа: Пользователи
Ранг: Участник
Сообщений: 93
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, спасибо, то что нужно. Правда теперь при добавлении строчки лаг примерно 2 секунды (всего строчек у меня 1720, вроде не так и много), но вроде жить можно.

krosav4ig, спасибо, на примере работает.
Насчет Вашего предудущего поста на самом деле пропустил его часть
Цитата krosav4ig
(у меня считается сумма за дни месяца предшествующие дню оплаты)

Нужно считать не совсем так, а за дни месяца предшествующие дню оплаты плюс 2 дня. Подробно в посте №6

Цитата krosav4ig
но у вас по-моему есть ошибки в исходных данных (выделил красным)

Это не совсем ошибки, в последней строке добавлена дата наобум за прошлый год, чтобы показать при каких условиях формула Pelen-ы начинает считать некорректно, а 7-я строка раньше была бонусом, а потом перемаркирована в ps. Процитирую себя из поста 19:
Цитата RMMMM
Вообще формулы _Boroda_ и Pelena способны считать не только каждые две недели, но и промежуточные значения (всякие бонусы и прочее, если флаг поменять, то считает корректно, что даже удобнее)


Спасибо всем, почерпнул много нового.
 
Ответить
Сообщение_Boroda_, спасибо, то что нужно. Правда теперь при добавлении строчки лаг примерно 2 секунды (всего строчек у меня 1720, вроде не так и много), но вроде жить можно.

krosav4ig, спасибо, на примере работает.
Насчет Вашего предудущего поста на самом деле пропустил его часть
Цитата krosav4ig
(у меня считается сумма за дни месяца предшествующие дню оплаты)

Нужно считать не совсем так, а за дни месяца предшествующие дню оплаты плюс 2 дня. Подробно в посте №6

Цитата krosav4ig
но у вас по-моему есть ошибки в исходных данных (выделил красным)

Это не совсем ошибки, в последней строке добавлена дата наобум за прошлый год, чтобы показать при каких условиях формула Pelen-ы начинает считать некорректно, а 7-я строка раньше была бонусом, а потом перемаркирована в ps. Процитирую себя из поста 19:
Цитата RMMMM
Вообще формулы _Boroda_ и Pelena способны считать не только каждые две недели, но и промежуточные значения (всякие бонусы и прочее, если флаг поменять, то считает корректно, что даже удобнее)


Спасибо всем, почерпнул много нового.
Автор - RMMMM
Дата добавления - 30.01.2015 в 21:46
krosav4ig Дата: Суббота, 31.01.2015, 20:18 | Сообщение № 25
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
еще раз перечитал 6 пост, исправил свою формулу
Код
=ЕСЛИОШИБКА(СУММ((ЕСЛИОШИБКА(ВПР(ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1))+14;$A$2:$B$16;2;)*(17-G$3+ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1)))/14;)+ЕСЛИОШИБКА(ВПР(ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1))+28;$A$2:$B$16;2;);)+ЕСЛИОШИБКА(ВПР(ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1))+42;$A$2:$B$16;2;)*(КОНМЕСЯЦА(G$3;0)-30-ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1)))/14;))^{0;1})-1;"нет данных")

добавил формулу для бонусов и % (Массивная, вводится по Ctrl-Shift-Enter)
Код
=ЕСЛИОШИБКА(СУММ(СУММ((ЕСЛИ(ЕНД(ПОИСКПОЗ($A$2:$A$16;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1);))*((ДЕНЬНЕД($A$2:$A$16)<>6)+(ЕНЕЧЁТ(НОМНЕДЕЛИ(--$A$2:$A$16))));$A$2:$A$16;)>=G$3)*(ЕСЛИ(ЕНД(ПОИСКПОЗ($A$2:$A$16;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1);))*((ДЕНЬНЕД($A$2:$A$16)<>6)+(ЕНЕЧЁТ(НОМНЕДЕЛИ(--$A$2:$A$16))));$A$2:$A$16;)<=КОНМЕСЯЦА(G$3;0))*(($C$2:$C$16="%")*$B$3:$B$17*$B$2:$B$16%+($C$2:$C$16="bonus")*$B$2:$B$16))^{0;1})-1;"")


UPD.
добавил еще 1 файл, в нем добавил имена
К сообщению приложен файл: Book1-22-2-.xlsx (17.1 Kb) · Book1-22-3-.xlsx (16.8 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Суббота, 31.01.2015, 22:12
 
Ответить
Сообщениееще раз перечитал 6 пост, исправил свою формулу
Код
=ЕСЛИОШИБКА(СУММ((ЕСЛИОШИБКА(ВПР(ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1))+14;$A$2:$B$16;2;)*(17-G$3+ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1)))/14;)+ЕСЛИОШИБКА(ВПР(ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1))+28;$A$2:$B$16;2;);)+ЕСЛИОШИБКА(ВПР(ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1))+42;$A$2:$B$16;2;)*(КОНМЕСЯЦА(G$3;0)-30-ПРОСМОТР(G$3;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1)))/14;))^{0;1})-1;"нет данных")

добавил формулу для бонусов и % (Массивная, вводится по Ctrl-Shift-Enter)
Код
=ЕСЛИОШИБКА(СУММ(СУММ((ЕСЛИ(ЕНД(ПОИСКПОЗ($A$2:$A$16;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1);))*((ДЕНЬНЕД($A$2:$A$16)<>6)+(ЕНЕЧЁТ(НОМНЕДЕЛИ(--$A$2:$A$16))));$A$2:$A$16;)>=G$3)*(ЕСЛИ(ЕНД(ПОИСКПОЗ($A$2:$A$16;ЦЕЛОЕ(РАЗНДАТ(6;КОНМЕСЯЦА(ДАТАМЕС(G$3;-МЕСЯЦ(G$3));0);"d")/7)*7+6+14*(СТРОКА($1:$28)-1);))*((ДЕНЬНЕД($A$2:$A$16)<>6)+(ЕНЕЧЁТ(НОМНЕДЕЛИ(--$A$2:$A$16))));$A$2:$A$16;)<=КОНМЕСЯЦА(G$3;0))*(($C$2:$C$16="%")*$B$3:$B$17*$B$2:$B$16%+($C$2:$C$16="bonus")*$B$2:$B$16))^{0;1})-1;"")


UPD.
добавил еще 1 файл, в нем добавил имена
Автор - krosav4ig
Дата добавления - 31.01.2015 в 20:18
RMMMM Дата: Понедельник, 02.02.2015, 16:17 | Сообщение № 26
Группа: Пользователи
Ранг: Участник
Сообщений: 93
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
krosav4ig, спасибо, плюсик в репутацию добавил. Просто массивы не очень удобны, когда добавляешь строчки по одной постоянно - формулу нельзя "протянуть", ее каждый раз нужно перевводить через Ctrl-Shift-Enter. Или можно как то по другому?
 
Ответить
Сообщениеkrosav4ig, спасибо, плюсик в репутацию добавил. Просто массивы не очень удобны, когда добавляешь строчки по одной постоянно - формулу нельзя "протянуть", ее каждый раз нужно перевводить через Ctrl-Shift-Enter. Или можно как то по другому?
Автор - RMMMM
Дата добавления - 02.02.2015 в 16:17
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Конвертация зарплаты из каждые две недели в месячную (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

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