Нужно написать формулу в итоговую таблицу(сводную) Мозг ломаю уже второй день. Пол таблицы сделал (подсмотрел в другом файле)) А вот самую главную не могу( она там нужна практически одна по всей таблице, только название ячеек менять) Место под формулу залито красным.
Грубо говоря нужно с Первого листа фаила "Ведомость общая" вытащить на "Итого деревьев" данные о кол-ве растений по категориям состояния (в категории стоит "+" либо есть Численное значение той-же самой категории(от 1 до 6) И нужно учитывать что в столбце где написанны штуки (растений) может быть не только 1(единица)и нужно делать привязку к столбцу N6 (тип посадки)...........
Блин как смог объяснил.........
Может кто примерно знает как это сделать ГОРЮ СИНИМ ПЛАМЕНЕМ в
понедельник сдавать
Ссылка на фаил (яндекс диск)
не совсем понял что надо, уже сделал бы, но если то что я думаю, то открываешь то что выделено красным, ставишь " равно", дальше вводишь листая листы данные из соответствующих колонок. потом перетягиваешь формулу ниже по строке, единственное " + " считать не будет, надо 0 или другое значение, если не надо что бы эта колонка имела значение суммируемое ставьте ноль в шрифте " broadway " в 12 размере будет считаться как ноль, а выглядит как значок
Нужно примерно следующее: Какое кол-во деревьев-конкретного вида допустим "Березы", определенного типа посадки допустим "Одиночное" находится в определенном состоянии (там где "+", есть численное значение категории состояния от 1 до 6) Нужно это для того чтобы потом из сводной "Ведомости" удаляя ненужные деревья определить состояние деревьев на каждом "квартале и выделе". Вводить каждое значение вручную не реально там больше 25 тысяч деревьев.
А считать каждую ведомость для каждого квартала и выдела отдельно это на неделю не меньше.
Ну можно фильтрацией выбирать нужные категории деревьев, а сумму получать функцией "промежуточные итоги". Хотя, по моему, проще скинуть в отдельные столбцы не признаки категорий а сами численные значения. Тогда вообще всё в лоб можно посчитать.
Если правильно понял, то
1) установить Excel 2007/2010/2013
2) на листе "Итого деревьев" в столбце E заменить слово "одиночное" на слово "одиночная" (т.к. на листе "Ведомость общая" используется слово "одиночная", а не "одиночное"
3) на листе "Итого деревьев" в ячейку J14 вставить форумул
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!F7:$F$10000;E14;'Ведомость общая'!U7:$U$10000;">0")
P.S. Совет. Профессионалы по Excel сидят тут http://www.planetaexcel.ru/for...NAME=list&FID=1
но если вы выложите туда файл с 9000 строк - этот файл удалят. Т.к. чтобы написать формулу под ваш файл, в нём хватит и 20 строк.
На указаном мной форуме, файл должен весить не более 100 кб, а не 3,17мб, как у вас.
Pavel_Crio
Если правильно понял, то1) установить Excel 2007/2010/2013
2) на листе "Итого деревьев" в столбце E заменить слово "одиночное" на слово "одиночная" (т.к. на листе "Ведомость общая" используется слово "одиночная", а не "одиночное"
3) на листе "Итого деревьев" в ячейку J14 вставить форумул[b]=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$9061;'Итого деревьев'!D14;'Ведомость общая'!F7:$F$9061;'Итого деревьев'!E14;'Ведомость общая'!U7:$U$9061;">0")
[/B]
Не получается по факту их 712, а у вас получается 850
1) Я открываю лист "Ведомость общая"
2) cтавлю фильтр в столбце H - "Береза бородавчатая"
3) cтавлю фильтр в столбце F - "одиночная"
4) ставлю фильтр в столбце U - "непустые" (т.е. 1..6 )
5) считаю отлильтрованные строки - 850. Формула выдаёт 850.
Почему должно получится 712 ? Может я в столцбе U неправильно фильтрую? Может не все значения (1,2,3,4,5,6) нужно брать?
Сейчас формула считать все строки, где значения больше 0 в столбце U
Вы формулу смотрели? смысл поняли? Сами под свои реальные кретерии сможете дописать?
Какие столбцы по каким кретериям нужно отфильтровать, чтобы получить 712 ?
Проверил элементарно :
Сделал фильтр "береза бородавчатьая" потом
"Одиночное"
потом "+" в столбце "без признаков ослабления" получилось 712 штук
Без признаков ослабления = "1"
А в чём проблема переписать мою формулу под это условие?
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!F7:$F$10000;E14;'Ведомость общая'!O7:$O$10000;"+")
Вот вам 712
P.S. На всякий случай вырезка из Справки по СЧЁТЕСЛИМН
СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2] 😊
В моей формуле 3 диапазона и 3 условия, каждое из них разделено точкой с запятой
(первый_диапазон; условлие_для_диапазона_1; второй_диапазон; условлие_для_диапазона_2; третий_диапазон; условлие_для_диапазона_3)
Pavel_Crio
А в чём проблема переписать мою формулу под это условие?[b]=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!F7:$F$10000;E14;'Ведомость общая'!O7:$O$10000;"+")
[/B]
проблем может и нет только - как это сделать ( главное чтобы удаляя из общей ведомости пересчитывались "Итого деревьев" и "Итого кустарников")
Pavel_Crio
А в чём проблема переписать мою формулу под это условие?[b]=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!F7:$F$10000;E14;'Ведомость общая'!O7:$O$10000;"+")
[/B]
Вставил формулу - получилось "0"
Файл сохранил в формате *.xlsx
Залил сюда http://webfile.ru/6443409
нажмите там справа - скачать напрямую
Ваше предложение - главное чтобы удаляя из общей ведомости пересчитывались "Итого деревьев" и "Итого кустарников"
я не понял. Где удалить? Где эти ячейки "Итого деревьев", Итого кустарников? Искал на 3-х листах и не нашёл.
Вы просили формулу - я вам её написал, формула показывает 712.
Объясните, что и где вы хотите удалить?
P.S. Извините, но я спать. Завтра с работы почитаю ваш ответ
я в экселе как свинья в апельсинах.......
В экселе в листе "Ведомость общая" перечислены все деревья находящиеся на объекте. Первые два столбца "Квартал" и "выдел". Я должен отдать заказчику файлы отдельные на каждый "квартал" и каждый "выдел" данного объекта. Чтобы там были перечислены все деревья находящиеся там и их состояние ( по 6-ти критериям)
"Итого деревьев" "Итого кустарников" это листы в фаиле - сводные, в которые и нужно вставить формулу чтобы посчитать кол-во кустов и деревьев на объекте (в определенном состоянии)
Т.е. у вас должно получиться "дофига" Excel файлов, с такой разбивкой:
1 файл: 1-й квартал, выдел 16
2 файл: 1-й квартал, выдел 17
....
....
100-й файл: 9-й квартал, выдел 7
мильонный файл: квартал "-", выдел "-"
Так?
А ну да, Итого деревьев и кустарников - это листы.
Да, введёте мою формулу и удаляйте что хотите с листа "Ведомость общая" - всё будет пересчитываться (если у вас включен автоматический пересчёт формул, если он выключен, то включите его в настройках или нажмите кнопку F9)
Pavel_Crio
А ну да, Итого деревьев и кустарников - это листы.
Да, введёте мою формулу и удаляйте что хотите с листа "Ведомость общая" - всё будет пересчитываться (если у вас включен автоматический пересчёт формул, если он выключен, то включите его в настройках или нажмите кнопку F9)
Дело в том что подобную формулу нужно вводить по всем пустым ячейкам листов "итого"деревьев" "итого кустарников".
Записывая формулу в остальные ячейки получаю черти что( не считается она блин,) короче не понимаю я в этом ничерта лох "я" и все тут, как газон правильно посадить знаю, а здесь.......
Введите в J14 вот эту формулу и протяните её ниже
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!$F$7:$F$10000;E14;'Ведомость общая'!$O$7:$O$10000;"+")
Pavel_Crio
Введите в J14 вот эту формулу и протяните её ниже[b]=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!$F$7:$F$10000;E14;'Ведомость общая'!$O$7:$O$10000;"+")
[/B]
Спасибо за помощь. Частично это решило проблему. (просто удаляя из ведомости 4 дерева из итоговой удаляется почему-то 2, и формула не подходит для остальных столбцов , но разбираюсь потихоньку)
Еще раз спасибо
Вот формулы для остальных ячеек:
Для ячейки K14:
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!$F$7:$F$10000;E14;'Ведомость общая'!$P$7:$P$10000;"+")
для ячейки L14:
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!$F$7:$F$10000;E14;'Ведомость общая'!$Q$7:$Q$10000;"+")
для ячейки M14:
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!$F$7:$F$10000;E14;'Ведомость общая'!$R$7:$R$10000;"+")
для ячейки N14:
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!$F$7:$F$10000;E14;'Ведомость общая'!$S$7:$S$10000;"+")
для ячейки O14:
=СЧЁТЕСЛИМН('Ведомость общая'!$H$7:$H$10000;D14;'Ведомость общая'!$F$7:$F$10000;E14;'Ведомость общая'!$T$7:$T$10000;"+")
А поповоду того, что удаляете 4 дерева в ведомости, а в итоговой удаляется 2 - давайте посмотрим. Скажите какие строки (или данные) вы удаляете в ведомости, а я посмотрю почему удаляется 2 в итоговой.
По-идее, формула должна показать другое значение только в том случае, если вы удалите строки (данные) на листе ведомости, которые соответствуют 3-м критериям:
- в столбце F написано слово "Одиночная"
- в столбце H написано "Береза бородавчатая"
- в столбце, например, О стоит "+"
вот тогда формула обязательно должна пересчитаться.
А если же вы удалили Берёзу бородавчатаю, но она была не одиночная или в столбце О не было плюса, то формула не изменится, а как показывала 712, так и будет показывать. Т.к. эти строки не подходят под указанные критерии
С этим я разобрался (это была ошибка фаила)
Только данная формула для деревьев в группах не подходит(там в 9ом столбце нужно еще и кол-во в штуках учитывать).
Для одиночных считается (грубо говоря) кол-во строк в которых встречается "+" ,но т.к. в столбце N9 стоит Единица напротив одиночных то пофигу что строки что штуки все сходится.
получается для групп нужно чтобы результат обсчета данной формулы умножался на кол-во деревьев из 9 столбца "выделенных строк из диапазона отвечающих условию выборки"(о загнул)
Что-то я не очень понял, как считать деревья в группах.
Расскажите на примере, что нужно отфильтровать на листе Ведомость общая, чтобы получить результат для берёзы бородавчатой с типом постадки "группа" ?
Может что-нибудь придумаем
P.S. Вы на примерах объяняйте, я же не работаю с вами в одном отделе, для меня все эти деревья, посадки и т.д. вообще ничего не значат. Вы скажите, вот фильтруем такой столбец по такому критерию, другой по другому критерию и считамем там-то, ответ должен получиться такой-то... так будет легче понять
ОК
Если:
Делаем выборку по столбцам -- N6 Тип насаждения "группа", N8 Вид растения"Береза бородавчатая", N15 "Состояние хор. без признаков ослабления" "+" то в столбце N9 мы увидим кол-во растений в группах.
Дело в том что в Ведомости в одной строке при "Типе насаждения"-"группа" или "рядовая посадка" или "живая изгородь" в кол-ве можно ставить сразу несколько растений.
Тоесть номер по ведомости один, а растений на этом номере много.
Так, поставил вот эти фильтры
N6 Тип насаждения "группа", N8 Вид растения"Береза бородавчатая", N15 "Состояние хор. без признаков ослабления" "+" то в столбце N9 мы увидим кол-во растений в группах.
у меня получилось 294 строки. Правильно?
или вам нужна сумма по 9-му столбцу, которая составляет 1939 ?
вам число строк нужно или сумма по 9 столбцу?
Сумма по 9 му столбцу
Вот формула для группы (ячейка J15)
=СУММПРОИЗВ(('Ведомость общая'!$F$7:$F$10000=E15)*('Ведомость общая'!$H$7:$H$10000=D15)*('Ведомость общая'!$O$7:$O$10000="+")*'Ведомость общая'!$I$7:$I$10000)
P.S. Соответственно, на листе итого деревьев можно поставить Автофильтр, отфильтровать сперва по столбцу E по значению "Одиночная" и протянуть формулы для "одиночной" посадки. А потом можно отфильтровать групповые посадки и протянуть формулы для групповых
Вот файл с формулами http://webfile.ru/6444225
спасибо