Excell. Некоторые тайны

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

Ответов - 132, стр: 1 2 3 4 5 6 7 All

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;);))}

avg: Вот вопрос еще один, насколько я понял он либо вообще не решается или решается с помощью средств макросов или программировния визиал. Очень часто есть прайсы, в формате ексель, на многих листах - как там организовать поиск? так, чтобы не открывать каждый лист в отдельности. (Сразу скажу, очень часто по марке прибора или оборудования, не знаешь в какой раздел, его поместили авторы прайса, и приходиться по все листам искать- очень много времени занимает)

AMar: avg пишет: на многих листах - как там организовать поиск? Стандартными средствами Excel. Выбираем Правка-Найти (или Ctrl-F), в открывающемся меню нажимаем кнопку параметры, в списке "Искать" выбираем "в книге"...

Kikinda: avg Сохранить в Моих документах, потом стандартным поиском из меню пуск ищем нужное слово. Но самое главное - поиск навести на нужную папку, иначе будет очень долго.

Дмитрий: Kikinda пишет: потом стандартным поиском Лучше использовать Яндекс-Дескотоп. Ищет тщательнее т быстрее.

Yurik: Мы используем специализированные программные продукты типа Cros или Архивариус3000 Первый продукт тхорош тем, что позволяет осуществлять поиск по результатам для уточнения выборки, плох тем, что не понимает падежей, склонений, спряжений и прочих правил русского языка. Второй продукт это все понимает, но поиск по результатм не производит. Плюс и того и другого - высокая скорость обработки данных (на процессоре Core Duo 5200+ поиск по массиву размером около 4 гиг занимает менее 2 сек при условии предварительного индексирования данных)

Kikinda: Yurik А где их можно добыть? Хорошие показатели по скорости...лучше чем стандартный поиск. В принципе, я не забываю где и что у меня лежит...но иногда все таки и мне нужны такие программы.

Yurik: Демоверсию Cros можно взять на официальном сайте компании Кронос http://www.cronos.ru/products-demo.html Остальное здесь - http://slil.ru/25193411

Kikinda: Табличный диапазон ячейки можно быстро закрепить знаками $$, просто выделив мышкой нужное значение и нажав F4! Гораздо быстрее, чем переводить клаву в другой реестр, а потом писать значки $$ вручную.

Kikinda: Потихоньку учу горячие клавиши. Оказывается, что это очень удобно. Делюсь последними достижениями: Включить (выключить) фильтр: CTRL+SHIFT+L Выделить до первой пустой ячейки вправо: CTRL+SHIFT+ Стрелка влево Перейти на другой лист: CTRL+PageUP(PageDN) Нашла в инете очень полезную таблицу, распечатала повесила на стену...скоро вообще не буду меню открывать, буду все делать горячими клавишами http://sizop.my1.ru/forum/10-64-1

Дмитрий: Просто молодеж привыкла мышкой щелкать. А те кто еще на ДВК работал, те привыкли клавиатурой пользоваться - горячими клавишами. А выделать диапазоны до первой пустой или первой заполненой ячейки лучьше через кнопку End и нужную стрелку (вправо, влево вниз вверх)

Игорь г. Львов: Kikinda пишет: Включить (выключить) фильтр: CTRL+SHIFT+L Попробовал. У меня не работает. Exel-2003.

Kikinda: У меня вроде все работает. Существенно увеличивается скорость выполнения работы. Кстати выделить всю таблицу можно гораздо проще = нажать CTRL+A. Выйти в окно с функциями = SHIFT + F3. Закрыть окно = CTRL + W. Я прям сегодня наслаждалась скоростью. Скоро придется мазать клавиатуру скипидаром. А кто нибудь знает как связать word и excel?

Мисовец: Kikinda пишет: А кто нибудь знает как связать word и excel? Да всегда кто-нибудь знает... Идем в Word, Меню: Вставка/Объект/Лист Microsoft Excel Появляется встроенный лист, в котором можно даже создавать обычным способом другие листы, туда можно из нормального Excel копировать формулы, форматы, в общем, что хочешь... Потом его за углолок можно мышей масштабировать по разному. Входишь в него двумя кликами - обычный Excel, только маленький, ткнул мышей в пустое место листа Word - обычная область типа таблица. Всё пересчитывается, всё связано, ну красота... Только не надо по-моему этого делать, не знаю, как в последних версиях, а недавно это всё было такое глючное.... Запросто может такая фишка повесить всю систему или другие неявные проблемы вызвать.

avg: Kikinda пишет: А кто нибудь знает как связать word и excel? Лучше этого не делать, Оксана! Косяков может быть очень много. Если потом все не проверить можно попасть на какую-нибудь бяку. а. если все проверять, то тогда особо и нет смысла нет это делать - выигрыша во времени подготовки отчета не будет.

