Easy-题目9:171. Excel Sheet Column Number

本文介绍了一种将Excel中的列号(如A、B、C等)转换为相应的数字编号(如1、2、3等)的算法实现。通过递归计算方法,将任意长度的列号字符串转化为其对应的数字表示。

题目原文:
Given a column title as appear in an Excel sheet, return its corresponding column number.

For example:

A -> 1
B -> 2
C -> 3
...
Z -> 26
AA -> 27

AB -> 28
题目大意:
把Excel中的列号转换成自然数的列号。
题目分析:
递归计算,列号长度为0时对应0,否则为除最后一位的子串对应列号*26加末尾一位字母的序号。
例如:ABC列对应的自然数是 AB列对应的自然数(28)*26+C的字母序号(3)。
源码:(language:java)

public class Solution {
    public int titleToNumber(String s) {
        return (s.length()==0)?0:titleToNumber(s.substring(0, s.length()-1))*26+s.charAt(s.length()-1)-'A'+1;
    }
}

成绩:
3ms,beats 11.31%,众数3ms,66.06%

Option Explicit &#39; Main procedure to convert column pairs to vertical format Sub ConvertColumnPairsToVerticalXLSM() &#39; Declare all variables Dim sourceFile As Variant Dim wbSource As Workbook, wbTarget As Workbook Dim wsSource As Worksheet, wsTarget As Worksheet Dim wsName As String Dim i As Long, pairIndex As Long, rowOffset As Long Dim fileName As String Dim maxRow As Long Dim targetPath As String Dim startTime As Double Dim processedCells As Long &#39; Define column pair mappings Dim colPairs() As Variant Dim pair1 As Variant, pair2 As Variant, pair3 As Variant, pair4 As Variant, pair5 As Variant Dim pair6 As Variant, pair7 As Variant, pair8 As Variant, pair9 As Variant, pair10 As Variant, pair11 As Variant pair1 = Array(Array(4, 5), "D:E") pair2 = Array(Array(9, 10), "I:J") pair3 = Array(Array(11, 12), "K:L") pair4 = Array(Array(13, 14), "M:N") pair5 = Array(Array(15, 16), "O:P") pair6 = Array(Array(17, 18), "Q:R") pair7 = Array(Array(19, 20), "S:T") pair8 = Array(Array(21, 22), "U:V") pair9 = Array(Array(23, 24), "W:X") pair10 = Array(Array(25, 26), "Y:Z") pair11 = Array(Array(27, 28), "AA:AB") colPairs = Array(pair1, pair2, pair3, pair4, pair5, pair6, pair7, pair8, pair9, pair10, pair11) &#39; Start timer startTime = Timer processedCells = 0 &#39; Step 1: Select source Excel file sourceFile = Application.GetOpenFilename( _ FileFilter:="Excel Files (*.xlsx; *.xls; *.xlsm), *.xlsx; *.xls; *.xlsm", _ Title:="Select Source Excel File") If sourceFile = False Then Exit Sub &#39; User canceled &#39; Step 2: Open selected workbook Application.ScreenUpdating = False Set wbSource = Workbooks.Open(sourceFile, ReadOnly:=True) &#39; Prompt for worksheet name wsName = Application.InputBox("Enter worksheet name to process:", "Worksheet Selection", Type:=2) If wsName = "" Or wsName = "False" Then wbSource.Close False Exit Sub End If On Error Resume Next Set wsSource = wbSource.Sheets(wsName) On Error GoTo 0 If wsSource Is Nothing Then MsgBox "Worksheet &#39;" & wsName & "&#39; not found!", vbExclamation wbSource.Close False Exit Sub End If &#39; Step 3: Determine maximum data row maxRow = FindMaxDataRow(wsSource, colPairs) If maxRow = 0 Then MsgBox "No data found in specified columns", vbInformation wbSource.Close False Exit Sub End If &#39; Step 4: Get output file name fileName = Application.InputBox("Enter output file name (without extension):", "File Name", "OP_Report", Type:=2) If fileName = "" Or fileName = "False" Then wbSource.Close False Exit Sub End If targetPath = Application.GetSaveAsFilename( _ InitialFileName:=fileName & ".xlsm", _ FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", _ Title:="Save Output File") If targetPath = False Then &#39; User canceled wbSource.Close False Exit Sub End If &#39; Step 5: Create new workbook Set wbTarget = Workbooks.Add Set wsTarget = wbTarget.Sheets(1) wsTarget.Name = "OutputData" &#39; Step 6: Add headers For i = LBound(colPairs) To UBound(colPairs) wsTarget.Cells(1, i + 1).Value = "Column Pair " & (i + 1) & " (" & colPairs(i)(1) & ")" wsTarget.Cells(1, i + 1).Font.Bold = True wsTarget.Cells(1, i + 1).Interior.Color = RGB(200, 200, 255) wsTarget.Cells(1, i + 1).HorizontalAlignment = xlCenter Next i &#39; Step 7: Use arrays for better performance Dim outputData() As Variant Dim outputRows As Long outputRows = maxRow * 2 &#39; Each column has 2 rows per data point ReDim outputData(1 To outputRows, 1 To UBound(colPairs) + 1) &#39; Populate data array with type-safe assignments For pairIndex = LBound(colPairs) To UBound(colPairs) Dim columns As Variant columns = colPairs(pairIndex)(0) &#39; Get column indices array &#39; Process each row for current column pair For i = 1 To maxRow &#39; Calculate target row position rowOffset = (i - 1) * 2 &#39; First column data outputData(rowOffset + 1, pairIndex + 1) = GetCellValue(wsSource, i, columns(0)) processedCells = processedCells + 1 &#39; Second column data outputData(rowOffset + 2, pairIndex + 1) = GetCellValue(wsSource, i, columns(1)) processedCells = processedCells + 1 Next i Next pairIndex &#39; Step 8: Write data to worksheet in bulk If outputRows > 0 Then wsTarget.Range("A2").Resize(outputRows, UBound(colPairs) + 1).Value = outputData End If &#39; Step 9: Apply formatting ApplyFormatting wsTarget, maxRow * 2, UBound(colPairs) + 1 &#39; Step 10: Create summary sheet CreateSummarySheet wbTarget, wbSource.FullName, wsName, startTime, processedCells, maxRow, UBound(colPairs) + 1 &#39; Step 11: Save workbook Application.DisplayAlerts = False wbTarget.SaveAs fileName:=targetPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.DisplayAlerts = True &#39; Step 12: Cleanup wbSource.Close False Set wsSource = Nothing Set wbSource = Nothing &#39; Add VBA code to the workbook If Not AddVbaCodeToWorkbook(wbTarget) Then MsgBox "Unable to add macro code, but report has been saved.", vbExclamation End If &#39; Save again with macros wbTarget.Save &#39; Completion message MsgBox "Report generated successfully!" & vbCrLf & _ "Saved to: " & targetPath & vbCrLf & _ "Cells processed: " & Format(processedCells, "#,##0") & vbCrLf & _ "Time taken: " & Format(Timer - startTime, "0.00") & " seconds", _ vbInformation, "Complete" Application.ScreenUpdating = True End Sub &#39; Safely get cell value with proper type handling Function GetCellValue(ws As Worksheet, row As Long, col As Long) As Variant &#39; Handle potential errors On Error Resume Next &#39; Check if row is beyond data range If row > ws.Cells(ws.Rows.Count, col).End(xlUp).row Then GetCellValue = "" Exit Function End If Dim cellValue As Variant cellValue = ws.Cells(row, col).Value &#39; Handle empty cells If IsEmpty(cellValue) Then GetCellValue = "" Exit Function End If &#39; Handle error values If IsError(cellValue) Then GetCellValue = "Error: " & CStr(cellValue) Exit Function End If &#39; Format dates consistently If IsDate(cellValue) Then GetCellValue = Format(cellValue, "yyyy-mm-dd") Exit Function End If &#39; Handle large numbers to prevent scientific notation If IsNumeric(cellValue) Then If Abs(cellValue) > 1E+15 Then GetCellValue = "&#39;" & CStr(cellValue) Exit Function End If End If &#39; Default case GetCellValue = cellValue End Function &#39; Apply consistent formatting to output Sub ApplyFormatting(ws As Worksheet, dataRows As Long, colCount As Long) &#39; Define the data range Dim rng As Range Set rng = ws.Range("A2").Resize(dataRows, colCount) &#39; Set text properties With rng .WrapText = True .VerticalAlignment = xlTop End With &#39; Apply alternating row colors Dim rowIdx As Long For rowIdx = 2 To dataRows + 1 If rowIdx Mod 2 = 0 Then ws.Rows(rowIdx).Interior.Color = RGB(240, 240, 240) &#39; Light gray Else ws.Rows(rowIdx).Interior.Color = RGB(255, 255, 255) &#39; White End If Next rowIdx &#39; Auto-fit columns for optimal width ws.columns.AutoFit &#39; Add borders to data area With rng.Borders .LineStyle = xlContinuous .Weight = xlThin .Color = RGB(150, 150, 150) End With &#39; Freeze header row for easy scrolling ws.Activate ws.Range("A2").Select ActiveWindow.FreezePanes = True End Sub &#39; Find the maximum row with data across all column pairs Function FindMaxDataRow(ws As Worksheet, colPairs As Variant) As Long Dim maxRow As Long Dim i As Long Dim columns As Variant maxRow = 0 For i = LBound(colPairs) To UBound(colPairs) columns = colPairs(i)(0) &#39; Get column indices array &#39; Check first column maxRow = Application.WorksheetFunction.Max(maxRow, _ ws.Cells(ws.Rows.Count, columns(0)).End(xlUp).row) &#39; Check second column maxRow = Application.WorksheetFunction.Max(maxRow, _ ws.Cells(ws.Rows.Count, columns(1)).End(xlUp).row) Next i FindMaxDataRow = maxRow End Function &#39; Create summary sheet with report details Sub CreateSummarySheet(wb As Workbook, sourcePath As String, sheetName As String, _ startTime As Double, cellCount As Long, maxRow As Long, pairCount As Long) &#39; Add new worksheet Dim ws As Worksheet Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)) ws.Name = "ReportSummary" &#39; Create title With ws.Range("A1") .Value = "Data Conversion Report Summary" .Font.Size = 16 .Font.Bold = True .Interior.Color = RGB(180, 180, 255) End With &#39; Prepare report details Dim details As Variant details = Array( _ Array("Source file:", sourcePath), _ Array("Worksheet:", sheetName), _ Array("Generated at:", Format(Now, "yyyy-mm-dd hh:mm:ss")), _ Array("Column pairs processed:", pairCount), _ Array("Rows processed:", maxRow), _ Array("Cells processed:", Format(cellCount, "#,##0")), _ Array("Time taken:", Format(Timer - startTime, "0.00") & " seconds")) &#39; Write details to worksheet Dim i As Long For i = 0 To UBound(details) ws.Cells(i + 3, 1).Value = details(i)(0) ws.Cells(i + 3, 1).Font.Bold = True ws.Cells(i + 3, 2).Value = details(i)(1) Next i &#39; Add navigation buttons Dim btn As Button &#39; View Data button Set btn = ws.Buttons.Add(100, 100, 120, 30) With btn .Caption = "View Data" .OnAction = "GoToOutputSheet" .Name = "btnViewData" End With &#39; Regenerate button Set btn = ws.Buttons.Add(100, 140, 120, 30) With btn .Caption = "Regenerate" .OnAction = "RefreshReport" .Name = "btnRefresh" End With &#39; Close Report button Set btn = ws.Buttons.Add(100, 180, 120, 30) With btn .Caption = "Close Report" .OnAction = "CloseReport" .Name = "btnClose" End With &#39; Format summary sheet With ws.Range("A3:B" & 3 + UBound(details)) .Borders.Weight = xlThin .columns.AutoFit End With End Sub &#39; Add VBA code to the workbook for button functionality Function AddVbaCodeToWorkbook(wb As Workbook) As Boolean On Error GoTo ErrorHandler Dim vbProj As Object Dim vbComp As Object Dim codeLines As String Set vbProj = wb.VBProject Set vbComp = vbProj.VBComponents.Add(1) &#39; Create standard module &#39; Add GoToOutputSheet procedure codeLines = "Sub GoToOutputSheet()" & vbCrLf & _ " On Error Resume Next" & vbCrLf & _ " Sheets(""OutputData"").Activate" & vbCrLf & _ " If Err.Number <> 0 Then" & vbCrLf & _ " MsgBox ""&#39;OutputData&#39; worksheet not found!"", vbExclamation" & vbCrLf & _ " End If" & vbCrLf & _ "End Sub" vbComp.CodeModule.AddFromString codeLines &#39; Add RefreshReport procedure codeLines = "Sub RefreshReport()" & vbCrLf & _ " MsgBox ""To regenerate this report, you need access to the original data file." & vbCrLf & _ "Please run the original macro to regenerate the report."", vbInformation" & vbCrLf & _ "End Sub" vbComp.CodeModule.AddFromString codeLines &#39; Add CloseReport procedure codeLines = "Sub CloseReport()" & vbCrLf & _ " ThisWorkbook.Close SaveChanges:=False" & vbCrLf & _ "End Sub" vbComp.CodeModule.AddFromString codeLines AddVbaCodeToWorkbook = True Exit Function ErrorHandler: AddVbaCodeToWorkbook = False End Function 修正byref错误
12-19
根据原作 https://pan.quark.cn/s/459657bcfd45 的源码改编 Classic-ML-Methods-Algo 引言 建立这个项目,是为了梳理和总结传统机器学习(Machine Learning)方法(methods)或者算法(algo),和各位同仁相互学习交流. 现在的深度学习本质上来自于传统的神经网络模型,很大程度上是传统机器学习的延续,同时也在不少时候需要结合传统方法来实现. 任何机器学习方法基本的流程结构都是通用的;使用的评价方法也基本通用;使用的一些数学知识也是通用的. 本文在梳理传统机器学习方法算法的同时也会顺便补充这些流程,数学上的知识以供参考. 机器学习 机器学习是人工智能(Artificial Intelligence)的一个分支,也是实现人工智能最重要的手段.区别于传统的基于规则(rule-based)的算法,机器学习可以从数据中获取知识,从而实现规定的任务[Ian Goodfellow and Yoshua Bengio and Aaron Courville的Deep Learning].这些知识可以分为四种: 总结(summarization) 预测(prediction) 估计(estimation) 假想验证(hypothesis testing) 机器学习主要关心的是预测[Varian在Big Data : New Tricks for Econometrics],预测的可以是连续性的输出变量,分类,聚类或者物品之间的有趣关联. 机器学习分类 根据数据配置(setting,是否有标签,可以是连续的也可以是离散的)和任务目标,我们可以将机器学习方法分为四种: 无监督(unsupervised) 训练数据没有给定...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值