Сообщений в теме: 914

[Daulet.tech]

=ЕСЛИ(ДВССЫЛ($N$3)<>"";A3;"")

замените на столбике O

=ЕСЛИ(ДВССЫЛ($N$3)=1;A3;"")

файле имеется Диспетчер имен CTRL+F3, только продукты не нужен, забыл удалить

 

Руками это делать не охота

 

На примере: выгружаю данные с клиентской программы в htm формате (несколько файлов) в определенную папку, дальше рук макроса: макрос загружает в один лист excel файл данные со всех htm'ки, для дальнейшей обработки...

 

 

 

Актуальные данные находятся в 1с

Если есть доступ к баз данных (... SQL) 1C думаю можно подключиться. Пока только подключался к my sql, так как базу сам поднимал.

Сообщение отредактировал Daulet.tech: 22.04.2019, 22:18:48

[sinsemilia]

Если есть доступ к баз данных (... SQL) 1C думаю можно подключиться. Пока только подключался к my sql, так как базу сам поднимал.

Тут самая сложность. У меня доступ ко всему есть, но структуру в 1с я не знаю. Где их там искать, как настроить 1с-ку с той же самой BI, нужен спец грамотный, а пока такого нет. 

Sql владею на уровне чайника, но эта проблема активно исправляется 

 

На примере: выгружаю данные с клиентской программы в htm формате (несколько файлов) в определенную папку, дальше рук макроса: макрос загружает в один лист excel файл данные со всех htm'ки, для дальнейшей обработки...  

Спасибо вам огромное, Даулет, за подсказки))) Вы всегда очень сильно помогаете!  

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

Сообщение отредактировал sinsemilia: 24.04.2019, 15:15:52

[sinsemilia]

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

[zavuch]

Скрыть все листы по городам кроме одного, где написана инструкция по пользованию книгой

Пароль запрашиваем макросной формочкой где кроме пароля юзер выбирает свой город (он же имя листа) из списка

Выполняем Sheet("имялиста").Unprotect "пароль"

Если совпадает пароль с листом вуаля - макрос даёт команду Sheet("имялиста").visible=true и Sheet("имялиста").Activate

Если юзер накосячил то обработка ошибки, ругательство в адрес юзера и выбор Повторить или Досвидания!

Доступ к коду запаролить тоже

 

Хотя хитрый юзер может, зная имена скрытых листов, сделать ссылки на них и прочитать все скрытые листы

но таких юзеров мало )

Сообщение отредактировал zavuch: 26.07.2019, 12:40:33

[zavuch]

ЛС

 

* защита не листа а структуры книги

 

Листы:

Инструкция (с кнопкой "Жми сюды" запуска auto_open), дальше по городам в порядке "Алматы", "Астана", "Атырау", "Актау"

 

Форма:

 

 

Модуль:

 

Global pass, city, parol

 

Sub auto_open()  ' стартует при открытии книги

parol = "123"       ' пароль на структуру листов, задаётся вручную один раз тут и в Экселе

frmForm.Show    ' открыть форму

End Sub

 

Sub auto_close() ' стартует при закрытии книги

Dim sh As Worksheet

ThisWorkbook.Unprotect parol ' снять защиту книги

For Each sh In Sheets

    If sh.Name <> "Инструкция" Then sh.Visible = xlSheetHidden  ' скрыть все листы

Next sh

ThisWorkbook.Protect parol ' поставить защиту книги

Application.DisplayAlerts = False   ' пересохранить книгу без запросов

ActiveWorkbook.Save

End Sub

 

Форма:

 

Private Sub UserForm_Initialize()

city = Array("Алматы", "Астана", "Атырау", "Актау") ' список городов, в принципе можно считать его из книги а не задавать вручную - ннада?

pass = Array("a", "b", "c", "d")    ' пароли, попозиционно соответствуют массиву городов (использовать Type щитаю тут не к месту)

Dim sh As Worksheet

For Each sh In Sheets

    If sh.Name <> "Инструкция" Then frmForm.cboCity.AddItem sh.Name ' заполнить выпадающий список

Next sh

End Sub

 

Private Sub cmdOk_Click()

i = cboCity.ListIndex   ' узнать номер выбранного в выпадающем списке города

If cboCity.Value = city(i) And txtPass.Text = pass(i) Then ' если он соответствует позиции города в массиве и паролю в массиве то..

    ThisWorkbook.Unprotect parol    ' снять защиту с листов

    Sheets(city(i)).Visible = True       ' сделать лист видимым

    Sheets(city(i)).Activate                ' перейти на него

    Unload Me                                  ' убрать форму

    ThisWorkbook.Protect parol        ' опять запаролить книгу

