Перейти к содержимому

Фотография

Excelпомощь, консультации...


  • Авторизуйтесь для ответа в теме
Сообщений в теме: 876

#861
Daulet.tech

Daulet.tech
  • Завсегдатай
  • 239 сообщений
=ЕСЛИ(ДВССЫЛ($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

  • 0

#862
sinsemilia

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

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

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

 

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

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

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


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

  • 0

#863
sinsemilia

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

#864
zavuch

zavuch
  • В доску свой
  • 1 821 сообщений

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

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

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

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

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

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

 

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

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


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

  • 0

#865
zavuch

zavuch
  • В доску свой
  • 1 821 сообщений

ЛС

 

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

 

Листы:

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

 

Форма:

 

gallery_12341_19677_808.png

 

Модуль:

 

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

  • 0

#866
zavuch

zavuch
  • В доску свой
  • 1 821 сообщений

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

 

gallery_12341_19677_2053.png

 

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

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


  • 0

#867
T-800 Danya

T-800 Danya
  • Постоялец
  • 370 сообщений

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

 

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

 

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

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

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


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

  • 0

#868
zavuch

zavuch
  • В доску свой
  • 1 821 сообщений


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


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

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

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

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

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

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

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

  • 0

#869
Daulet.tech

Daulet.tech
  • Завсегдатай
  • 239 сообщений
Если данные по городам хранить в отдельном листе и наименование листа = город (регион)
Каждый лист в отдельном книге сохраняет, дальше в ручную по эл.почте
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

  • 0

#870
zavuch

zavuch
  • В доску свой
  • 1 821 сообщений

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

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

 

Синс?


  • 0

#871
sinsemilia

sinsemilia
  • В доску свой
  • 2 597 сообщений

Синс?

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

 

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

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

 

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

 

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

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


  • 0

#872
sinsemilia

sinsemilia
  • В доску свой
  • 2 597 сообщений

Если данные по городам хранить в отдельном листе и наименование листа = город (регион)
Каждый лист в отдельном книге сохраняет, дальше в ручную по эл.почте
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 сохранял (там тоже один лист). По почте можно тоже макросом, если Аутлук стоит и табличка какой город кому отправляется есть.
 

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


  • 0

#873
punter

punter
  • Свой человек
  • 874 сообщений

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

Возник вопрос расчета эффективной годовой ставки в файле прикрепленном, в ячейке 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


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

 

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


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

  • 0

#874
punter

punter
  • Свой человек
  • 874 сообщений

ошибка


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

  • 0

#875
T-800 Danya

T-800 Danya
  • Постоялец
  • 370 сообщений

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

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


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

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

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

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

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

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


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

  • -2

#876
sinsemilia

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

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

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

 

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

Прикрепленный файл  kalkulator.rar   70,16К   Количество загрузок: 8


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

  • 0

#877
S.Carter

S.Carter
  • Свой человек
  • 936 сообщений

@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

  • 1




Количество пользователей, читающих эту тему: 1

пользователей: 0, неизвестных прохожих: 1, скрытых пользователей: 0

Размещение рекламы на сайте     Предложения о сотрудничестве     Служба поддержки пользователей

© 2011-2019 vse.kz. При любом использовании материалов Форума ссылка на vse.kz обязательна.