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