Else

    MsgBox "Неправильный пароль!"

End If

End Sub

 

Private Sub cmdCancel_Click()

Unload Me

End Sub

 

Сообщение отредактировал zavuch: 26.07.2019, 15:51:55

[zavuch]

но вот приходит хитрый юзер и узнаёт названия скрытых листов и делает ссылку и растягивает...

 

 

откуда узнает названия листов, если ярлыки скрыты?.. пффф... из Бейсика - он под паролем... из меню Параметры, например..((

куда вставит ссылку, если ячейки заблокировать? в новую книгу, например - защита окон вроде тут не спасёт

[T-800 Danya]

Добрый день, уважаемые любители экселя.
Возникла новая необходимость и идея, но вот с реализацией туговато.
Есть файл с продажами по всему Казахстану, в котором отдельно города и тотал. Каждому городу нужно рассылать инфу отдельно, города не должны видеть инфу друг друга, только свою.
Городов дофига, поэтому каждый раз копировать отдельный участок таблицы или лист долго и муторно. Я придумала следующее - каждому городу дать свой пароль, они его запоминают, а я должна сделать таблицу так, чтобы город, вводя свой пароль видел только свое.
 

 

ИМХО  придумано Плохо, поскольку если вы хотите скрыть инфу от Юзвера не ему надо посылать     

 

Что же касается  "долгости и муторности копирования"  - напищите скрипт который будет это делать в один клик. 

ЗЫ Как сделать такой скрипт на макросах - Спросите у Завуча  - Он Сертифицированный  ИМХО должен  ЗНАТЬ как

ЗЗЫ Ну а если Завуч вдруг окажется очень занят и не сможет вам помочь с макоросо   если что можем  написать для вас серверный скрип на python/php  который бы делал бы это все  "долгое и муторное дело"  -  легко и  быстро  онлайн

Сообщение отредактировал T-800 Danya: 26.07.2019, 18:01:32

[zavuch]

Добрый день, уважаемые любители экселя.
Возникла новая необходимость и идея, но вот с реализацией туговато.
Есть файл с продажами по всему Казахстану, в котором отдельно города и тотал. Каждому городу нужно рассылать инфу отдельно, города не должны видеть инфу друг друга, только свою.
Городов дофига, поэтому каждый раз копировать отдельный участок таблицы или лист долго и муторно. Я придумала следующее - каждому городу дать свой пароль, они его запоминают, а я должна сделать таблицу так, чтобы город, вводя свой пароль видел только свое.

ИМХО придумано Плохо, поскольку если вы хотите скрыть инфу от Юзвера не ему надо посылать

Что же касается "долгости и муторности копирования" - напищите скрипт который будет это делать в один клик.

ЗЫ Как сделать такой скрипт на макросах - Спросите у Завуча - Он Сертифицированный ИМХО должен ЗНАТЬ как
ЗЗЫ Ну а если Завуч вдруг окажется очень занят и не сможет вам помочь с макоросо если что можем написать для вас серверный скрип на python/php который бы делал бы это все "долгое и муторное дело" - легко и быстро онлайн

1) В том и дело, что выковырять чужие данные юзер может и по ссылкам и через Басик с другой книги
Только надежда, что юзер не знает, как это делать )

2) Макросом разбросать таблицы по листам - да, естественно, руками это плохо, тем более что не раз придётся разбрасывать.. Справлюсь-то справлюсь, с 90х годов юзаю ВеБеЭй.
Как и Перл/ПХП/Питон. Но ВеБешную задачу лучше на ВеБеЭй )
Ну Синса сама не чужда прогингу, так что главное направить.

И я не работаю прогером я мебель таскаю за это платят а прогером надо за эту же зп писать на 20 языках что не проблема но ещё и делать это 20 часов в сутки и быть нянькой 20ти халявщикам и халтурщикам и тянуть 20 проектов параллельно ну его нафиг...

Сообщение отредактировал zavuch: 26.07.2019, 19:33:36

[Daulet.tech]

Если данные по городам хранить в отдельном листе и наименование листа = город (регион)
Каждый лист в отдельном книге сохраняет, дальше в ручную по эл.почте
Sub Test()
Dim strFileNm As String, s As Worksheet
Application.EnableEvents =0
Application.ScreenUpdating =0
For Each s In ActiveWorkbook.Worksheets
Err.Clear: s.Copy: DoEvents
strFileNm = ThisWorkbook.Path & "\" & s.Name & ".xlsx"
Application.DisplayAlerts =0
ActiveWorkbook.SaveAs Filename:=strFileNm, FileFormat:=51
Application.DisplayAlerts =1
ActiveWorkbook.Close 0
Next
Application.EnableEvents =1
Application.ScreenUpdating =1
End Sub

