Это интересно: Excel

Показаны сообщения с ярлыком Excel. Показать все сообщения

понедельник, 12 февраля 2018 г.

Клавиши Excel

http://lifter.com.ua/5766/Vot-200-sochetaniy-klavish-kotorie-sdelayut-vas-bogom-Ekselya

среда, 6 декабря 2017 г.

Замена переноса строк в ячейке EXCEL

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(G5;СИМВОЛ(13);"&");СИМВОЛ(10);"&")

вторник, 29 ноября 2016 г.

Отправка листа по электронной почте VBA EXCEL

Sub SendSheetççç()
ThisWorkbook.ActiveSheet.Copy
' ThisWorkbook.Sheets("Лист1").Copy
With ActiveWorkbook
.SendMail Recipients:="vasya@pupkin.com", Subject:="тема"
.Close SaveChanges:=False
End With
End Sub

====================================

Sub SendActiveWorkbook()

Dim name As String
Dim email As String

Set name = Range("C17").Value
Set email = name & "@xxx.com.au"

    ActiveWorkbook.SendMail _
    Recipients:="xxxx@xxx.com.au", email, _
    Subject:="Stock Request" & Format(Date, "dd/mmm/yyyy")
    
End Sub

===================================

Function SendMail()     
Dim y As String     
Dim x As Integer     
y = Sheets(1).Range("M1").Value     
x = Sheets(1).Range("K1").Value     
' Copy the sheet(1)     
ThisWorkbook.Sheets(x).Copy     
' Send Email     
With ActiveWorkbook     
.SendMail Recipients:="y", Subject:="test" & Format(Date, "dd/mmm/yy")     
.Close SaveChanges:=False     
End With     
' Subject:=Range("Feuil1!B1").Value & " " & Range("Feuil1!A1").Value     
End Function

======================================

Function SendMail()     
Dim y As String     
Dim x As Integer     
y = Sheets(1).Range("M1").Value     
x = Sheets(1).Range("K1").Value     
' Copy the sheet(1)     
ThisWorkbook.Sheets(x).Copy     
' Send Email     
With ActiveWorkbook     
.SendMail Recipients:="y", Subject:="test" & Format(Date, "dd/mmm/yy")     
.Close SaveChanges:=False     
End With     
' Subject:=Range("Feuil1!B1").Value & " " & Range("Feuil1!A1").Value     
End Function

===========================================

http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html

Отправка письма из Excel

https://www.youtube.com/watch?v=x7At5hkOQeQ

Sub SendWorkbook()

    ActiveWorkbook.SendMail Recipients:="vasya@pupkin.com", Subject:="Лови файлик"

End Sub

Sub SendSheet()

    ThisWorkbook.Sheets("Лист1").Copy

    With ActiveWorkbook

        .SendMail Recipients:="vasya@pupkin.com", Subject:="Лови файлик"

        .Close SaveChanges:=False

    End With

End Sub

http://www.planetaexcel.ru/techniques/3/48/

понедельник, 28 ноября 2016 г.

VBA отправить почту

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Дальше »

среда, 19 октября 2016 г.

Отправка писем из EXCEL

=ГИПЕРССЫЛКА("mailto:alexandr@pushkin.ru, mikhail@lermontov.ru?cc=vladimir@mayakovski.ru&bcc=anna@akhmatova.ru&subject=Дружеские посиделки&body=Друзья!%0AЕсть идея.%0AА не хлопнуть ли нам по рюмашке?";"Отправить")

http://www.planetaexcel.ru/techniques/11/82/

==================================================================

В URL также может содержаться адрес электронной почты. Щелкнув на такой гиперссылке, вы открываете сообщение в окне вашего стандартного почтового клиента, где в поле Кому указан адрес электронной почты. Например, =ГИПЕРССЫЛКА("mailto:s_bag@mail.ru";"Отправить"). Если на вашем локальном компьютере не установлен почтовый клиент и вы пользуетесь веб-почтой (например, gmail), то данный метод не сработает.

Дальше »

Содержание папки

Как получить список имен файлов, содержащихся в папке, а затем отобразить этот список на листе Excel