Дмитрий: Kikinda пишет: У меня вроде все работает. Это радует. По весне когда не хватает витаминов и чтоб все работало. Это хорошо Kikinda пишет: Кстати выделить всю таблицу можно гораздо проще = нажать CTRL+A. Ну вообще-то этим сочетанием клавишь выделяется все, оно, сочетание горячих клавишь, так и называется "Выделить все". Причем работает во все виндовских программах. Будто Эксель, Ворд, Блокнот, Paint, Photoshop и прочее. Причем многие клавиши во вногих программах рабюотают одинаково. Kikinda пишет: Скоро придется мазать клавиатуру скипидаром. Ну обычно мажут скипидаром чтоб увеличить скорость, а если и так скорость большая, то это может привести к перегрузкам. Kikinda пишет: А кто нибудь знает как связать word и excel? Этот вопрос Вы, Оксана, уже задавали где-то год назад. И как верно отметил ув. В.Г. "кто-нибудь знает". Но то, что он, то есть В.Г., посоветовал это называется внедрение объекта. Что часто довольно используется, когда вставляются графики в текст. В большинстве случаев графики это внедренные экселевские книги. Связь Экселя и Ворда это не много другое. Это когда они (файлы Эксель и Ворд) существуют по отдельности. Самый простой способ создать связь между Экселем и Вордом это скопировать кусок в экселе (CTRL+C) и вставить (CTRL+V) в Ворд. Когда появиться серенький квадратик справа внизу (раскрывающийся список) там в меню выбрать "Вставить связь" (или что-то подобное). Можно и через специальную вставку. Но делать этого не стоит по следующим причинам: 1. такая связь требует много ресурсов от компа; 2. в случае переноса в другую директорию (папку) экселевского файла или его перенаименование приводит к потери связи, и в ворде возникает ошибка, которую нужно вручную исправлять; 3. если в текст вставлено много связанных кусков из экселя, то велика вероятность, что при использование нового расчетного файла (формирование нового отчета по предыдущему отчету), Вы забудете исправить все ссылки, и какой-то кусок будет ссылаться на страрый расчет (от предыдущего отчета); 4. при связывание экселя и ворда возникает большая проблема по форматированию таблицы - при обновление (горячая клавиша F9) форматирование по умолчанию теряется и используется ворматирование, как в файле экселя (это конечно можно победить). Особенно если нужно вставить строки или столбцы; 5. да и извращение это. Проще написать макрос который будет копировать таблицы из экселя в ворд. Особенно в типовых отчетах, где все таблицы остаются на своих места и не меняются.

Игорь г. Львов: avg пишет: Лучше этого не делать, Оксана! Согласен с avg. Оно слабо вяжется и в результате или ошибки или потеря времени. Проблемы такие-же как и со связанными вставками. Вроде-бы все хорошо, а потом появляються несоответствия. Пока лучший вариант : таблица в Excel и перенос в Word.

Дмитрий: И добавление Здесь это обсуждалось

Дмитрий: Игорь г. Львов пишет: Попробовал. У меня не работает. Exel-2003. Так как винды и экслель битые, не лицензионные, то чтоб все работало нужно подбирать пару винды-офис. При разных сочетаниях разные функциональные возможности. Почти как совместимость семейных пар. Вроде бы вместе живут, но что-то не ладиться. Часто проблема с "горячими клавишами" возникает от плохой русификации (или украинизации). При включенной латинской клавиатуре работает, а при русской не работает. Пример: сохранить должно быть CTRL+S, но если включена русская то часто нужно жать SHIFT-F12

Игорь г. Львов: Дмитрий пишет: Так как винды и экслель битые У меня все легально. Лицензия на каждый крмп.

Дмитрий: Игорь г. Львов пишет: У меня все легально. Все равно от подбора пары винды-офис зависит работоспособность. В основном из-за разной русификации

Kikinda: Мне тут сегодня avg подсказал способ борьбы с глюками фильтра. При работе с фильтром (горячая клавиша CTRL+Shift+L) может возникать такой глюк. При фильтрации по одному параметру мы покрасили ячейки в один цвет, а при фильтрации по другому параметру он перекрашивает ранее выделенные ячейки в другой цвет. Чтобы этого не было надо нажать "применить повторно".

Kikinda: В Excell 2007 нашла очень любопытную функцию, которая позволяет разделить содержимое ячейки на две части. 1. Скопировать столбец который нужно разделить на новую страницу 2. Выделить столбец 3. Вкладка "данные" - "работа с данными" - "текст по столбцам" и далее выбираете знак по которому разделять. Например, если у вас написано, компьютер P-III, инв. номер 2344, вы ставите, что знак разделения - запятая. Excell делит содержимое одного столбца на два столбца: в одном столбце написано компьютер P-III, в другом - инв. номер 2344. Очень полезная штука.