Сообщение отредактировал Daulet.tech: 29.07.2019, 17:16:16

[zavuch]

FileFormat 51 это кто? согласно таблице xlOpenXMLWorkbook. Сохраняет только один лист? Если так то красивое решение, я бы новую книгу с одним листом создавал или в CSV сохранял (там тоже один лист).

По почте можно тоже макросом, если Аутлук стоит и табличка какой город кому отправляется есть.

 

Синс?

[sinsemilia]

Синс?

Привет всем! Прошу прощения что пропала, на работе все поотрезали, сюда не зайдешь, а вечером приползаешь кое-как. 

 

Скрыть все листы по городам кроме одного, где написана инструкция по пользованию книгой Пароль запрашиваем макросной формочкой где кроме пароля юзер выбирает свой город (он же имя листа) из списка Выполняем Sheet("имялиста").Unprotect "пароль" Если совпадает пароль с листом вуаля - макрос даёт команду Sheet("имялиста").visible=true и Sheet("имялиста").Activate Если юзер накосячил то обработка ошибки, ругательство в адрес юзера и выбор Повторить или Досвидания! Доступ к коду запаролить тоже

 

Да, я так и хотела. На днях сяду и разберу твой пример, но в целом воплощение огонь! 

 

Я сама наковыряла макрос, который по паролю делает еще и выпадающий список, например "Алматы = 123", он его вызывает и дальше формулами цепляет все, что связано с Алматы, остальное скрывает, поэтому я и прицепилась к этим паролям. Задача - не отправлять 20-30 листов руками, а один раз в день отправлять файл, который бы каждый город смотрел сам и видел только свою инфу. Если каждый день отправлять по 30 файлов, тут ничем другим заниматься времени не останется.)) 

 

откуда узнает названия листов, если ярлыки скрыты?.. пффф... из Бейсика - он под паролем... из меню Параметры, например..(( куда вставит ссылку, если ячейки заблокировать? в новую книгу, например - защита окон вроде тут не спасёт

Это маловероятно, юзеры не знают бейсик, там начальный уровень эксель))))

[sinsemilia]

Если данные по городам хранить в отдельном листе и наименование листа = город (регион)
Каждый лист в отдельном книге сохраняет, дальше в ручную по эл.почте
Sub Test()
Dim strFileNm As String, s As Worksheet
Application.EnableEvents =0
Application.ScreenUpdating =0
For Each s In ActiveWorkbook.Worksheets
Err.Clear: s.Copy: DoEvents
strFileNm = ThisWorkbook.Path & "\" & s.Name & ".xlsx"
Application.DisplayAlerts =0
ActiveWorkbook.SaveAs Filename:=strFileNm, FileFormat:=51
Application.DisplayAlerts =1
ActiveWorkbook.Close 0
Next
Application.EnableEvents =1
Application.ScreenUpdating =1
End Sub

Спасибо вам большое Даулет, я на днях соберу все в кучу, прикреплю файлы и выложу сюда) 

 

Ребят, спасибо вам огромное за отзывчивость и примеры! Спасибо огромное за помощь! 

На днях сяду вечером, разберу, нормально все сделаю и выложу сюда) 

 

FileFormat 51 это кто? согласно таблице xlOpenXMLWorkbook. Сохраняет только один лист? Если так то красивое решение, я бы новую книгу с одним листом создавал или в CSV сохранял (там тоже один лист). По почте можно тоже макросом, если Аутлук стоит и табличка какой город кому отправляется есть.

 

По почте автоматом отправлять тоже хочу и буду, но это попозже, сейчас нужно с паролями разобраться)) 

[punter]

Всем доброго дня.

Возник вопрос расчета эффективной годовой ставки в файле прикрепленном, в ячейке N15 - может ли кто помочь показать пошаговый механизм расчета? 

' Copyright by Hawk, 2005-2006

Private Sub Add_credit_Click()
Dim StartRow, StartCol, InsPos As Integer
StartRow = 22
StartCol = 2

ActiveSheet.Unprotect

    
    If Cells(StartRow, StartCol).Value = "" Then
        InsPos = StartRow
        Else
            If Cells(StartRow + 1, StartCol).Value = "" Then
                InsPos = StartRow + 1
            Else
                InsPos = Cells(Cells(StartRow, StartCol).End(xlDown).Row + 1, StartCol).Row
            End If
    End If
    
    Range("B18:Q18").Copy
    Cells(InsPos, StartCol).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Cells(InsPos, StartCol).PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B18").Copy
    Cells(InsPos, StartCol).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
    Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Row >= 5 And Target.Row <= 14) And (Target.Column >= 4 And Target.Column <= 7) Then
    ActiveSheet.Unprotect
    ActiveSheet.Calculate
    'Range("N14").Calculate
    Range("N12").GoalSeek Goal:=Range("M16").Value, ChangingCell:=Range("N15")
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim StartRow, StartCol, InsPos As Integer
StartRow = 22
StartCol = 2
DelDes = vbNo