В этой статье будет рассказано, как получить список имен файлов, содержащихся в каталоге, а затем отобразить этот список на листе. Данный способ связан с использованием макрофункции Excel 4 XLM в именованной формуле. Такой метод удобен, поскольку позволяет относительно легко выстроить на листе список имен файлов — обычно для этого требуются сложные макросы VBA.

Откройте новую книгу, а затем выполните следующие действия для создания именованной формулы.

Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
Впишите в поле Имя название Список.
Введите следующую формулу в поле Диапазон (рис. 8.1): =ФАЙЛЫ(Лист1!$A$1).
Нажмите ОК, чтобы закрыть окно Создание имени.

Обратите внимание: функция ФАЙЛЫ не относится к стандартным функциям рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для использования на специальном листе макросов. Она принимает один аргумент (путь к каталогу и описание файла) и возвращает массив имен файлов, содержащихся в данном каталоге и удовлетворяющих описанию файла. Обычные формулы рабочего листа не могут использовать подобные XLM-функции, а именованные формулы — могут.

Определив именованную формулу, введите в ячейку А1 путь к каталогу и описание файла. Например: C:\Excelexpert.ru\* *. Далее формула отображает первый найденный файл: =ИНДЕКС(Список;1). Если изменить второй аргумент на 2, то будет отображен второй найденный файл и т. д. На рис. 8.2 приведен пример. Путь и описание файла указаны в ячейке А1. В ячейке А2 содержится следующая формула, скопированная далее по столбцу: =ИНДЕКС(Список;СТР0КА()-1). Функция СТРОКА, используемая здесь, генерирует ряд последовательных натуральных чисел: 1,2,3 и т. д. Эти числа используются в качестве второго аргумента функции ИНДЕКС.

Рис. 8.2. Использование XLM-макроса в именованной формуле позволяет создать на листе список имен файлов
Рис. 8.2. Использование XLM-макроса в именованной формуле позволяет создать на листе список имен файлов

Обратите внимание: в ячейке А17 (и ниже) происходит ошибка. Дело в том, что в каталоге содержится всего 19 файлов и формула пытается отобразить имена несуществующих. Если изменить каталог или описание файла в ячейке А1, то формулы обновляются и выводят имена новых файлов.

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).

http://excelexpert.ru/kak-poluchit-spisok-imen-fajlov-soderzhashhixsya-v-papke-a-zatem-otobrazit-etot-spisok-na-liste-excel

суббота, 24 сентября 2016 г.

Проверка ячеек на содержание гипперссылки

Sub Ìàêðîñ1()
For i = 1 To 1500
If Cells(i, 4).Hyperlinks.Count = 0 Then
' If ActiveCell.Hyperlinks.Count = 0 Then
'êîä äëÿ äîáàâëåíèÿ ãèïåðññûëêè
Else
Cells(i, 22) = 1
'MsgBox ActiveCell.Hyperlinks(1).Address & vbLf & ActiveCell.Hyperlinks(1).SubAddress
End If

Next
End Sub

четверг, 1 сентября 2016 г.

Команда отмена действия

При использовании средства Excel Отмена (Undo), когда вы достигаете 16-го дей-
ствия, на место 17-го помещается первое и так далее. Кроме того, когда вы сохра-
няете рабочую книгу, стек отмены очищается и история отмены действий теряет-
ся. Это происходит потому, что, нажимая кнопку Сохранить (Save), вы сообщаете
Excel, что удовлетворены сделанными изменениями, и он решает, что история
отмены действий больше не нужна.
Иногда вы, вероятно, обнаруживали, что 16 изменений, хранящихся в стеке отме-
ны, недостаточно. Вместо того чтобы смириться с этим, вы можете изменить этот
параметр, отредактировав реестр (это работает только в Windows). Для этого пол-
ностью закройте Excel. Выберите команду Пуск > Выполнить (Start > Run) и в поле
Открыть (Open) введите Regedit.exe. Щелкните кнопку ОК. В открывшемся окне
редактора реестра разверните папку HKEY_CURRENT_USER. Затем разверните пап-
ку Software t Microsoft > Office, затем, например, папку 10.0 (название последней
папки разное для различных версий Excel, например, 10.0 — это название для
Excel 2002). Разверните папку Excel и, наконец, папку Options.
Выберите команду Правка > Создать > Параметр DWORD (Edit > New > DWORD Value),
введите слово UndoHistory и нажмите клавишу Enter. Дважды щелкните только что
созданный пункт UndoHistory и введите значение от 16 до 100.
Надеемся, что 100 отмен будет достаточно даже для самых требовательных поль-
зователей, вопрос с очисткой стека отмены при сохранении, однако, остается не-
решенным.