Дмитрий: Kikinda пишет: В Excell 2007 нашла очень любопытную ну это и раньше было. еще да же в версиях под дос

AMar: Тем, у кого нет Excell 2007, можно пользоваться следующим "извращенным" способом: В колонке А - данные, которые надо поделить. В колонке B пишем следующую формулу: =ЛЕВСИМВ(A1;ПОИСК(",";A1)-1) где: "," - "разделительный" символ. В колонке С: =ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(",";A1)) Как говориться, "через Ширшу в Маймаксу", но...

Дмитрий: AMar пишет: можно пользоваться следующим "извращенным" Да не нужно это есть во всех версиях. Кроме того того разделение часто происходит автоматически когда копируешь в эксель Можно разделять не только с помощью разделителя, но и по фиксированной шире. Кроме того если вмнимательно посмотреть на мастер, то там можно задать колонки, которые нужно пропустить, а так е задать формат: текст, число дата. А если он неправильно поставил линие разделения на колонки, то можно эти лини, в том же мастере перенести в нужное место. Нужно вгимательно читать, что пишут на экране, а не жать сразу кнопку "ГОТОВО"

AMar: Дмитрий пишет: Да не нужно это есть во всех версиях. Кто-ж спорит-то. Просто иногда бывает удобнее сделать это формулой. Например, прислали Вам списки разные подразделения, у одних - через ".", в вторых - через "/" или ",". Собрали все в один файл, а потом уже делим (сначала написалось "дулим" ) общую базу ("разделительный" символ можно вынести в отдельную колонку).

Мисовец: AMar пишет: =ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(",";A1)) Угу, а чтобы вторая часть строки не начиналась с пробела, нужно написать: =ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(",";A1)-1) А вот при работе через меню такой возможности нет, но зато сразу можно разделить строку и по запятой и по пробелу, например, чтобы выделить в разных колонках и марку "P-III" и номер "2344" без лишних слов. Так что в боевой обстановке всё может пригодиться.

Kikinda: Чтож вы раньше то молчали? Я вот мучилаь...разделяла только через ПРАВСИМВ А слово ПОИСК что обозначает?

Мисовец: Kikinda пишет: А слово ПОИСК что обозначает? Выдает число, равное номеру позиции в данном случае запятой в строчке, расположенной в данном случае в клетке А1

Kikinda: Оценщикам может быть по душе функция "условное форматирование", которое позволяет выделять цветом ячейки с нужным значением, пустые ячейки и т.д. 1. Выделить столбец, таблицу, строчку 2. Меню "Формат" - "Условное форматирование" 3. Задать нужное условие (равно, больше, меньше, н/д и т.д.) 4. ОК

Kikinda: Получила очередной номер рассылки форума: Excel-Готовые формулы http://sizop.my1.ru/forum/10 И опять дельный совет для нас, оценщиков, работающих с нестандартными списками: Если дан список и в одном столбце дана и текстовая и цифровая информаци, то отделить цифры в отдельный столбец можно при помощи следующей готовой формулы. =ЗНАЧЕН(ПСТР(A2;НАЙТИ(":";A2;1)+ДЛСТР(":");ДЛСТР(A2)-НАЙТИ(":";A2;1))) Например, трасформатор инв. №: 12354 тогда разделение работает по метве: и в отдельном столбце после манипуляций будет 12354 В данном случае меткой можно сделать и символ №, тогда формула примет вид: =ЗНАЧЕН(ПСТР(A2;НАЙТИ("№";A2;1)+ДЛСТР("№");ДЛСТР(A2)-НАЙТИ("№";A2;1))) Спасибо мастерам с форума Excel-Готовые формулы

Дмитрий: мне все равно не понятна любовь к формулам, если можно сделать "текст по столбцам" с меткой какой хочешь или без оной по ширине столбца. Раза два кликнув мышкой и один раз на клавиатуре Зачем сложности?

Kikinda: Дмитрий А это для коллекции

Дмитрий: Понятно. А я то был уверен, что обычно девИцы брюлики коллекционируют. На худой конец - жемчуг. А тут, э-э-э вон оно ка-а-а-к. Формулки коллекционируют. Да уж.

Интересующийся: Господа и Дамы подскажите, как в Exel сделать сквозную нумерацию страниц? Предположим на 1 листе 10 страниц, на 2 листе еще страницы. Как сделать чтобы начинался не с 1 а с 11?