ActiveSheet.Unprotect

Target.Range.Activate
If Target.Range.Row = 20 Then
        DelDes = MsgBox("Удалить ВСЕ кредиты из списка?", vbYesNo, "Удаление кредита")
        If DelDes = vbYes Then Range(Cells(StartRow, StartCol), Cells(StartRow, StartCol).End(xlDown)).Select
    Else
        If Target.Range.Row >= 22 Then _
            DelDes = MsgBox("Удалить этот кредит из списка?", vbYesNo, "Удаление кредита")
End If
If DelDes = vbYes Then
    Selection.EntireRow.Delete
    Cells(Selection.Row, Selection.Column).Select
    'Cells(StartRow - 2, StartCol).Select
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

В описании файла говорится по использование аннуитетной формой погашения, а как зашить в формулу дифференцированной оплаты?

 

Спасибо! С меня пиво/нишятки 

 

Прикрепленные файлы

•  Расчет эффективной ставки вознаграждения.zip   22,7К   Количество загрузок: 73

Сообщение отредактировал punter: 11.08.2019, 11:21:32

[punter]

ошибка

Сообщение отредактировал punter: 11.08.2019, 11:20:28

[T-800 Danya]

В описании файла говорится по использование аннуитетной формой погашения, а как зашить в формулу дифференцированной оплаты?

Спасибо! С меня пиво/нишятки

ИМХО выписать формулу Аннуитентного погашения под ней выписать Вашу формулу для дифференциального погашения.

Затем посмотреть какие константы/переменные в выписанных формулах совпадают, какие нет.

Затем посмотреть какие переменные каким ячейкам сосуществуют в вашем файле.

Затем по образцу приведенного Вами кода написать код для расчета Вашей эффективной годовой ставки.

Один словом как-то примерно так вот...

Скрытый текст

ЗЫ А если для Вас это слишком скучно/трудно как вариант можете обратится к тем кому кодить интересно/легко

Скрытый текст

ЗЗЫ Завуч - так понимаю -  кодить не хочет ибо ужасно занят тасканием мебели, а лично я, увы Пиво не пью от слова ВООБЩЕ даже если оно с Ништяками

Сообщение отредактировал T-800 Danya: 11.08.2019, 20:11:53

[sinsemilia]

В описании файла говорится по использование аннуитетной формой погашения, а как зашить в формулу дифференцированной оплаты?   Спасибо! С меня пиво/нишятки 

Я сейчас бегло осмотрела на файл, основная формула расчета у вас зашита не в макросе, а в скрытых ячейках (B18:Q18). 

Макрос только добавляет строки на лист, которые тянуться из основного расчета (см. выше), скорее для удобства пользования, по моему расчетов там нет. 

 

А вообще вам именно этот файл нужен? Я нашла вот такой калькулятор, где прописаны сразу два вида расчета.

 kalkulator.rar   70,16К   Количество загрузок: 162

Сообщение отредактировал sinsemilia: 13.08.2019, 22:05:12

[S.Carter]

@punter,  вот кусок который отвечает за проценты. Есть еще скрытые ячейки текст выделите красным это М16 и N12

 

 

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Row >= 5 And Target.Row <= 14) And (Target.Column >= 4 And Target.Column <= 7) Then
    ActiveSheet.Unprotect
    ActiveSheet.Calculate
    'Range("N14").Calculate
    Range("N12").GoalSeek Goal:=Range("M16").Value, ChangingCell:=Range("N15")
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

 

 

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

 

Здесь описано как это сделать вручную

Сообщение отредактировал S.Carter: 17.08.2019, 16:48:44

[punter]

В описании файла говорится по использование аннуитетной формой погашения, а как зашить в формулу дифференцированной оплаты?   Спасибо! С меня пиво/нишятки 

Я сейчас бегло осмотрела на файл, основная формула расчета у вас зашита не в макросе, а в скрытых ячейках (B18:Q18). 

kalkulator.rar

 

 

Спасибо!

[punter]

@punter,  вот кусок который отвечает за проценты. Есть еще скрытые ячейки текст выделите красным это М16 и N12

 

 

Private Sub Workshe

 

Спасибо!

[Boommer]

Надстройка PLEX тут!