Форум » Полезное » Excell. Некоторые тайны » Ответить
Excell. Некоторые тайны
Kikinda: Простые функции обсуждать не будет....но вот какие нибудь очень полезные и без которых не обойтись в нашем нелегком деле давно пора обнародовать и рассказать всем, кто о них еще не знает. 1) Делюсь своей находкой. Это функция "сцепить" или "расцепить". Применение. Например если у меня длинный список, который состоит только из данных по году (1998 1987 1956 и т.д.) а в соседней колонке месяц (01.06 01.12. 06.05. и т.д.), то я могу два столбца склеить, для этого =СЦЕПИТЬ(L18;J19) и два длинных столбца склеиваются и получается 01.06.1998 01.12.1987 06.05.1956 и т.д.) 2) Функция "расцепить". Примененение. Например, если у меня во всем списке есть какое то ненужное мне начало или окончание и мне от него надо избавиться... 1415-23 1213-25 1658 -26 то я делаю следующее =ПРАВСИМВ(L19;4) или =ЛЕВСИМВ(L19;4) где L19 - ссылка на ячейку и 4 - кол-во символов с пробелами, которые надо откусить.
Kikinda: В Экселе есть замечательная функция, которая называется ВПР. Ее очень любит наш уважаемый Кот (как большой специалист огромных списков), ну и я тоже люблю, поскольку неравнодушна ко всему, что может значительно упростить тяжелый оценочный труд. При помощи функции ВПР можно совместить данные двух таблиц по какому то опознавательному признаку, например, по инвентарному номеру. Это бывает нужно для ситуации, когда есть два списка, данные которых проставлены в разном порядке. Например, в первом списке, у меня Станок 1К62, инв 3, Станок 1М62 инв 10, Пресс гибочный инв 32....А во втором списке дан год для каждого инвентарного номера без указания маркировки... инв 10 1962, инв 3 1973, инв 10 1985 Для того, чтобы быстро совместить два списка я 1. Открываю два файла с требуемыми таблицами. Нажимаю ВСТАВКА - ФУНКЦИЯ - ВПР 2. Искомое значение - выделяю столбец по которому я буду искать в другой таблице. Таблица - выделяю таблицу в которой надо найти искомое значение. Первый столбец в выделяемой таблице должен иметь опознавательные символы по которым я ищу. 3. Номер столбца - отсчитываю в каком по номеру столбце у меня стоит искомое значение. 4. Интервальный просмотр. Вписываю слово ЛОЖЬ 5. Закревляю табличный диапазон в ячейке "Таблица" знаками $$. Если не закрепить, то при копировании этой функции табличка будет смещаться с требуемого диапазона. 5. Копирую функцию на всю таблицу.
AMar: По поводу больших таблиц: Рекомендую также ознакомиться со следующими полезными функциями: СУММЕСЛИ ИНДЕКС СМЕЩ ЗЫ: кроме ВПР есть еще ГПР...
Kikinda: AMar А подробнее? Я с этими чудесами техники , к сожалению, еще не знакома.
AMar: СУММЕСЛИ Есть список объектов, у каждого объекта прописан тип/группа (например, задания, сооружения,...). Все объекты в перемешку. Фукнция позволяет рассчитать суммы какого-то показателя по отдельной группе. ИНДЕКС СМЕЩ - удобно использовать для формирования таблиц в отчет. ГПР то же, что и ВПР, только находит не срочку, к колонку...
Мисовец: Иногда в больших таблицах, в т.ч. используя функции ВПР, ГПР и другие мы на выходе получаем #Н/Д - т.е. сообщение Экселя о том, что для данной ячейки формула ничего не нашла. Тогда дальнейшие формулы, ссылающиеся на ячейку, в которой содержится #Н/Д также содержат #Н/Д. Это довольно неприятная вещь: например и суммы по таким колонкам будут #Н/Д. Но можно использовать логическую функцию ЕНД(Ячейка), которая будет ИСТИНА или 1, если в Ячейке стоит #Н/Д, ну и ЛОЖЬ или 0, если там всё в порядке: =ЕСЛИ(Ячейка="";"";ЕСЛИ(ЕНД(Ячейка);"";Ячейка*0,18)) - если ячейка пустая или в ней #Н/Д, то оставить новую ячейку пустой, а если не пустая, то посчитать НДС.
Kikinda: Я делаю проще. Фильтрую весь список по знаку #н/д и все такие знаки удаляю. Быстро и просто. Хотя при неожиданном нахождении значения в бывшей #н/д придется опять поводить манипуляции со списком. Функция ЕСЛИ возьму на вооружение.
Kikinda: Разницу в месяцах между двумя датами в формате ХХ ХХ ХХХХ можно посчитать по следующей формуле =(ГОД(M66)-ГОД(H66))*12+МЕСЯЦ(M66)-МЕСЯЦ(H66) Очень удобно! Предполагаю, что можно посчитать и разница в днях (но у меня пока такой необходимости не возникало).
AMar: Чтобы посчитать разницу в днях, достаточно просто посчитать разницу: =М66-Н66
Мисовец: AMar пишет: Чтобы посчитать разницу в дня, достаточно просто посчитать разницу: =М66-Н66 Угу, а чтобы её же иметь в месяцах, достаточно формулы: =(М66-Н66)*12/365,25 Такой точности оценщику хватит... :)
AMar: Мисовец пишет: Угу, а чтобы её же иметь в месяцах, достаточно формулы: =(М66-Н66)*12/365,25 Такой точности оценщику хватит... :) Я так понимаю, про то, как посчитать разницу в годах, озвучивать не надо... Про функции ГОД(), МЕСЯЦ() и т.п. Часто дата постановки на учет не совпадает с началом месяца. При этом бывает удобным привести все даты к началу месяца (например, чтобы индексы цеплять или группировать объекты). Удобно это выполнить таким образом: =ДАТА(ГОД(A1);МЕСЯЦ(A1);1) где: A1 - число в формате дата, например 26.06.07 Результат получим 01.06.07
Franck: Не удержался. Кто нибудь умеет пользовать функции ВПР или ГПР, когда нужно сделать выборку не по одному параметру, а по двум?, т. е. искомое находится на пересечении столбцов и строк. Вот один вариант решения проблемы, без ВПР. Есть еще с ВПР.
Kikinda: Я с функцией ЕСЛИ вообще не знакома :(. К своему величайшему стыду. Задачу, поставленную Франком решаю, обычно ВПР :) и фильтром. И еще меня интересует, как транспонировать столбцы в строки и наоборот? Я знаю, что такое возможно :). Но никак не найду в справочнике.
NPB: Оксана, это делается с помощью Копировать / Правка / Спец. вставка / Значения / Транспонировать
AMar: Franck пишет: Не удержался. Кто нибудь умеет пользовать функции ВПР или ГПР, когда нужно сделать выборку не по одному параметру, а по двум?, Например так: http://r.foto.radikal.ru/0706/a2/4cfc387ab477.bmp ЗЫ: Чего-то у меня не получилось картинку вставить...
Дмитрий: Kikinda пишет: И еще меня интересует, как транспонировать столбцы в строки и наоборот можно как предложили NPB пишет: Оксана, это делается с помощью Копировать / Правка / Спец. вставка / Значения / Транспонировать А можно и так: ТРАНСП Возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Функция ТРАНСП должна быть введена как формула массива в интервал, который имеет столько же строк и столбцов, сколько столбцов и строк имеет аргумент массив. Функция ТРАНСП используется для того, чтобы поменять ориентацию массива на рабочем листе с вертикальной на горизонтальную и наоборот. Разница в том, что в 1 случае получается массив значений, а во 2 - массив ссылок на ячейки
Franck: AMar пишет: Например так: Ага, неплохо. Я вот так сделал в первый раз, жуть как топорно. =ИНДЕКС(Лист1!$A$1:$H$12;ПОИСКПОЗ(ГОД(D2);Лист1!$A$1:$A$12;0);ПОИСКПОЗ(E2;Лист1!$A$1:$H$1;0)) А вот еще вариант:
AMar: Ну тогда еще один вариант:
Franck: AMar пишет: Ну тогда еще один вариант: Класс! Ну, с ВПР и ГПР вроде уже всё перебрали. Но, "Врагу не сдается наш гордый Варяг!" Ваш ход, профессор.
AMar: Franck пишет: Ваш ход, профессор. Ну, до профессора еще очень далеко... По поводу «Ваш ход», фантазия моя иссякла. Остались только извращенные варианты:
Franck: Копирайт не мой, фантазия тоже иссякла (Ваш вариант через ГПР и ВПР самый удачный, ИМХО): Если Год КС-8 КС-9 1997 5,2397 4,6328 1998 4,5209 3,9973 1999 3,7699 3,3333 описанная таблица находится в диапазоне A1:C4 A10 = имя объекта B10 = КС-9 C10 = 1997 D10 =СУММПРОИЗВ((B10=$B$1:$C$1)*(C10=$A$2:$A$4)*$B$2:$C$4) D10 - искомое значение Либо массив, вводится через Ctrl + Shift + Enter D10 {=СУММ(ЕСЛИ($B$1:$C$1=B11;ЕСЛИ($A$2:$A$4=C11;$B$2:$C$4*1;);))}
полная версия страницы