Отправлено: 01.04.07 15:40. Заголовок: Excell. Некоторые тайны

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

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 - кол-во символов с пробелами, которые надо откусить.

 Отправлено: 19.04.07 11:15. Заголовок: Re:

В Экселе есть замечательная функция, которая называется ВПР. Ее очень любит наш уважаемый Кот (как большой специалист огромных списков), ну и я тоже люблю, поскольку неравнодушна ко всему, что может значительно упростить тяжелый оценочный труд.

При помощи функции ВПР можно совместить данные двух таблиц по какому то опознавательному признаку, например, по инвентарному номеру. Это бывает нужно для ситуации, когда есть два списка, данные которых проставлены в разном порядке. Например, в первом списке, у меня Станок 1К62, инв 3, Станок 1М62 инв 10, Пресс гибочный инв 32....А во втором списке дан год для каждого инвентарного номера без указания маркировки... инв 10 1962, инв 3 1973, инв 10 1985

Для того, чтобы быстро совместить два списка я
1. Открываю два файла с требуемыми таблицами. Нажимаю ВСТАВКА - ФУНКЦИЯ - ВПР
2. Искомое значение - выделяю столбец по которому я буду искать в другой таблице.
Таблица - выделяю таблицу в которой надо найти искомое значение. Первый столбец в выделяемой таблице должен иметь опознавательные символы по которым я ищу.
3. Номер столбца - отсчитываю в каком по номеру столбце у меня стоит искомое значение.
4. Интервальный просмотр. Вписываю слово ЛОЖЬ 5. Закревляю табличный диапазон в ячейке "Таблица" знаками $$. Если не закрепить, то при копировании этой функции табличка будет смещаться с требуемого диапазона.
5. Копирую функцию на всю таблицу.

 Отправлено: 19.04.07 12:18. Заголовок: Re:

По поводу больших таблиц:
Рекомендую также ознакомиться со следующими полезными функциями:
СУММЕСЛИ
ИНДЕКС
СМЕЩ

ЗЫ: кроме ВПР есть еще ГПР...

 Отправлено: 19.04.07 12:33. Заголовок: Re:

AMar

А подробнее?
Я с этими чудесами техники , к сожалению, еще не знакома.

 Отправлено: 19.04.07 13:29. Заголовок: Re:

СУММЕСЛИ
Есть список объектов, у каждого объекта прописан тип/группа (например, задания, сооружения,...). Все объекты в перемешку.
Фукнция позволяет рассчитать суммы какого-то показателя по отдельной группе.

ИНДЕКС
СМЕЩ
- удобно использовать для формирования таблиц в отчет.

ГПР
то же, что и ВПР, только находит не срочку, к колонку...

 Отправлено: 20.04.07 04:37. Заголовок: Re:

Иногда в больших таблицах, в т.ч. используя функции ВПР, ГПР и другие мы на выходе получаем #Н/Д - т.е. сообщение Экселя о том, что для данной ячейки формула ничего не нашла. Тогда дальнейшие формулы, ссылающиеся на ячейку, в которой содержится #Н/Д также содержат #Н/Д. Это довольно неприятная вещь: например и суммы по таким колонкам будут #Н/Д. Но можно использовать логическую функцию ЕНД(Ячейка), которая будет ИСТИНА или 1, если в Ячейке стоит #Н/Д, ну и ЛОЖЬ или 0, если там всё в порядке:
=ЕСЛИ(Ячейка="";"";ЕСЛИ(ЕНД(Ячейка);"";Ячейка*0,18)) - если ячейка пустая или в ней #Н/Д, то оставить новую ячейку пустой, а если не пустая, то посчитать НДС.

 Отправлено: 20.04.07 10:04. Заголовок: Re:

Я делаю проще. Фильтрую весь список по знаку #н/д и все такие знаки удаляю. Быстро и просто.
Хотя при неожиданном нахождении значения в бывшей #н/д придется опять поводить манипуляции со списком. Функция ЕСЛИ возьму на вооружение.

 Отправлено: 22.06.07 20:49. Заголовок: Re:

Разницу в месяцах между двумя датами в формате ХХ ХХ ХХХХ можно посчитать по следующей формуле
=(ГОД(M66)-ГОД(H66))*12+МЕСЯЦ(M66)-МЕСЯЦ(H66)

Очень удобно!

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

 Отправлено: 22.06.07 22:33. Заголовок: Re:

