Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » alessandro2981
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Локальная база данных, Выбор Front End and Back End приложений (интерфейсов)
 
#
20.07.2015 21:39:25
Здравствуйте, хочу узнать ваше мнение, так как думаю, что на форуме есть много людей, кто с этим сталкивался.

Нужно создать базу данных и пользовательский интерфейс для компании, БД будет храниться на рабочем компьютере, с открытым доступом других пользователей локальной сети.
Где введя свой Логин, работник сможет получить всякие отчеты в самом интерфейсе или в виде excel файла.


1.Страховая компания.
2.Пользователей примерно 20-30.

3.Сейчас все хранится в Excel файлах, нету структуры данных, поэтому появилась необходимость создать Базу данных для нужд работников, а именно:
1) Одновременный ввод данных в формы многими работниками.
2) Просмотр данных в виде таблиц.
3) Легкий импорт/экспорт в Excel таблицы. (Некоторые данные получаются только в виде Excel файлов из других баз данных, появляется необходимость постоянного обновления записей базы данных на новые из Excel файла)
4) Получение отчетов о продажах и т.д.
5) Построение графиков исходя из запросов и таблиц.

4.Есть:
1) на всех компьютерах установленный MS Office 2013 (без Access)
2) на 2 компьютерах установлен MS Access 2013. (Есть возможность на остальные установить бесплатный Access Runtime 2013)
3) начатый проект в MS Access 2013, где есть логин форма и в зависимости от пользователя выбор разрешенной ему формы. (Разделено на 2 файла, Front end, Back end)

5.Знания:
1) MS Office 2013 (Access, Excel)
2) VBA
3) Основы HTML + CSS
4) Основы создания SQL запросов.

6.Объемы БД:
1) Есть отчет из интернета, где 3млн строчек и около 15 колонок. (Было 5 разделенных excel файлов, с горем пополам, закинул в один файл MS Access) в Access он весит 1гб.
2) Есть много разных отчетов, где около 150 тысяч строк и 100 колонок.
3) И всякие разные мелкие файлы.
Все это хранится в Excel файлах.

7.Сроки и ограничения:
- Нужно создать основу, где уже будут хоть какие-то отчеты в течении 15 дней.

- Без доп. вложений. (без покупки лицензий на сторонний софт)

8.Предположительные решения:
1) MS Access (Front End) + MS Access (Back End)
2) MS Access (Front End) + MS SQL Server Express 2012 (Back End) (нужно будет обучиться управлением SQL server)
3) ASP.NET + MS SQL Server Express 2012
4) Готовое бесплатное решение, предназначенное для этих целей. (типо Joomla)
5) Php + MYSQL

Что можете посоветовать в данном случае? (учитывая срок в 15 дней и время на обучение пользования решений 8.3-8.5)
 
Перейти    
SQL запрос или словари для слияния листов
 
#
13.07.2015 13:57:17
Здравствуйте, который раз уже сталкиваюсь с проблемой из-за не понимания принципа написания SQL запросов в excel или использование словарей. В интернете очень мало такой информации, особенно доступной для соображения. На Youtube вообще не нашел видео на эту тему.

Попробовал сделать через массивы, и выгрузку из них. Получилось все очень тормознуто, минут 20 обрабатывает 140 000 строк из-за огромного количества For, IF, Next.

Думаю SQL запрос как раз расщитан для такого и справится намного быстрее:

1. Лист CRRATYLV и DATA добавляются с помощью макроса из другой книги, это я сделал.
1.1. Лист CRRATYLV постоянно новый
2. В Лист FLEET из листа "DATA" вытаскиваются значения Produkta kods, Polises numurs, FLEET, Territory
3. Нужно взять все значения из листа CRRATYLV только из коллонок перечисленных в Sheets("MACRO").Range("A2:A" & Lastrow)
3.1. Выбрать только те строки, где Produkta kods = 430 и 440
4. Скопировать отсортированные значения из листа "CRRATYLV" на лист "DATA", при этом подставляя значения Коллонок (FLEET, Territory) из листа FLEET где Produkta kods = Produkta kods AND Polises numurs = Polises numurs
5. В коллонках Auto Year, Period на листе DATA будут формулы, так что с ними ничего делать не надо