понедельник, 13 июня 2016 г.

Клавишы excel

таблица. Горячие клавиши, незаменимые при работе в Excel Клавиша или комбинация клавиш Действие Аналогичная команда меню Вычисления SHIFT + F3 Вызвать мастер Функций Формулы - Вставить функцию F4 Задать тип сылки на ячейку при вводе формулы – относительная или абсолютная – CTRL + ~ Отображать в ячейках формулы или их результаты Формулы – Показать формулы ALT+ «Равно» Ввести в ячейку функцию СУММ Формулы – Автосумма F9 Пересчитать формулы во всей книге Формулы – Пересчет SHIFT + F9 Пересчитать формулы на текущем листе Формулы – Произвести вычисления Редактирование F2 Редактировать активную ячейку – SHIFT + F2 Редактировать примечание к ячейке – CTRL + Delete Удалить текст от курсора до конца строки (при редактировании содержимого ячейки) – CTRL+ALT+V Специальная вставка (после копирования) – CTRL + «Плюс» Добавить выделенное количество строк или столбцов Главная – Вставить – Строки (Столбцы) CTRL + «Минус» Удалить выделенные строки или столбцы Главная – Удалить – Строки (Столбцы) CTRL + D Заполнить выделенный диапазон значениями или формулами из первой выделенной ячейки Главная – Заполнить – Вниз CTRL + H Вызвать диалоговое окно «Найти и заменить» Главная – Найти и выделить – Заменить SHIFT + F11 Вставить пустой лист Главная – Вставить – Вставить лист ALT + SHIFT + «Стрелка вправо» Сгруппировать выделенные строки (столбцы) Данные – Группировать ALT + SHIFT + «Стрелка влево» Разгруппировать выделенные строки/столбцы Данные – Разгруппировать Ввод данных CTRL + Enter Ввод сразу во все выделенные ячейки – ALT + Enter Перенос строки внутри ячейки (при вводе) – CTRL + SHIFT + «4» Вставить текущую дату – ALT + «Стрелка вниз» Открыть выпадающий список (в пустой ячейке под столбцом с данными) – Навигация и выделение CTRL + Back space Вернуться к активной ячейке (прокрутка) – CTRL + PgUp (PgDown) Переключение между листами – CTRL+Tab Переключение между открытыми книгами Вид – Перейти в другое окно CTRL + пробел Выделить весь столбец – SHIFT + пробел Выделить всю строку – CTRL + A Выделить текущую область (до ближайших пустых строк и столбцов) – CTRL + End Перейти к последней ячейке листа – CTRL + SHIFT + End Выделить все до последней ячейки листа – CTRL + «Стрелка вправо / влево / вверх / вниз» Перейти до конца или начала строки (столбца) – CTRL + SHIFT + «Стрелка вправо / влево или вверх /вниз» Выделить до конца или начала строку или /столбец – Работа с файлами CTRL + N Создать новую пустую книгу – CTRL + S Сохранить текущую книгу – CTRL + O Открыть файл – Прочее CTRL + L Создание «умной» таблицы (с фильтрами, автоподстройкой размеров, дизайном, закреплением шапки и т. д.) Данные – Форматировать как таблицу CTRL + F2 Предварительный просмотр Кнопка Офис (Файл) – Печать – Предварительный просмотр F4 Повторить последнее действие (любое, если вообще возможно) – CTRL + K Вставить гиперссылку Вставка – Гиперссылка CTRL + F3 Открыть диалоговое окно «Диспетчер имен» Формулы – Диспетчер имен ALT+F8 Открыть диалоговое окно «Макрос» Разработчик – Макросы

пятница, 3 июня 2016 г.

Гиперссылка в EXCEL

Так же можно создать гиперссылку, переходящую на определенную ячейку на листе:

=ГИПЕРССЫЛКА("#Лист2!D4";"Перейти в ячейку D4 Лист2")

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