AMar: Как сквозную нурмерацию автоматически сделать - не знаю. Но распечатать с правильными номерами можно так: Меню Файл-Параметры страницы, закладка "Страница", внизу поле "номер первой страницы"

Jane: Подскажите пожалуйста как разбить (вернуть обратно) четыре соединенных в один столбца с помошью функции "сцепить". какую функцию нужно применить? Спасибо.

AMar: Jane пишет: Подскажите пожалуйста как разбить (вернуть обратно) четыре соединенных в один столбца с помошью функции "сцепить". какую функцию нужно применить? Спасибо. Если при соединении ("сцепить") использовались какие-либо разделители (например, колонки А и B были сцеплены при помощи такой формулы: "=СЦЕПИТЬ(A1;";";B1)"), то разделить можно таким образом: то, что ранее было в колонке А "вытаскивается": =ЛЕВСИМВ(C1;НАЙТИ(";";C1)-1) то, что ранее было в колонке B: =ПРАВСИМВ(C1;ДЛСТР(C1)-НАЙТИ(";";C1)) где С1 - ячейка, которую необходимо разделить. Если сцеплено несколько колонок: - можно по тому же принципу использовать функции ПСТР и НАЙТИ или ПОИСК. - путем "постепенного" отделения колонок при помощи функций ЛЕВСИМВ и ПРАВСИМВ. А вот если разделителя не было, то все гораздо сложнее...

Дмитрий: Помню раньше была функция - что-то нажать и эксель считает подитоги по заданному условию. А сейчас найти это не могу. Пример. Есть колонка "дата" где год постановки на баланс, можно было автоматически посчитать суммы при условии "изменения даты" по любой заданной колонке - балансовая ст-ть или рыночная стоимость или еще что. Получался таблица с добавленными внутри списка строкой "итого по 1998 году ...." Путано получилось

AMar: Наверно СУММЕСЛИ или СУММЕСЛИМН?

Дмитрий: нет суммесли() это типа формирование "сводной таблицы". А там проводилась группировка по условию изменения значения в каком либо столбце и вычисление промежуточных итогов (не только сумма, но и среднее, макс, и т.д.) по любым столбцам

AMar: Тогда, плз., поподробнее что именно требуется. Может пример коротенечко? ЗЫ: В принципе, можно сводной таблицей проблему решить. Она все считает (и средние, и количество, и мин / макс и т.п.).

AMar: Похоже нашел - меню данные-итоги. Оно? Только, если я правильно понял, там нужен сортированный список.

ГолубевАВ: Еще есть функция "промежуточные итоги". Это когда данные отфильтрованы по какому-то значению (например все авто 2005 года выпуска) http://slil.ru/27838698 Это имели ввиду?

Дмитрий: AMar пишет: Похоже нашел - меню данные-итоги. Оно? Да оно, спасибо большое и человеческое

Дмитрий: Кто-нибудь на 2007 офис перешел? Как там панели редактировать (настраивать)? Чтоб показывало только то что нужно И как редактировать картинки (пиктограммы) если добавляешь свою кнопку. Кстати, ту иногда заходит вопрос про округление. Я тут небольшой макрос написал, который вставляет (добавляет) формулу округления в выделенные ячейки. Количество знаков после запятой запрашивается. Sub ДобавитьФормулуОкругл() RowCount = Selection.Rows.Count ColCount = Selection.Columns.Count NumZero = InputBox("Введите число знаков после запятой", , 0) 'количество знаков после запятой For Each c In Range(Selection.Cells(1, 1), Selection.Cells(RowCount, ColCount)) If Left(c.Formula, 1) = "=" Then strTextOfcell = Mid(c.Formula, 2) Else strTextOfcell = c.Formula End If strTextOfcell = "=round(" & strTextOfcell & "," & NumZero & ")" c.Formula = strTextOfcell Next c End Sub может кому пригодится

fisher: Кто-нибудь пользовал офис 10? Хочется узнать, что это за зверь такой. Прочитав о возможных последствиях установки и использования, поостерегся...

Игорь Б.: fisher пишет: Прочитав о возможных последствиях установки и использования, поостерегся... аналогично. Дай думаю погожу

Alex: PLEX - очень полезная и необходимая надстройка Excel. Избавляет от множества рутинных операций Почитать о ее возможностях можно тут: http://www.planetaexcel.ru/plex_features.php#35 Мне тут например, очень понравилась функция извлечения кода заливки ячейки

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

Дмитрий: Я сейчас поставил Офис 2010 (бета). Получше чем 2007. Скачивается бесплатно с сайта микрософт, надо только зарегистрироваться Эксель удобней чем 2007 и 2003. А что самое удобное, можно установить 2010 офис и оставить предыдущую версию.

