首先,D列是自己输入,还是要用公式从A列获取?如果手动输入,跳过第1步。
1. D2单元格输入下面公式,不要直接回车,按Ctrl + Shift + Enter,再下拉:
=IFERROR(INDEX($A$2:$A$9,SMALL(IF(ROW($A$2:$A$9)-1=MATCH($A$2:$A$9,$A$2:$A$9,0),ROW(A2:A9)-1),ROW(1:1))),"")
2. 然后再在E2单元格输入下面公式,同样按Ctrl + Shift + Enter,再向右和向下拉:
=IFERROR(INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D2,ROW($A$2:$A$9)-1),COLUMN(A:A))),"")
(以上是以9行数据为例,可根据实际数据行数修改数字“9”)
追问红色框内的订单号是重复的,我用公式一算了后,这些重复的订单号,D列就没有了,然后我自己用删除重复值这个功能做了下这步,然后做第二个公式的时候也出不来数据,这得咋搞
第一个公式的目的是,将A列去重后复制到D列。但公式中漏了个“-1”导致有点毛病。将第一个公式改为下面这个就可以了,不需要你手动删除重复值。
=IFERROR(INDEX($A$2:$A$9,SMALL(IF(ROW($A$2:$A$9)-1=MATCH($A$2:$A$9,$A$2:$A$9,0),ROW($A$2:$A$9)-1),ROW(1:1))),"")
如果还有问题,就发下面邮箱:
我刚刚发送表格至您邮箱了。
追答公式没有问题,我重新看了你的截图,你把第二个公式输入错了,最前面的IFERROR少了个"I"。
再重新列一下公式,你复制粘贴过去就行,手动输入容易写错:
D2单元格公式:(Ctrl + Shift + Enter,再下拉)
=IFERROR(INDEX($A$2:$A$1500,SMALL(IF(ROW($A$2:$A$1500)-1=MATCH($A$2:$A$1500,$A$2:$A$1500,0),ROW($A$2:$A$1500)-1),ROW(1:1))),"")
E2单元格公式: (直接回车,下拉)
=SUMIF($A$2:$A$1500,D2,C2:C1500)
F2单元格公式: (Ctrl + Shift + Enter,再向右、向下拉)
=IFERROR(INDEX($B$2:$B$1500,SMALL(IF($A$2:$A$1500=$D2,ROW($A$2:$A$1500)-1),COLUMN(A:A))),"")
这种题,5分就可以了,100分太多了。
在C1输入Flag
在C2输入公式:="数值"&COUNTIF($A$2:A2,A2),然后向下填充
按Alt+F11,打开VBE编辑器,然后在左边资源管理器窗口插入一个模块;
复制以下代码:
Function GetSqlRecordSetFromExcel(ByVal Sqlstr As String, ByVal Headers As Boolean)
Dim cn As Object, rs As Object
Dim strConn As String
Dim Path As String, m,n
Path = ActiveWorkbook.FullName
If Headers = True Then
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Path & ";" & "Extended Properties=""Excel 12.0 Macro; IMEX=2;HDR=YES"""
Else
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Path & ";" & "Extended Properties=""Excel 12.0 Macro; IMEX=2;HDR=NO"""
End If
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strConn
On Error GoTo herro
Set rs = cn.Execute(Sqlstr)
Dim i As Long, j As Long
Dim arr() As Variant, brr() As Variant
brr() = ArrayTranspose(rs.GetRows)
m = UBound(brr(), 1) + 2
n = UBound(brr(), 2) + 1
ReDim arr(1 To m, 1 To n)
For j = 1 To n
arr(1, j) = rs.Fields(j - 1).name
Next
For i = 2 To m
For j = 1 To n
arr(i, j) = brr(i - 2, j - 1)
Next
Next
Erase brr
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
herro:
If Err.Number = 0 Then
GetSqlRecordSetFromExcel = arr()
Else
GetSqlRecordSetFromExcel = "Error Number: " & Err.Number & ";" & Err.Description
End If
End Function
Function ArrayTranspose(ByVal SourceArray As Variant) As Variant
Dim i, j
Dim arr As Variant
ReDim arr(0 To UBound(SourceArray, 2), 0 To UBound(SourceArray, 1))
For i = LBound(SourceArray, 1) To UBound(SourceArray, 1)
For j = LBound(SourceArray, 2) To UBound(SourceArray, 2)
If VBA.IsNull(SourceArray(i, j)) Then
arr(j, i) = ""
Else
arr(j, i) = SourceArray(i, j)
End If
Next
Next
ArrayTranspose = arr
End Function
5.在模块1内粘贴代码,然后关闭VBE界面回到Excel
6.这时候在D1单元格输入公式(请根据你数据存放的sheet名字修改公式中的粗体sheet1:
=GetSqlRecordSetFromExcel("TRANSFORM Last(值) SELECT ID FROM [sheet1$A1:C] GROUP BY ID order by id PIVOT FLAG",1)
7.如果是office2016以上版本,直接回车,完成。
追问咋做
用INDEX数组公式
1、复制A列并粘贴到D列,点上面菜单上的:数据==>删除重复项==>勾选:以当前选定区域排序==>点按:删除重复项==>确定。 即可在D列得到A中所有不重复项
2、在E1单元格输入数组公式:=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$D2,ROW($1:$7),9^9),COLUMN(A$1))),"")
特别注意:上面的数组公式粘贴或者输入后必须同时按CTRL+SHIFT+ENTER这3个键结束,否则公式无效的!!!
3、复制并下拉和右拉E2单元格的公式,填充好E、F、G……列即可。
d2公式拉出来的数据就是A列的订单号,有什么区别吗
然后我用拉E2的数据,也只能出来第一列的数据,右拉没数据,这是咋回事,数据是挺多的,,可以发源文件你们看看呢
那把问题作为内容(邮件主题一定要包含“excel”,本人以此为依据辨别非垃圾邮件,以免误删),excel样表文件(把现状和目标效果表示出)作为附件发到[email protected]帮你看下,才发现公式失误e2=if($d2="","",if(column(a1)>countif($a:$a,$d2),"",index($b:$b,match($d2,$a:a,0)+column(a1)-1)))右拉下拉
本回答被提问者采纳