Ссылку на ячейку можно указывать динамически, ссылаясь на определенную ячейку, не вписывая её адрес(на примере D2):

=ГИПЕРССЫЛКА("#Лист2!"&ЯЧЕЙКА("адрес";D4);"Перейти в ячейку "&ЯЧЕЙКА("адрес";D4)&" Лист2")

Можно указать ссылку на ячейку листа в другой книге:

=ГИПЕРССЫЛКА("#'[Другая книга.xls]Лист2'!"&ЯЧЕЙКА("адрес";D4);"Перейти в ячейку "&ЯЧЕЙКА("адрес";D4)&" Лист2 Другая книга.xls")

Или указать путь к файлу, который необходимо открыть по нажатии ячейки:

=ГИПЕРССЫЛКА("C:\Users\Дмитрий\Desktop\Книга 1.xls";"Открыть книгу Книга 1.xls")

При использовании гиперссылки из функции для открытия книги нельзя применить так же переход на нужный лист и ячейку. Это связано с ограничением именно самой функции. Т.е. нельзя указать что-то вроде: "C:\Users\Дмитрий\Desktop\Книга1.xls#Лист2!D4".

Важно: Книга, на которую ссылается функция ГИПЕРРСЫЛКА должна быть открыта. Если в имени книги или листа содержатся пробелы(или иные знаки препинания, кроме точки и нижнего подчеркивания), то обязательно в начале имени книги (перед [ ) и после имени листа ставить апостроф('). Я специально в одной из последних функций привел пример именно с пробелом - для наглядности:

=ГИПЕРССЫЛКА("#'[Другая книга.xls]Лист2'!"&ЯЧЕЙКА("адрес";D4);"Перейти в ячейку "&ЯЧЕЙКА("адрес";D4)&" Лист2 Другая книга.xls")

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

вторник, 31 мая 2016 г.

Оглавление книги Excel

http://www.planetaexcel.ru/techniques/12/60/

Способ 1. Создаваемые вручную гиперссылки

Вставьте в книгу пустой лист и добавьте на него гиперссылки на нужные вам листы, используя команду Вставка - Гиперссылка (Insert - Hyperlink). В открывшемся окне нужно выбрать слева опцию Место в документе и задать внешнее текстовое отображение и адрес ячейки, куда приведет ссылка:

Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление. Чтобы не заниматься ручным созданием гиперссылок и копированием их потом на каждый лист, лучше использовать другой метод - функцию ГИПЕРССЫЛКА (HYPERLINK). Выделяем все листы в книге, куда хотим добавить обратную ссылку (для массового выделения листов можно использовать клавиши Shift и/или Ctrl) и в любую подходящую ячейку вводим функцию следующего вида:

=ГИПЕРССЫЛКА("#Оглавление!A1";"Назад в оглавление")

Эта функция создаст в текущей ячейке на всех выделенных листах гиперссылку с текстом "Назад в оглавление", щелчок по которой будет возвращать пользователя к листу Оглавление.

Способ 2. Динамическое оглавление с помощью формул

Это хоть и слегка экзотический, но весьма красивый и удобный способ создания автоматического листа оглавления вашей книги. Экзотический – потому что в нем используется недокументированная XLM-функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK), оставленная разработчиками для совместимости со старыми версиями Excel. Эта функция выгружает список всех листов текущей книги в заданную переменную, из которой мы потом можем их извлечь и использовать в нашем оглавлении.

Откройте Диспетчер Имен на вкладке Формулы (Formulas – Name Manager) и создайте новый именованный диапазон с именем, допустим, Оглавление. В поле Диапазон (Reference) введите вот такую формулу:

=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)
=GET.WORKBOOK(1)

Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX), которая «выдергивает» элементы из массива по их номеру:

=ИНДЕКС(Оглавление; СТРОКА())
=INDEX(Оглавление; ROW())

Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.

Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND), которые найдут символ закрывающей квадратной скобки ( ] ) и заменят весь текст до этого символа включительно на пустую строку (""). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas - Name Manager), двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

Теперь наш список листов будет выглядеть существенно лучше:

Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9. Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой "хвост":

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА()) =SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