Людамилка: Alex пишет: Я сейчас поставил Офис 2010 (бета). Получше чем 2007. А в чем преимущества???? в чем разница с 2007 ???

Дмитрий: Разницы почти нет, но почему-то стало удобней, какие-то вроде бы мелочи. Ну и теперь можно настраивать ленту, а не только панель быстрого доступа. Сохранять эти настройки ленты и панели и переносить на другой компьютер. А вот убирать ленту (панель) вправо или влево этого как не было, так и нет

Дмитрий: fisher пишет: Кто-нибудь пользовал офис 10? Хочется узнать, что это за зверь такой. Прочитав о возможных последствиях установки и использования, поостерегся... Игорь Б. пишет: аналогично. Дай думаю погожу Единственный минус, что в октябре бесплатная лицензия закончится. Начнется коммерческое использование. Но этим нас не испугать. Как уже говорил при установке запрашивается удалить старую версию офиса или оставить, можно оставить старую версию, на всякий случай. Надо внимательно читать при установке что пишет система. Я не жалею потраченного времени на скачивание 500 мегов и установку

Alex: заголовок темы: "Excell. Некоторые тайны" обратил внимание - в слове Эксел одна буковка "L" явно лишняя

Kornilov: Коллеги! Кто знает - можно ли настроить курсор так, чтобы тыкая мышью в ячейку (например F5), подсвечивалась либо строка (5-я, не только имя, а вся строка!), либо строка (5-я) и столбец (АF). Чаще хочу первый вариант, но именно при выделении ячейки.

Людамилка: Kornilov пишет: можно ли настроить курсор так, чтобы тыкая мышью в ячейку (например F5), подсвечивалась либо строка (5-я, не только имя, а вся строка!), либо строка (5-я) и столбец (АF). Нет, Евгений Иванович, не получится! Ни в параметрах Экселя, ни в панели управления такого нет! И вряд ли такой макрос можно написать. Можно только ручками вставать на заголовки строк или столбцов.... А Вам такой маневр для чего???

Kornilov: Людамилка пишет: А Вам такой маневр для чего??? Чтобы взгляд не соскальзывал на соседние строки...

Людамилка: Ну тогда, как я уже сказала раньше :) Пальцем или на заголовок строки или на заголовок столбца! Машины умные, но не настолько

Kikinda: Kornilov Если ткнуть курсором на линейку справа (перед столбцом A), то подсветится вся строка. Чтобы было удобно в длинных таблицах - используйте кнопку "закрепить ячейку". А чтобы взгляд не соскальзывал - совета два: 1. Не смотреть футбол во время работы с экселем (Хотя сегодняшний матч пропустить никак нельзя) 2. Выделять цветом весь столбец

Kornilov: Kikinda пишет: используйте кнопку "закрепить ячейку". А это где? У меня Офис-2003. Там только "закрепить области" вижу...

Kornilov: Kikinda пишет: (Хотя сегодняшний матч пропустить никак нельзя) Лишнего билетика нету?

Людамилка: Kornilov пишет: Там только "закрепить области" вижу... Это оно и есть!

Kornilov: Людамилка пишет: Это оно и есть! Тогда - не годится! Эта функция уже использована для закрепления заголовков колонок.

Людамилка: Остается только цветом заливать!

Дмитрий: Можно в экселе делать разрыв оси в графике? Например на графике цены квартир в Москве и в Области между кривульками много места пустого, если делать больше шаг деления то графики сглаживаются и не красиво, но близко, если шаг деления большой то линии сильно разносятся друг от друга не удобно смотреть

Kikinda: Дмитрий Можно это делать. Нажимать "добавить данные" и совместить 2 графика.

Дмитрий: Kikinda пишет: совместить 2 графика. Что значит "совместить"? На разных осях сделать? Или там есть новая функция "совместить"

Kornilov: А еще можно так рисовать:

gonza: «»

Rums: надстройка для Exel, переводящая число в "рубли прописью" (копейки округляются до рубля) формула - =СУММ_ПРОП_РУБ(ячейка) выложить тут не получается, залил сюда: http://ifolder.ru/28205641 с копейками и описанием установки надстройки - http://keysoft.narod.ru/_private/PROG/propis.htm

Rums: Могу посоветовать удобную программку для написания/вставки в отчет сложных формул. http://www.softportal.com/software-20157-mathtype.html Описание: MathType - Мощное средство для набора формул в документах. Работает совместно с любым текстовым редактором, включая MS Word. Кстати, тот редактор формул, что включен в Word, при ближайшем рассмотрении оказывается ни чем иным, как сильно усеченным вариантом MathType... Если кому надо - пробуйте, по скайпу перешлю полную версию...