Подскажите, как это можно реализовать. Заранее спасибо!
Изменено: alessandro2981 - 13.07.2015 14:00:04
 
Перейти    
Использование словарей как альтернативу Vlookup
 
#
30.06.2015 15:35:25
Здравствуйте, пробую разобраться с созданием словарей в Excel VBA. Не могу понять их принцип.

Задача такая - Имеется таблица на листе ("Data") с огромным колличеством строк и коллонок: (сверху A,B это буква коллонки; слева номер строки)
AB…..CB
1
……
11Код продуктаНомер продуктаФотография
124001Yes
1341021No
1442013Yes
15430222Yes
1644033No
Нужно добавить в словарь только те значения, у которых в коллонке А номер = 440, и добавить на другой лист ("Foto") эти значения, где:
AB….E
1Номер продуктаФото
2
3
Через Select Case сделал, но скорость обработки не самая быстрая получилась, хочу научиться через словари это делать или через SQL запросы.
 
Перейти    
Вывод значений из Excel в DOS
 
#
17.06.2015 14:36:03
Здравствуйте, хочу узнать, можно ли как-то записать макрос, который будет управлять DOS системой, в которой управление ведется засчет клавиатуры (Всякие нажатия, например Tab, стрелки, буквы). И сам вводить значения из таблицы excel. А то надо ввести 2000 значений, руками это делать проблематично. А по сути дела происходят одни и те же комманды.
 
Перейти    
Использование переменной для выбора диапазона
 
#
15.06.2015 15:54:11
Здравствуйте, столкнулся с такой проблемой:
Не получается с помощью переменных указать диапазон для копирования

Код
Dim WB1 As Workbook, WB2 As Workbook
Set WB1 = ActiveWorkbook

Sheets("CRRATYLV").AutoFilterMode = False

    WB1.Sheets("CRRATYLV").Select
    Set WB2 = Workbooks.Add
'    Set WB2 = ActiveWorkbook
    Worksheets(1).Name = "DATA"
    WB1.Activate

Dim PNf As Range, PrN As Long, Fcoll As String
    With Worksheets("CRRATYLV").Rows("1:1")
        Set PNf = .Find(What:="Produkta nosaukums", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not PNf Is Nothing Then
            PrN = PNf.Column
            End If
    End With
    Set PNf = Nothing
    Fcoll = Split(Cells(1, PrN).Address, "$")(1)' Когда указываю диапазон с переменными, пишет что Object doesn't support this property or method
    Columns(Fcoll & ":" & Fcoll).Copy Destination:=WB2.Range("A1")
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Перейти    
О важности обозначения типа переменной
 
#
10.06.2015 19:44:41
Здравствуйте, недавно начал читать книжку по Excel VBA, и как новичек, не понимал зачем же нужно писать Dim ..., эксель ведь не глупый, разберется. Пока не увидил в книжке отличный пример кода.
Код
Sub TimeTest()
Dim x As Long, y As Long
Dim A As Double, B As Double, C As Double
Dim i As Long, j As Long
Dim StartTime As Date, EndTime As Date
' Sohranenie vremeni nachala vichislenij
StartTime = Timer
' Vipolnenie vichislenij
x = 0
y = 0
For i = 1 To 10000
    x = x + 1
    y = x + 1
    For j = 1 To 10000
        A = x + y + i
        B = y - x - i
        C = x / y * i
    Next j
Next i
' Poluchenie vremeni okonchanija vichislenij
    EndTime = Timer
' Otobrazhenie obshego vremeni v sekundah
    MsgBox Format(EndTime - StartTime, "0.0")
End Sub
Попробуйте запустить сначала такой код, а потом под комментарий добавить начиная со 2 строчки по 5.
Просто назначаем тип переменной, а разница обработки по времени в 3 раза. 6,5 сек по сравнению с 17.  :)

В качестве эксперемента изменил переменные на String.
Эксель думал 311 секунд.
Код
Dim x As String, y As String
Dim A As String, B As String, C As String
Мораль сей басни такова, чем больше знате, тем быстрее считаете.  :D
Изменено: alessandro2981 - 10.06.2015 19:59:27
 
Перейти    
Оптимизация вычислений формул через макрос
 