Чтобы посчитать разницу в днях, достаточно просто посчитать разницу:
=М66-Н66

 Отправлено: 25.06.07 04:39. Заголовок: Re:

AMar пишет:

цитата:

Чтобы посчитать разницу в дня, достаточно просто посчитать разницу: =М66-Н66

Угу, а чтобы её же иметь в месяцах, достаточно формулы: =(М66-Н66)*12/365,25 Такой точности оценщику хватит... :)

 Отправлено: 25.06.07 17:16. Заголовок: Re:

Мисовец пишет:

цитата:

Угу, а чтобы её же иметь в месяцах, достаточно формулы: =(М66-Н66)*12/365,25 Такой точности оценщику хватит... :)

Я так понимаю, про то, как посчитать разницу в годах, озвучивать не надо...

Про функции ГОД(), МЕСЯЦ() и т.п.
Часто дата постановки на учет не совпадает с началом месяца. При этом бывает удобным привести все даты к началу месяца (например, чтобы индексы цеплять или группировать объекты).
Удобно это выполнить таким образом:
=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)
где: A1 - число в формате дата, например 26.06.07
Результат получим 01.06.07

 Отправлено: 27.06.07 09:53. Заголовок: Re:

Не удержался. Кто нибудь умеет пользовать функции ВПР или ГПР, когда нужно сделать выборку не по одному параметру, а по двум?, т. е. искомое находится на пересечении столбцов и строк.
Вот один вариант решения проблемы, без ВПР. Есть еще с ВПР.

 Отправлено: 27.06.07 10:04. Заголовок: Re:

Я с функцией ЕСЛИ вообще не знакома :(. К своему величайшему стыду.
Задачу, поставленную Франком решаю, обычно ВПР :) и фильтром.

И еще меня интересует, как транспонировать столбцы в строки и наоборот? Я знаю, что такое возможно :).
Но никак не найду в справочнике.

 Отправлено: 27.06.07 10:16. Заголовок: Re:

Оксана, это делается с помощью Копировать / Правка / Спец. вставка / Значения / Транспонировать

 Отправлено: 27.06.07 13:01. Заголовок: Re:

Franck пишет:

цитата:

Не удержался. Кто нибудь умеет пользовать функции ВПР или ГПР, когда нужно сделать выборку не по одному параметру, а по двум?,

Например так:
http://r.foto.radikal.ru/0706/a2/4cfc387ab477.bmp

ЗЫ: Чего-то у меня не получилось картинку вставить...

 Отправлено: 27.06.07 14:06. Заголовок: Re:

Kikinda пишет:

цитата:

И еще меня интересует, как транспонировать столбцы в строки и наоборот

можно как предложили
NPB пишет:

цитата:

Оксана, это делается с помощью Копировать / Правка / Спец. вставка / Значения / Транспонировать

А можно и так:
ТРАНСП
Возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Функция ТРАНСП должна быть введена как формула массива в интервал, который имеет столько же строк и столбцов, сколько столбцов и строк имеет аргумент массив. Функция ТРАНСП используется для того, чтобы поменять ориентацию массива на рабочем листе с вертикальной на горизонтальную и наоборот.

Разница в том, что в 1 случае получается массив значений, а во 2 - массив ссылок на ячейки

 Отправлено: 27.06.07 19:12. Заголовок: Re:

AMar пишет:

цитата:

Например так:

Ага, неплохо. Я вот так сделал в первый раз, жуть как топорно.

=ИНДЕКС(Лист1!$A$1:$H$12;ПОИСКПОЗ(ГОД(D2);Лист1!$A$1:$A$12;0);ПОИСКПОЗ(E2;Лист1!$A$1:$H$1;0))

А вот еще вариант:

 Отправлено: 28.06.07 08:43. Заголовок: Re:

Ну тогда еще один вариант:

 Отправлено: 28.06.07 09:21. Заголовок: Re:

AMar пишет:

цитата:

Ну тогда еще один вариант:

Класс!
Ну, с ВПР и ГПР вроде уже всё перебрали.
Но, "Врагу не сдается наш гордый Варяг!"

 Отправлено: 28.06.07 11:34. Заголовок: Re:

Franck пишет:

цитата:

Ваш ход, профессор.

Ну, до профессора еще очень далеко...

 Отправлено: 28.06.07 14:38. Заголовок: Re:

Копирайт не мой, фантазия тоже иссякла (Ваш вариант через ГПР и ВПР самый удачный, ИМХО):

Если
Год КС-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;);))}