Мисовец: Rums пишет: Могу посоветовать удобную программку для написания/вставки в отчет сложных формул. http://www.softportal.com/software-20157-mathtype.html Вашу страницу заблокировал DrWeb

Rums: Мисовец пишет: Вашу страницу заблокировал DrWeb хммм... мой "аваст" не реагирует... ну тогда просто в поисковике MathType

kreativka: а вот как сделать такую штуку? Допустим есть большая-длинная-широкая таблица. Когда начинаешь передвигаться по листу экселя вниз и по ширине вправо, то скрывается шапка таблицы и нумерация по порядку, приходится постоянно возвращаться туда-сюда по листу, чтобы уточнить что же это за столбец-строка. Видела, что как-то закрепляют шапку таблицы таким образом, что при прокручивании таблицы шапка остаётся всё время сверху, то есть движется не весь лист эксель, а только ячейки со значениями под шапкой. Очень удобно и красиво!

Kornilov: kreativka пишет: Видела, что как-то закрепляют шапку таблицы таким образом, что при прокручивании таблицы шапка остаётся всё время сверху, то есть движется не весь лист эксель, а только ячейки со значениями под шапкой. Посмотрите, надо сделать так:

kreativka: Kornilov спасибо огромное, оказывается, это так просто!

AMar: Есть еще один способ - воспользоваться "Разделить" (на картинке в сообщении Генерала эта кнопка находится справа от выделенной "Закрепить области")...

Kornilov: AMar пишет: в сообщении Генерала Нет уж, раз на Аппрайзере назвали Адмиралом - пусть им и останусь! ЗЫ. В отместку - сошлюсь в ближайшем отчете на скидку в 7% на ЗУ в период кризиса (со всеми регалиями Русской Службы Оценки)!

AMar: Kornilov пишет: Нет уж, раз на Аппрайзере назвали Адмиралом - пусть им и останусь! Раз Адмирал, значит Адмирал. Товарищ Адмирал, разрешите доложить: как человеку невоенному мне простительно путать звания ... даже военные сборы я просидел в кабинетах родного ВУЗа (единственный поток, у которого были «кабинетные сборы» – офицеры это объяснили отсутствием денег. Потоки на год младше и на год старше все выезжали). Хотя... сначала наш майор попытался развить какую-то активность: в первый же день нарядил в КХЗ и выгнал на ближайший пляж ставить мины. В результате 10 поставили, а вот сняли только 9. 10-ю искали часа 4. После этого случая желание «выводить» нас в поля у майора почему-то пропало... :)

Kornilov: AMar пишет: как человеку невоенному мне простительно путать звания . Принимается! А вот со скидками на ЗУ в размере 7% - придется смириться. Конечно, хотелось бы процентов 30, а еще лучше 50, но где ж их аргументированно надыбать!?

kreativka: а можно ли в экселе провернуть что-нибудь эдакое, например, удалить каждую пятую строку, или транспонировать все строки кроме каждой четвёртой, или добавить пустой столбец через каждых два столбца )))

Kornilov: kreativka пишет: а можно ли в экселе провернуть что-нибудь эдакое, например, удалить каждую пятую строку, или транспонировать все строки кроме каждой четвёртой, или добавить пустой столбец через каждых два столбца ))) Можно. Ручками.

kreativka: Kornilov пишет: Можно. Ручками. ДА???? и всё же - должен быть способ оптимизировать процесс

Kornilov: kreativka пишет: Kornilov пишет: цитата: Можно. Ручками. ДА???? и всё же - должен быть способ оптимизировать процесс Есть у меня секретный способ - отдаю рутинную техническую работу помощнику. -)

Мисовец: kreativka пишет: а можно ли в экселе провернуть что-нибудь эдакое, например, удалить каждую пятую строку, или транспонировать все строки кроме каждой четвёртой, или добавить пустой столбец через каждых два столбца ))) Удалить выбранные строки можно так: выделяем строки кликая мышкой на номера строк, удерживая клавишу Ctrl, в итоге мы выделим все нужные строки, а потом правый клик на одной из выделенных срок и удалить. Т.е. удалить каждую пятую, тут нужно писать макрос, а вот выделить все ненужные и разом удалить это можно. С транспонированием вообще всё просто, есть функция =ТРАНСП() и куда Вы её натравите, то она и транспонирует, т.е. натравили на три строки, и копируете куда надо....., пропуская четвертую. Ну а столбцы ручками, тут иного способа нет