#
03.06.2015 10:44:30
Здравствуйте!
Столкнулся с такой проблемой:
Имеются 2 файла со статистикой, где каждый содержит по 100 000 строк.

Задача состоит в том, чтобы 2 раза в неделю вытаскивать значения из одного файла в другой по ID, где в каждом из файлов он стоит в коллонке A.
Код
Range("A1:CK1").AutoFilter Field:=3, Criteria1:=Array( _
        "19063", "19066", "430", "440"), Operator:=xlFilterValues
        
    Dim v As Range
    Set v = Range("A2:A" & Range("A1000000").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cells(1)
 ' proverjaem nashu peremennuju
  '  MsgBox "The first visible cell is " & v.Row
  
  ' Zapolnjaem kazduju jachejku
    With Sheets("CRRATYLV").Range("F" & v.Row & ":F" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=VLOOKUP(RC[11],BrokerMAG!C[-5]:C[-3],2,FALSE)"
    End With
' Zamenjaem na znachenija ------------------------------------------------------------------
    Sheets("CRRATYLV").AutoFilterMode = False
    With Range("F:F")
    .Value = .Value
    End With
     Range("A1:CK1").AutoFilter Field:=3, Criteria1:=Array( _
        "19063", "19066", "430", "440"), Operator:=xlFilterValues
' ------------------------------------------------------------------------------------------
    With Sheets("CRRATYLV").Range("G" & v.Row & ":G" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=VLOOKUP(RC[10],BrokerMAG!C[-6]:C[-4],3,FALSE)"
    End With
' Zamenjaem na znachenija ------------------------------------------------------------------
    Sheets("CRRATYLV").AutoFilterMode = False
    With Range("G:G")
    .Value = .Value
    End With
     Range("A1:CK1").AutoFilter Field:=3, Criteria1:=Array( _
        "19063", "19066", "430", "440"), Operator:=xlFilterValues
' ------------------------------------------------------------------------------------------
    With Sheets("CRRATYLV").Range("H" & v.Row & ":H" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=IF(VLOOKUP(RC[-7],ISSUEDALL!C1:C6,4,FALSE)=0,"""",VLOOKUP(RC[-7],ISSUEDALL!C1:C6,4,FALSE))"
    End With
 ' Zamenjaem na znachenija ------------------------------------------------------------------
    Sheets("CRRATYLV").AutoFilterMode = False
    With Range("H:H")
    .Value = .Value
    End With
     Range("A1:CK1").AutoFilter Field:=3, Criteria1:=Array( _
        "19063", "19066", "430", "440"), Operator:=xlFilterValues
' ------------------------------------------------------------------------------------------
    With Sheets("CRRATYLV").Range("I" & v.Row & ":I" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=IF(VLOOKUP(RC[-8],ISSUEDALL!C1:C6,5,FALSE)=0,"""",VLOOKUP(RC[-8],ISSUEDALL!C1:C6,5,FALSE))"
    End With
 ' Zamenjaem na znachenija ------------------------------------------------------------------
    Sheets("CRRATYLV").AutoFilterMode = False
    With Range("I:I")
    .Value = .Value
    End With
     Range("A1:CK1").AutoFilter Field:=3, Criteria1:=Array( _
        "19063", "19066", "430", "440"), Operator:=xlFilterValues
' ------------------------------------------------------------------------------------------
    With Sheets("CRRATYLV").Range("J" & v.Row & ":J" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=IF(VLOOKUP(RC[-9],ISSUEDALL!C1:C6,6,FALSE)=0,"""",VLOOKUP(RC[-9],ISSUEDALL!C1:C6,6,FALSE))"
    End With
' Zamenjaem na znachenija ------------------------------------------------------------------
    Sheets("CRRATYLV").AutoFilterMode = False
    With Range("J:J")
    .Value = .Value
    End With
     Range("A1:CK1").AutoFilter Field:=3, Criteria1:=Array( _
        "19063", "19066", "430", "440"), Operator:=xlFilterValues
' ------------------------------------------------------------------------------------------
    
    With Sheets("CRRATYLV").Range("K" & v.Row & ":K" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        .FormulaR1C1 = "=LOOKUP(""žžž"",RC[-4]:RC[-1])"
    End With
    
    
    Range("CL1").FormulaR1C1 = "Kanāls"
     Range("A1:CL1").AutoFilter Field:=3, Criteria1:=Array( _
        "19063", "19066", "430", "440"), Operator:=xlFilterValues
    
    With Sheets("CRRATYLV").Range("CL" & v.Row & ":CL" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        .FormulaR1C1 = "=VLOOKUP(RC[-73],BrokerMAG!C[-89]:C[-86],4,FALSE)"
    End With
    
    Sheets("CRRATYLV").AutoFilterMode = False
    With Range("CL:CL")
    .Value = .Value
    End With
Получается что-то вроде такого кода, как можно его оптимизировать, чтобы не приходилось каждый раз ждать по 20 минут, когда он обработает все значения?

После каждого вычисления столбика сделал чтобы excel заменял на значения, иначе вообще все подвиснет.
Сначала пробовал, чтобы он без снятия автофильтра замещал значения,
Код
With Sheets("CRRATYLV").Range("F" & v.Row & ":F" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=VLOOKUP(RC[11],BrokerMAG!C[-5]:C[-3],2,FALSE)"
    .Value = .Value
    End With
но тогда выдает ошибки и не заменяет значения как надо.
 
Перейти    
Пересечение диапазона дат и поиск по номеру строки
 
#
02.06.2015 17:19:46
Здравствуйте! Столкнулся с такой проблемой:

Нужно проверить для каждой из тех строк, у которых имеется значение в столбце G. По номеру строки который указан в ячейке H. Пересекаются ли диапазоны дат начала и конца у 430 продукта с 440. И найти эти полисы, где хотябы один из дней продукта 430 был действителен во время продукта 440. Полученные значения вывести в J ячейку, I как пример посчитал сам в голове.
 
Перейти    
Если значения диапазона равны нулю, то очистить значения
 
#
01.06.2015 10:08:41
Здравствуйте дорогие друзья! Столкнулся с такой проблемой:

Имеется код:
Код
Dim v As Range
    Set v = Range("A2:A" & Range("A1000000").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cells(1)
With Sheets("CRRATYLV").Range("H" & v.Row & ":H" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = "=VLOOKUP(RC[-7],ISSUEDALL!C1:C6,4,FALSE)"
    .Value = .Value
    If .Value = 0 Then .Value.Empty
    
    End With
Пишет ошибку Run-time error '13': Type mismatch
Как написать код правильно, чтобы после Vlookupa оставшиеся нули удалить из значений ячеек?
Изменено: alessandro2981 - 01.06.2015 10:22:53
 
Перейти    
Ввести формулу VLOOKUP в первую отфильтрованную строчку, Ввести формулу VLOOKUP в первую отфильтрованную строчку
 
#
29.05.2015 13:17:01
Здравствуйте, возник такой вопрос. Весь интернет облазил, ничего не смог найти.
Пишу макрос для автоматизации процесса обработки одного из файла статистики.
Ячейка C фильтруется по значениям автофильтра.

Далее из полученных отфильтрованных данных мне нужно найти первую отфильтрованную строку, допустим это F553 и вставить туда формулу -
Код
=VLOOKUP(A553;ISSUEDALL!$A:$G;2;FALSE)
Как сделать, чтобы макрос сам понимал что значение нужно вставить именно в F(номер строки) ячейку и в формуле именно A(номер строки) ?
И потом протянул значения только в офильтрованных ячейках.
Изменено: alessandro2981 - 29.05.2015 13:19:02
 
Перейти    
Нахождение предпоследней даты сделки
 
#
25.05.2015 10:38:53
Добрый день!

Составил пример имеющегося файла, суть вот в чем - имеется список номеров клиентов, которые берут страховки, есть столбцы с началом и с концом страховки. Если клиент оформляет страховку в день ее окончания или в течении следующего дня, то ему назначается статус - Возобнавленный, если нет, то Новый. Не могу найти функцию, которая бы находила эту самую дату конца предпоследней сделки, так как функция VLOOKUP возвращает только первое найденое значение, и если записей повторяющихся только 2, то он все вычисляет без ошибок, но если 3 или более, то уже VLOOKUP не подходит.
Изменено: alessandro2981 - 25.05.2015 10:49:08
 
Перейти    
Страницы: 1
Наверх