Функция ТДАТА (NOW) выдает текущую дату (с временем), а функция Т превращает эту дату в пустую текстовую строку, которая затем приклеивается к нашему имени листа с помощью оператора склейки (&). Т.е. имя листа, фактически, не меняется, но поскольку функция ТДАТА пересчитывается и выдает новое время и дату при любом изменении листа, то и остальная часть нашей формулы вынуждена будет заново пересчитаться тоже и – как следствие – имена листов будут обновляться постоянно.

Для скрытия ошибок #ССЫЛКА (#REF), которые будут появляться, если скопировать нашу формулу с функцией ИНДЕКС на большее количество ячеек, чем у нас есть листов, можно использовать функцию ЕСЛИОШИБКА (IFERROR), которая перехватывает любые ошибки и заменяет их на пустую строку (""):

И, наконец, для добавления к именам листов "живых" гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА(HYPERLINK), которая будет формировать адрес для перехода из имени листа:

Способ 3. Макрос

И, наконец, для создания оглавления можно использовать и несложный макрос. Правда, запускать его придется каждый раз при изменении структуры книги - в отличие от Способа 2, макрос их сам не отслеживает.

Откройте редактор Visual Basic, нажав Alt+F11 или выбрав (в старых версиях Excel) в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor). В открывшемся окне редактора создайте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этого макроса:

Sub SheetList()
Dim sheet As Worksheet
Dim cell As Range
With ActiveWorkbook
For Each sheet In ActiveWorkbook.Worksheets
Set cell = Worksheets(1).Cells(sheet.Index, 1)
.Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"
cell.Formula = sheet.Name
Next
End With
End Sub

Закройте редактор Visual Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте менюСервис - Макрос - Макросы (Tools - Macro - Macros). Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.

Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление, как это было описано в Способе 1.

Способ мой. Мой вариант

Название листа - =ЕСЛИОШИБКА(ЗАМЕНИТЬ(ИНДЕКС(Оглавление;СТРОКА()-3);1;НАЙТИ("]";ИНДЕКС(Оглавление;СТРОКА()-3));"");"")

Ссылка - =ГИПЕРССЫЛКА("#"&"'"&B4&"'"&"!A10";">>>")

Дата - =ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'"&B4&"'"&"!A1")=0;"";ДВССЫЛ("'"&B4&"'"&"!A1"));"")

Наименование - =ДВССЫЛ("'"&B4&"'"&"!A3")

ЗП - =ДВССЫЛ("'"&B4&"'"&"!E5")

налог на ЗП - =ДВССЫЛ("'"&B4&"'"&"!E6")

амортизация - =ДВССЫЛ("'"&B4&"'"&"!E7")

материалы - =ДВССЫЛ("'"&B4&"'"&"!E8")

всп материалы - =ДВССЫЛ("'"&B4&"'"&"!E9")

и далее по столбцам

=ДВССЫЛ("'"&B4&"'"&"!E10")
=ДВССЫЛ("'"&B4&"'"&"!E11")
=ДВССЫЛ("'"&B4&"'"&"!E12")
=ДВССЫЛ("'"&B4&"'"&"!E13")
=ДВССЫЛ("'"&B4&"'"&"!E18")
=ДВССЫЛ("'"&B4&"'"&"!E19")

вторник, 26 января 2016 г.

Защита листа

[Как защитить лист от пользователя, но не от макроса? | Excel для всех]: Посмотри! http://www.excel-vba.ru/chto-umeet-excel/kak-zashhitit-list-ot-polzovatelya-no-ne-ot-makrosa/

вторник, 19 января 2016 г.

Надежная защита excel

[Защита паролем Excel, как снять пароль VBA Excel | WordPress и Master-It]: Посмотри! ------>>

суббота, 9 января 2016 г.

Волшебные формулы Excel

[Выборочное суммирование по двум критериям] хорошо, посмотри! http://bourabai.ru/einf/excel/3_2.htm

Страницы

Ярлыки

понедельник, 12 февраля 2018 г.

среда, 6 декабря 2017 г.

вторник, 29 ноября 2016 г.

понедельник, 28 ноября 2016 г.

среда, 19 октября 2016 г.

суббота, 24 сентября 2016 г.

четверг, 1 сентября 2016 г.

понедельник, 13 июня 2016 г.

пятница, 3 июня 2016 г.

вторник, 31 мая 2016 г.