AMar: kreativka пишет: а можно ли в экселе провернуть что-нибудь эдакое, например, удалить каждую пятую строку Например так: в пустой колонке заполняете "в столбик" пять цифр: 1 2 3 4 5 Затем все пять ячеек выделяете и копируете на нужный диапазон. Потом включаете фильтр, выделяете ячейки с цифрой 5 и удаляете соответствующие строки... Вместо написания цифр вручную и их копирования на диапазон можно формулу написать: =если(ХХ=5;1;ХХ+1), где ХХ - ссылка на предыдущую ячейку... С колонками так, к сожалению, не получится... На первый взгляд колонки только макросами ... или сформировать новый лист на основе данных другого листа (там можно и формулами...)...

Kikinda: Excel может считать IRR. К своему стыду я об этом не знала и всегда считала методом подбора. Теперь узнала про встроенную функцию Excel - IRR (ВСД). Синтаксис: =IRR(values;[guess]) values - диапазон с потоками денежных средств (надо выделить строчку). guess - необязательное поле - ваше предположение, какая ставка приблизительно получится. Оно нужно для того, чтобы начать от этой ставки расчет. Обычно у вас есть понимание о том, какая ориентировочно ставка дисконтирования может быть заложена в проект (контракт). Там где я это нашла написано следующее: http://youcanexcel.ru/formulae/89-irr Пример: Купить или арендовать. Вы осмысливаете покупку или аренду, скажем, грузовика, который будет приносить вам прибыль (предположим, вы транспортная компания). Купить грузовик вы можете за 2,5 миллиона рублей (цифры взяты с потолка), аренда обойдется вам в 600 тысяч рублей/год. Вы знаете, что срок полезного использования грузовика - пять лет, после чего он обладает остаточной стоимостью, скажем, 400 тысяч. После аренды грузовик остается у арендодателя. Предположим, что оплата производится авансом на год вперед. Свободных средств на покупку у вас нет, но есть возможность привлечь финансирование под 18% годовых. Что выгоднее? Постройте несложную таблицу потоков денежных средств и посчитайте внутреннюю ставку доходности. Как видно из таблицы вверху, внутренняя ставка доходности (в данном случае не IRR (internal rate of return), а IIR (internal implicit rate)) составляет 15% годовых, что дешевле займа в банке. На практике также учитывают налоговые моменты, для этого требуется построить серьезную экономическую модель, но общее представление можно получить.

VBA-Excel: Простите за рекламу, но думаю по теме. Профессионально разрабатываю макросы и приложения в MS Office Готовые решения: 1. Импорт курса валюты с сайта cbr.ru Функция =КУРС() http://vba-excel.ru/examples/get_currency 2. Сравнение таблиц http://vba-excel.ru/examples/matching 3. Сумма прописью http://vba-excel.ru/examples/summ_words

Kikinda: VBA-Excel Будем считать, что это не реклама. Если есть еще какие то удобные штуки, можете нам сообщать. Спасибо!

VBA-Excel: Excel умеет практически все, проще ответить что не умеет 1. Быстро найти числа, сохраненные как текст с помощью цветовой карты ячеек 2. Сделать скриншот выделенных ячеек 3. Поставить пароль на все листы сразу 4. Импортировать листы кучей из указанных файлов в текущую книгу 5. Открыть текущую папку одной кнопкой 6. Поменять столбцы местами одной кнопкой ... Много инструментов в надстройке VBA-Excel http://vba-excel.ru/files/examples/AddInVBA-Excel/VBA-Excel_Install_2.0.8.xlsm Постоянно дополняю, исправляю, автоматически обновляется.

Kornilov: VBA-Excel пишет: Постоянно дополняю, исправляю, автоматически обновляется. Ага! Тогда вопрос от слабовидящих: Как сделать ярче кнопку фильтра? Чтобы активная кнопка цветом выделялась среди неактивных, а?

VBA-Excel: К сожалению, это сделать нельзя (не взламывая Excel). Но это не означает, что слабовидящие должны унывать. Можно выйти из ситуации и подсвечивать строку заголовка, выделенного столбца. Файл прилагаю http://vba-excel.ru/files/examples/FiltersColumnFill.xlsm P.S. Вещь нужная, поставил в очередь разработки, возможно будет более удобный способ.

Kornilov: АТЛИЧНО! 1) Заработало сразу во всех открытых файлах. 2) Но, после выхода из программы и перезапуска с новым файлом, Эксель не помнит о процедуре. Правильно я понимаю, что присланный файл каждый раз надо подгружать для получения эффекта?

Мисовец: Kornilov пишет: Правильно я понимаю, что присланный файл каждый раз надо подгружать для получения эффекта? Правильно, но если сделать вначале программы, если бы это была, конечно, исполняемая программа, процедуру на ассемблере, то можно было бы дать команду TSR - Terminate stay resident и прога бы сидела в памяти до перезагрузки компьютера, ну или вставить ссылку на файл в автозагрузку при старте Windows, тогда и файл Excel годится

VBA-Excel: C ассемблером конечно не стоит связываться когда есть встроенный язык VBA. Есть несколько выходов. 1. Собственно запускать каждый раз 2. Добавить код в расчетный файл/файлы, которым постоянно пользуетесь + думаю скоро добавлю этот функционал в мою надстройку с возможностью включать и отключать данную подсветку фильтров. Может нужны еще инструменты?

Kornilov: VBA-Excel пишет: + думаю скоро добавлю этот функционал в мою надстройку с возможностью включать и отключать данную подсветку фильтров. Да, спасибо! В моем случае (база данных с сотней столбцов и более шести тысяч записей) хорошее вспоможение! Однако, обнаружился побочный эффект: началось торможение перехода в соседнюю ячейку, осуществляемое клавишами "вверх-вниз-вправо-влево". Может нужны еще инструменты? да-да. вспомню, опишу.

VBA-Excel: Поправил, должно помочь. http://vba-excel.ru/files/examples/FiltersColumnFill.xlsm

VBA-Excel: Продолжим, то что стандартный Excel не умеет объединение ячеек с сохранением текста цветовая карта данных менеджер листов (удаление, изменение видимости, установка пароля, сохранение как отдельных файлов и т.д.) защита всех листов сразу скриншот области (к примеру для отправке по почте) сортировка листов резервное копирование файла с сохранением в имени файла времени копирования суммирование ячеек с определенным цветом заливки/шрифта подсчет уникальных позиций их выделение цветом и много другое в новом обновлении надстройки http://vba-excel.ru/projects/addin_vba-excel

НН: что прописать в условном форматировании если в одной ячейке стоит пусто в другой стоит значение

Мисовец: Есть такая функция ЕНД() если нет данных она выдает истину :)

VBA-Excel: НН пишет: что прописать в условном форматировании если в одной ячейке стоит пусто в другой стоит значение А что Вы хотите получить? Пока вопрос из серии "что купить в магазине если в одном продукты, в другом хозтовары"

VBA-Excel: Новые функции в надстройке VBA-Excel =СЦЕПИТЬДИАПАЗОН(ДИАПАЗОН; РАЗДЕЛИТЕЛЬ) удобно пользоваться вместо стандартной функции СЦЕПИТЬ с перечислением множества ячеек =МИНЕСЛИ, МАКСЕСЛИ - аналогичные стандартной функции СУМЕСЛИ (странно что их нет в Excel) =ИЗВЛЕЧЬЛАТИНИЦУ(ТЕКСТ) - извлекает латинские слова из текста, может пригодиться при извлечении модели из наименования =ПОДСТРОКА() - Извлекает подстроку текста, разделенного символом-разделителем (Пример: =ПОДСТРОКА("Мама мыла раму";" ";1) вернет "Мама") =ТЕКСТФОРМУЛЫ - Возвращает текст формулы указанной ячейки Подробнее тут http://vba-excel.ru/projects/addin_vba-excel

Kornilov: 2VBA-Excel Еще часто встречающаяся (м.б., только у меня) проблема - включение фильтра в разных колонках не последовательно, а параллельно. Другими словами, не "and", а "or". как пример, нужно отфильтровать строки с непустыми ячейками в колонке А или в колонке В.

VBA-Excel: Да такое возможно. Есть два решения: 1. Можно использовать расширенный фильтр (Вкладка "данные" -> Группа "Сортировка и фильтры" -> "Дополнительно") по двум или более столбцам и условиям. Советую посмотреть доступное видео на русском http://office.microsoft.com/ru-ru/excel-help/VA104121817.aspx?CTT=1&client=1 2. Я обычно делаю так: Создаю новый столбец в котором прописываю формулу (на Вашем примере =ИЛИ(Столбец1="";Столбец2="")) и уже этот столбец фильтрую по значению ИСТИНА

Kornilov: 2VBA-Excel Спасибо! Прослушал урок здесь: http://www.youtube.com/watch?v=sF3BZ1bmEDM ОЧЕНЬ ПОНЯТНО!

VBA-Excel: Перекрестное выделение активной ячейки Excel для наглядной работы с данными http://youtu.be/dVxreYytJCU?list=UUh1z7J-GWxJkmc2M1ONq4Ug

VBA-Excel: Сделал функцию ПЕРЕВОД в Excel (встроил Яндекс.Перевод). Можно переводить с любого на любой язык. Удобно если у вас большой перечень ячеек на иностранном языке. Тут подробное описание http://vba-excel.ru/projects/addin_vba-excel/translate

Мисовец: А как этот файл загрузить в Excel, чтобы он там работал

twisasunde1986:

skameykin22: Для авто, который на видео не помешал бы дорожный конус.

Kar1na: спасибо

полная версия страницы